Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
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. –  Christopher Ickes Jun 2 '11 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 '11 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. –  Christopher Ickes Jun 2 '11 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 '11 at 18:13
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.