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

3 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 :)

  3. 初雪
    December 9th, 2011 @ 2:27 pm

    Today, I also have had the same problem;I also want to be like you solve the problem,But he date of the change is to get,so the second data value should how to write.I write English may not standard, please forgive me.

Leave a Reply