Search code examples
mysqlmysql-date

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?

Solution

  • 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