MySQL comparing date from unix timestamp

Question

So i have a session table which in essence has the following cols:

session_id | user_agent | last_activity (in unix timestamp)

When i tried to display the session_id and last_activity from a session that is created 5 minutes ago using this query

SELECT session_id, from_unixtime('last_activity' / 1000, '%Y-%m-%d %H:%i') AS session_time
FROM gw_sessions
WHERE last_activity >= unix_timestamp(CURRENT_TIMESTAMP - INTERVAL 5 MINUTE) * 1000

It does not work.

So i tried to simplify my query by just displaying all the sessions (session_id and last_activity) using the following query

SELECT session_id, from_unixtime('last_activity' / 1000, '%Y-%m-%d %H:%i') AS session_time
FROM gw_sessions

The result shows like this

session_id | session_time
abcdefg... --> 1970-01-01 07:00 (epoch)

Why it did not convert the value correctly and how can i compare two dates (now()) with the date stored in unix format correctly?

What are the type of field and value of last_activity in the database? — medina
– medina, Commented May 2, 2013 at 7:37
INT(10) with a value of 1367481523, i did a quick conversion using an online tool, the timestamp represent correct date.. — Jeremy
– Jeremy, Commented May 2, 2013 at 8:06

medina · Accepted Answer · 2013-05-03 03:52:19Z

1

Your query is returning the right information. Take a look:

SELECT (1367481523) a, (unix_timestamp(CURRENT_TIMESTAMP - INTERVAL 5 MINUTE) * 1000) b;
+------------+---------------+
| a          | b             |
+------------+---------------+
| 1367481523 | 1367483902000 |
+------------+---------------+

As you told me, your last_activity is the 'a' value. And it is smaller than 'b'. In your query you want all the records with 'a' >= 'b' (WHERE last_activity >= unix_timestamp...).

So far, your query is right. Maybe your logic is the one which needs to be changed.

[EDIT]

Then again, check out the returned dates that the values you are comparing bring over.

mysql> SELECT 
     from_unixtime(1367481523 / 1000, '%Y-%m-%d %H:%i') a, 
     from_unixtime(1367483902000 / 1000, '%Y-%m-%d %H:%i') b;
+------------------+------------------+
| a                | b                |
+------------------+------------------+
| 1970-01-17 05:51 | 2013-05-02 18:38 |
+------------------+------------------+
1 row in set (0.00 sec)

;-)

edited May 3, 2013 at 3:52

answered May 2, 2013 at 8:46

medina

8,2294 gold badges28 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeremy Over a year ago

Hmm... that is utterly strange, take a look of the query screenshot using workbench img199.imageshack.us/img199/9482/sql.PNG

Jeremy Over a year ago

i am totally lost now with the unix time formatting. I found the solution though after reading your suggestion over and over again. thanks though.

Collectives™ on Stack Overflow

MySQL comparing date from unix timestamp

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related