3

What is the correct way to store the TIME of day in SQL?

Have a field that cares only when an action took place. Example values will be 2:30 PM or 14:30, it doesn't matter to me which. This value will NEVER (ha, ha) be added, subtracted, compared, etc.. It is simply a static attribute.

Improve this question

2 Answers 2

Reset to default
6

If you're using Microsoft SQL Server or MySQL (I'm confident others will support this too, but those are the only 2 I know for definite) they both contain a dedicated data type for storing time values.

I'd always recommend storing things like dates and times in their dedicated formats (DATE, TIME, DATETIME etc) because then if you need to query the values or otherwise manipulate them, it makes writing SQL easier because the SQL engine is aware of what kind of data is in the field. If you stored dates or times in a VARCHAR for instance, you may or may not (database engine dependant) be able to write a query which specified SomeDateTimeField >= '2011-01-01 09:00:00' AND SomeDateTimeField <= '2011-01-01 17:00:00' without it puking, for example.

For reference, the SQL Server and MySQL TIME data type information is here.

Improve this answer
4
  • Because I wasn't aware of it... Wow! Knew DATETIME but was thinking more of using an int or something ridiculous. Thanks for the EASY answer.
    – csi
    Jun 2, 2011 at 17:59
  • No worries. That wasn't meant to be a snarky type of answer, more of a "if there's a reason you don't want to use the TIME data type please say". Reading my answer again, it kinda reads a bit "I am holier than thou", so lemme fix that.
    – Ben Pilbrow
    Jun 2, 2011 at 18:01
  • ha ha, not taken that way at all. The "WOW' was for my ignorance as in "wow, how did I miss that?" Much appreciated help. Thanks.
    – csi
    Jun 2, 2011 at 18:09
  • @Christopher Just a heads up, the TIME datatype was first introduced in MSSQL 2008. Prior to that only DATETIME is available.
    – squillman
    Jun 2, 2011 at 18:13
3

Many options exist - the big 3 are:

  1. Use an INTEGER storing Unix time_t values (Seconds since 01/01/1970)
    (If you go this route make sure you use at least a 64-bit integer)

  2. Use the SQL Date and Time types
    (TIME, DATETIME, TIMESTAMP, etc. - List of available types varies per DB server)

  3. Use a boring VARCHAR field
    (Don't do it. Your "NEVER" above will turn into "Well, just this once...")

The most "correct" way is to use the Date & Time types for your SQL implementation - This usually lets you use built-in date comparison and formatting commands.

A close second would be integer/time_t values as there are well-defined functions for converting & handling this representation of a date.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .