Problems With Storing Date And Time Separately In SQL Server
Posted July 27, 2006 at 9:17 AM by Ben Nadel
I just added links for previous and next blog posts at the bottom of every blog entry detail page. I stumbled a bit when trying to query for the previous and next posts because of the way I store the date and time of posting in the database. Over time I have learned it is easier to store date and time separately where date is in a DATETIME field (with zero time) and the time is stored as 24 hour time in a VARCHAR(5) field (ex. 17:15). This makes manipulation and date comparison easy... most of the time.
In this case, however, it did cause a slight problem. Having already queried for the detail blog post, my initial query went something like this (for shorter code, assume that @id is the current entry ID and that @date_posted is the current entry's date of posting):
- <!--- Query for next post. --->
- <cfquery name="REQUEST.NextQuery" datasource="..." username="..." password="...">
- SELECT TOP 1
- b.id,
- b.name,
- b.date_posted,
- b.time_posted
- FROM
- blog_entry b
- WHERE
- <!--- Exclude the current entry. --->
- b.id != @id
- AND
- <!--- Get only newish entries. --->
- b.date_posted >= @date_posted
- ORDER BY
- b.date_posted ASC,
- b.time_posted ASC
- </cfquery>
-
-
- <!--- Query for prev post. --->
- <cfquery name="REQUEST.PrevQuery" datasource="..." username="..." password="...">
- SELECT TOP 1
- b.id,
- b.name,
- b.date_posted,
- b.time_posted
- FROM
- blog_entry b
- WHERE
- <!--- Exclude the current entry. --->
- b.id != @id
- AND
- <!--- Get only oldish entries. --->
- b.date_posted <= @date_posted
- ORDER BY
- b.date_posted DESC,
- b.time_posted DESC
- </cfquery>
This worked most of the time so the problem wasn't immediately apparent. Sometimes though, the previous and next entries were the same link. After doing some CFDump'ing, I realized that the time_posted field was causing issues, or rather, the fact that it was stored separately was causing issues.
Take for example the three posts:
A. 7/27/2006 - 10:00 AM
B. 7/27/2006 - 09:00 AM
C. 7/26/2006 - 12:00 PM
Now, let's assume that post (B) is the current post. If I were to query for the next and previous posts based only on the date field (as the queries above do), I would get post (A) for both the previous and next results since date equality is allowed. After that, sorting on the time posted is moot as the data is already invalid.
To overcome this, I had to start taking the time into account in the WHERE clause, not just in the ORDER BY. The result is a little hacky, but seems to do the trick (for shorter code, assume that @id is the current entry ID, that @date_posted is the current entry's date of posting, and that @time_posted is the current entry's time of posting):
- <!--- Query for the next post. --->
- <cfquery name="REQUEST.NextQuery" datasource="..." username="..." password="...">
- SELECT TOP 1
- b.id,
- b.name,
- b.date_posted,
- b.time_posted
- FROM
- blog_entry b
- WHERE
- <!--- Exclude the current entry. --->
- b.id != @id
- AND
- <!--- Get only newish entries. --->
- b.date_posted >= @date_posted
- <!---
- We have to fudge it a bit here to take the time of
- posting into account.
- --->
- AND
- (
- <!---
- We only need to take the time of posting into account
- if we are looking at the date of the current post.
- Otherwise, time of posting is irrelevant.
- --->
- CASE
- WHEN
- b.date_posted = @date_posted
- THEN
- CASE
- WHEN
- b.time_posted >= @time_posted
- THEN
- 1
- ELSE
- 0
- END
- ELSE
- 1
- END
- ) = 1
- ORDER BY
- b.date_posted ASC,
- b.time_posted ASC
- </cfquery>
-
-
- <!--- Query for the previous post. --->
- <cfquery name="REQUEST.PrevQuery" datasource="..." username="..." password="...">
- SELECT TOP 1
- b.id,
- b.name,
- b.date_posted,
- b.time_posted
- FROM
- blog_entry b
- WHERE
- <!--- Exclude the current entry. --->
- b.id != @id
- AND
- <!--- Get only oldish entries. --->
- b.date_posted <= @date_posted
- <!---
- We have to fudge it a bit here to take the time of
- posting into account.
- --->
- AND
- (
- <!---
- We only need to take the time of posting into account
- if we are looking at the date of the current post.
- Otherwise, time of posting is irrelevant.
- --->
- CASE
- WHEN
- b.date_posted = @date_posted
- THEN
- CASE
- WHEN
- b.time_posted <= @time_posted
- THEN
- 1
- ELSE
- 0
- END
- ELSE
- 1
- END
- ) = 1
- ORDER BY
- b.date_posted DESC,
- b.time_posted DESC
- </cfquery>
As you can see above, I now have to take both date and time into account when doing the comparison (not just the sorting) since they are stored separately. However, time is not always relevant; if the entries were posted on different days, I only care about the time posted when sorting, not elimination. I only have to take time into account for entries posted on the same day. Not the end of the world, but it makes for a less elegant query. I still think that in the long run, storing date and time separately has many more benefits than draw backs.
