CODEWITHSUNDEEP
mysqlmysql-date
dhamo dharan
dhamo dharan

Reputation: 762

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

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?

Upvotes: 0

Views: 429

Answers (1)

FanoFN
FanoFN

Reputation: 7114

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

Upvotes: 1

Related Questions