I want to store times in a database table but only need to store the hours and minutes. I know I could just use DATETIME and ignore the other components of the date, but what's the best way to do this without storing more info than I actually need?
+10
A:
You could store it as an integer of the number of minutes past midnight:
eg.
0 = 00:00
60 = 01:00
252 = 04:12
You would however need to write some code to reconstitute the time, but that shouldn't be tricky.
Macka
2009-02-11 20:59:01
Then use DATEADD() to get real times back. Even smallint would be enough.
Joel Coehoorn
2009-02-11 21:02:14
been there, done that ... mins=dd%60 and hours=dd/60 on ints does the trick.
Osama ALASSIRY
2009-02-14 00:37:24
That's a great, simple and straighforward idea. +1
Willi
2010-09-05 19:51:48
A:
I would convert them to an integer (HH*3600 + MM*60), and store it that way. Small storage size, and still easy enough to work with.
gms8994
2009-02-11 20:59:02
+1
A:
Try smalldatetime. It may not give you what you want but it will help you in your future needs in date/time manipulations.
MarlonRibunal
2009-02-11 20:59:52
+3
A:
since you didn't mention it bit if you are on SQL Server 2008 you can use the time datatype otherwise use minutes since midnight
SQLMenace
2009-02-11 21:01:41
+2
A:
Are you sure you will only ever need the hours and minutes? If you want to do anything meaningful with it (like for example compute time spans between two such data points) not having information about time zones and DST may give incorrect results. Time zones do maybe not apply in your case, but DST most certainly will.
mghie
2009-02-11 21:07:21
+2
A:
Just store a regular datetime and ignore everything else. Why spend extra time writing code that loads an int, manipulates it, and converts it into a datetime, when you could just load a datetime?
Seth
2009-02-11 21:15:14
A:
If you are using MySQL use a field type of TIME and the associated functionality that comes with TIME.
00:00:00 is standard unix time format.
If you ever have to look back and review the tables by hand, integers can be more confusing than an actual time stamp.
Syntax
2009-02-11 21:21:44
A:
SQL Server actually stores time as fractions of a day. For example, 1 whole day = value of 1. 12 hours is a value of 0.5.
If you want to store the time value without utilizing a DATETIME type, storing the time in a decimal form would suit that need, while also making conversion to a DATETIME simple.
For example:
SELECT CAST(0.5 AS DATETIME)
--1900-01-01 12:00:00.000
Storing the value as a DECIMAL(9,9) would consume 5 bytes. However, if precision to not of utmost importance, a REAL would consume only 4 bytes. In either case, aggregate calculation (i.e. mean time) can be easily calculated on numeric values, but not on Data/Time types.
Cadaeic
2009-02-14 09:13:14
"SQL Server actually stores time as fractions of a day." I think it stores days since (or before) 01-Jan-1900 in the first 4 bytes and time, in milliseconds, in the second 4 bytes. (SmallDateTime uses 2 bytes for each with narrower date range, and minutes, rather than milliseconds for time)
Kristen
2009-02-14 18:19:02
A:
Instead of minutes-past-midnight we store it as 24 hours clock, as an SMALLINT.
09:12 = 912 14:15 = 1415
when converting back to "human readable form" we just insert a colon ":" two characters from the right. Left-pad with zeros if you need to. Saves the mathematics each way, and uses a few fewer bytes (compared to varchar), plus enforces that the value is numeric (rather than alphanumeric)
Pretty goofy though ... there should have been a TIME datatype in MS SQL for many a year already IMHO ...
Kristen
2009-02-14 10:35:23
A:
The saving of time in UTC format can help better as Kristen suggested.
Make sure that you are using 24 hr clock because there is no meridian AM or PM be used in UTC.
eg. 4:12 AM - 0412 10:12 AM - 1012 2:28 PM - 1428 11:56 PM - 2356
Its still preferrable to use standard four digit format.
balamurugavel
2010-06-15 10:37:32