Store time of the day in SQL

views:

1103

answers:

+3 Q:

Store time of the day in SQL

How would you store a time or time range in SQL? It won't be a datetime because it will just be let's say 4:30PM (not, January 3rd, 4:30pm). Those would be weekly, or daily meetings. The type of queries that I need are of course be for display, but also later will include complex queries such as avoiding conflicts in schedule. I'd rather pick the best datatype for that now.

I'm using MS SQL Server Express 2005.

Thanks!

Nathan

+3 A:

I would recommend still using a DateTime data type and ignoring the date values--ideally using the static MinDate for SQL (Google it). This will give you the benefits of working with a strongly typed field and the only cost will be a few extra bytes.

As for ranges, store them in two separate columns. Then you can subtract one from the other to determine the difference.

Edit: did some Googling.

SQL Server 2008 adds a Time data type, so you might want to consider that.
You can use SQL 2005's DateTime type and combine it with the CONVERT function to extract just the HH:MM:SS.MMM
- SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS(24H)] (Found on this handy-dandy page)
Different SQL versions support different minimum dates. You could use a static date that will be supported by all such as 1/1/2000, or you could use SQL 2005's minimum value of 1/1/1753 and append the time values to that startic day

So if you stick with 2005, pick your static date, like 1/1/2000, and store your times on it. So 1m:30s would be 2000-1-1 00:01:30.000, and 1h:15m would be 2000-1-1 01:15:00.000

You can then do Date2 - Date1 and get your result of (1h:15:m - 1m:30s) 2000-01-01 01:13:45.000. CONVERT it and you'll have 1:13:45.

STW 2009-05-01 18:37:44

note that using CONVERT in this way will disable any use of indexes on the datetime column

Mladen Prajdic 2009-05-01 20:25:42

Interesting, I wasn't aware of that effect. Thanks for the tip

STW 2009-05-01 21:48:14

+1 A:

You could store it as an int as 24 hour time and format as needed.

Or store it as a datetime with some fixed date and remove it as needed for display: Jan 1 2000 4:30PM

I would go with datetime field as it gives you the power of all the datetime related functionality.

schooner 2009-05-01 18:38:49

You might want to consider storing it as an int column representing the number of minutes since midnight. In your entity you could expose this as a TimeSpan (or int) representing the same thing. You'd only need to convert between your display values (time format) and the database value (minutes) in order to perform your queries and this could easily be done in your entity (TimeSpan.TotalMinutes, for example).

tvanfosson 2009-05-01 18:43:35

That's a fair solution, but isn't intuitive looking at the table or easy to alter (what if they want to raise the resolution? They would have to modify existing values). Using a provided Data Type, especially for data with intrinsic rules such as time, avoids having to do lots of manual math--and also makes it much more obvious to future developers.

STW 2009-05-01 18:46:23

Assuming the required precision is seconds, an alternative is an int in the format hhmmss. Not as useful if you need to calculate a difference between two times, but makes for more readable queries - WHERE Time >= 120000 AND Time < 130000.

Joe 2009-05-01 18:56:58

@Yooder -- it's precisely because using a DateTime might confuse future developers that I would avoid it for this purpose. Typically with a DateTime you would expect that the date portion would be valid. What happens if the future developer decides to use some other date as the fixed point (or uses DateTime.Today). Storing it as a column with a suitable name "MinutesFromMidnight" makes it very clear what the intent is. The math works out about the same 2:30PM is still after 1:30PM and before 2:30PM. Conversion is trivial: TimeSpan.FromMinutes(). TimeSpan also formats easily.

tvanfosson 2009-05-01 19:18:38

+2 A:

Personally I would find this a reason to upgrade to 2008 which has a separate time datatype.

HLGEM 2009-05-01 19:05:50

I second that notion, especially since we're talking about the Express edition (which I hope means that this isn't a big production system). A little upgrade pain would be worth it here.

STW 2009-05-01 22:25:31

+1 Agreed......

James Westgate 2010-04-27 22:25:46

to me it sounds like you're developing a type of meeting scheduler or something to display the meetings.

i think that i would set it p with 2 columns MeetingStart and MeetingEnd, both as datetime fields. This way, you can determine the length of the meeting, and since you already have the date you can easily use it to display it on a calendar or something.

DForck42 2009-05-02 19:13:49

ansaurus

tags:

views:

answers:

Store time of the day in SQL

related questions