CODEWITHSUNDEEP
mysqldatetimeunix-timestamp
Jeremy
Jeremy

Reputation: 2536

MySQL comparing date from unix timestamp

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?

Upvotes: 0

Views: 7651

Answers (1)

medina
medina

Reputation: 8159

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)

;-)

Upvotes: 1

Related Questions