How can I search a column in MySQL containing unix timestamps?

Question

I have mysql table called user_log that contains something like:

userid  created_at

1       1388514600 
2       1391193000

I want to get the record using exact date. For example I have the created_at which is 2013-12-31

SELECT * FROM user_log WHERE created_at = UNIX_TIMESTAMP('2013-31-12');

But record is not selected, I don't know what was the problem in that query. So how can I get the record from unix timestamp column in mysql using date?

`UNIX_TIMESTAMP('2013-12-31')` will work a lot better but still not match as it is `1388448000`, not `1388514600` — Nick, Mar 18 '20 at 06:37
1388514600 this contain timestamp but i'm using date only not datetime — dhamo dharan, Mar 18 '20 at 06:49
So given what you now know, update your question accordingly — Strawberry, Mar 18 '20 at 06:52

score 1 · Accepted Answer · answered Mar 18 '20 at 06:51

To know the exact value in dateformat, you can do :

SELECT FROM_UNIXTIME(created_at) FROM user_log;

That said, the value 1388514600 is not just 2013-12-31 but it's actually 2013-12-31 18:30:00;

So to search by just date, you can try this:

SELECT FROM_UNIXTIME(created_at) AS dt 
FROM user_log 
HAVING date(dt)='2013-12-31';

Fiddle here

How can I search a column in MySQL containing unix timestamps?

1 Answers1