Shaikh Sonny Aman’s Blog

Lets learn and share!

MySQL UNIX_TIMESTAMP problem while using in where clause to find out result from a date

Posted on | January 13, 2009 |

Today my teammate Mohsin was in a funny problematic situation and discovered an interesting behavior of unix_timestamp function of MySQL.


The task:

Say, he had a table which contained data along with the timestamp of the insertion time in a column. Now, his task was to find out all the entries on  a particular date.

The query:

His query was similar to this:

“select * from aTable where insertion_time = unix_timestamp(’2009-01-13′)“

Here, insertion_time is a column of type timestamp.

The result:

This query returns an empty result set however there was some entry on that date i.e. 2009-01-13!!

The cause:

unix_timestamp(’2009-01-13′) returns a timestamp for the first second on that day. But he had no entry on that time i.e.
on 0000hours on 13th January 2009:)
But all the entries were inserted on a later time!


The solution:

There are more than one solution to this problem, the simple one might be like this:

“select * from aTable where insertion_time >= unix_timestamp(’2009-01-13′) and insertion_time < unix_timestamp(’2009-01-14′) “

Category: mysql

Comments

2 Responses to “MySQL UNIX_TIMESTAMP problem while using in where clause to find out result from a date”

  1. hasin hayder
    January 19th, 2009 @ 12:54 am

    aman bhai

    this is an expected behavior from a typical date-to-timestamp converter function. if you want data for a particular date, there is an easier solution instead

    select data from aTable where from_unixtime(insertion_time, “%Y-%m-%d”)=”2009-01-13″

    it will save you few microseconds :)

  2. Shaikh Sonny Aman
    January 19th, 2009 @ 11:05 am

    Cool!! Thanks a lot hasin bhai :)

Leave a Reply