2 Apr 2008

Searching Problems with Dates and Midnight

Today, we run into an issue when a client of ours. They wanted to see some of the data on our website and when doing a search, they didnt see all the days they asked for in the search

Why didn't they see all the data? Because they did a search by date and some of the dates were stored via the website in the form of '2008-01-01 00:00:00' and some were stored via the database in the form of '2008-01-01 12:35:49'.
Now, for some magical reason, if you hide the time in the date in your searches, like so:
where signupdate between '2008-01-01' and '2008-01-02'
or
where signupdate between date('2008-01-01 00:00:00') and date('2008-01-02 00:00:00')
then you might not see all the data between the days 01 to 02.
Depending on which way your date was stored, you might only see the dates between those 2 dates and not equal to those dates as well.

So for example, if you had:

2008-01-01 00:00:00
2008-01-01 00:00:00
2008-01-02 00:00:00
2008-01-02 00:00:00
2008-01-03 00:00:00
2008-01-03 00:00:00

and you ran the search above, then you would see:

2008-01-01 00:00:00
2008-01-01 00:00:00

(I once worked for another company that booked hotel rooms online. For them when you booked the room, the nights were counted. So if you would do a search, on the website, all the date allocations would be set to midnight - 00:00:00.)

In our situation we had a mix of dates which caused use problems with 1 table and was ok with another.

We decided to solve the problem like this

where signupdate between '2008-01-01 00:00:00' and ' 2008-01-02 23:59:59'


That is, from what I understand, the time a day starts (00:00:00) and the time a day ends (23:59:59). So we figured we covered all the issues with this statement and we are happy with it.
I hope it helps you to if you also run into these kind of problems.

Reblog this post [with Zemanta]

1 comment:

  1. Sheeri K. Cabral3 April 2008 at 18:10

    Now, for some magical reason, if you hide the time in the date in your searches, like so:

    where signupdate between '2008-01-01' and '2008-01-02'
    or
    where signupdate between date('2008-01-01 00:00:00') and date('2008-01-02 00:00:00')

    then you might not see all the data between the days 01 to 02.

    It's not a magical reason -- if you don't specify the time, the time becomes "00:00:00". This includes implicit conversions, like when you compare a date to a datetime or timestamp.

    I'd suggest you use "date" when you mean "date" and "time" when you mean "time". "signupdate" is misleading, sounds like it's a date only when it's really a time.

    If you have a query that specifies "WHERE datetime_col BETWEEN '2008-01-01' and '2008-01-02'" then the implicit conversion is:

    "WHERE datetime_col BETWEEN '2008-01-01 00:00:00' and '2008-01-02 00:00:00'"

    Which is why you don't get all the dates you wanted.

    ReplyDelete