Field Type to use for Elapsed Time in HH:MM?
-
Hi All,
I need to create a field in a table that will store elapsed time in hours and minutes. I'm not sure if I should use a datatime and find a way to format it or what to do. The field will store the amount of time that it takes for machines in a production facility to complete their jobs. Here is my create table statement:
CREATE TABLE ICSheduleDetail
(
DetailID INT IDENTITY,
MasterID INT,
Sequence INT,
SKU INT,
Machine INT,
TimeSpan --------------------should this be date time? will store values like 12:37 (this means 12 hours and 37 minutes)
)
Thanks for your help!
-
In order to simplify the process, you may just want to have two fields instead of one TimeSpan field.
You could create the table with StartTime and EndTime fields. You could set both of those to datetime or smalldatetime (see the link http://msdn.microsoft.com/en-us/library/aa258277(SQL.80).aspx for further info on smalldatetime).
With those two fields of course, you will always be able to subtract one from the other to define the time taken for any particular process.
Keith Wiggans
-
i agree with kw if the choice is up to you it is better to probably have 2 fields with the start time and the stop time rather than the one time field
As end users have a history of doing they will expand the information need like was that 12 hours in the morning, on saturday
-
I agree with the other individuals that you might be better suited to use two fields of datetime data type and then look at the differences.
If you want to do this in one field because its data coming from a business object or something then I might suggest using a data type of int. Store the data as a duration of seconds or minutes depending on the level of percision you need. So you can store a value of 1800 seconds and then have a simple function to convert it to hh/mm and report 00:30 or whatever you want.
-
If you really want to store the data in HH:MM then you could use a CLR UDT, this would allow you to put validations on the data to ensure MM<=60 and HH<=(12/24)
though it may be easier and more scalable to do as others have suggested and store the start/end time or store the data as an integer based on mins or secs.
-
It’s usually better to store elapsed time in a datetime column as an offset from zero time, 1900-01-01 00:00:00.000. It then allows you to use all the datetime functions to deal with it. If you subtract StartDatetime from EndDatetime (EndDatetime-StartDatetime), you will get this value directly.
More information here on this subject:
Date/Time Info and Script Links
-
Michael Valentine Jones (10/31/2008)
It’s usually better to store elapsed time in a datetime column as an offset from zero time, 1900-01-01 00:00:00.000. It then allows you to use all the datetime functions to deal with it. If you subtract StartDatetime from EndDatetime (EndDatetime-StartDatetime), you will get this value directly.More information here on this subject:
Date/Time Info and Script Links
Heh... FINALLY! The voice of reason! 😛
Elapsed times should NOT be stored as formatted hours and minutes nor do you need a bloody CLR for any of this. Michael is spot on... stored the elapsed time as a DateTime data type and format it when you need to output it. Otherwise, you'll play hell with things like getting a "Total Elapsed Time", etc, etc.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
As Michael and Jeff have said, the SOP way (favored by the pragmatists) to store elapsed time is as an offset datetime from the Zero datetime value. If you are sure that you only need resolution down to 1 minute, you could instead use SmallDatetime to save some space.
The other way to go with this (favored by the purists) would be to store the elapsed time as an integer number of time increments, typically seconds. Again, if you are certain about your minimum resolution, you could store the elapsed time as a number of minutes, instead. In this approach, an elapsed time of two hours and thirty-nine minutes (2:39:00), wuld be stored ans an INT (or SMALLINT) value of 159.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Just to get you started, I thought I would post these items for using elapsed time in datetime columns.
This is a solution for parsing elapsed time into days, hours, minutes, seconds, and Milliseconds.
select[Days] = datediff(day,0,ET-ST),
[Hours] = datepart(Hour,ET-ST),
[Minutes] = datepart(Minute,ET-ST),
[Seconds] = datepart(Second,ET-ST),
[Milliseconds] = datepart(millisecond,ET-ST)
from
(
select -- Test Data
ST = convert(datetime,'2008/09/22 00:35:33.997'),
ET = convert(datetime,'2009/10/22 01:05:45.443')
) a
Results:
Days Hours Minutes Seconds Milliseconds
----------- ----------- ----------- ----------- ------------
395 0 30 11 447
(1 row(s) affected)
One thing that is a bit difficult is summing elapsed datetime values. This is a general solution to sum elapsed datetime values and return the sum as a datetime value that is an offset from 1900-01-01 00:00:00.000.
selectTotalElapsedTime =
dateadd(dd,sum(datediff(dd,0,a.ET)),
dateadd(hh,sum(datepart(hh,a.ET)),
dateadd(mi,sum(datepart(mi,a.ET)),
dateadd(ss,sum(datepart(ss,a.ET)),
dateadd(ms,sum(datepart(ms,a.ET)),0)))))
from
( -- Test Data
select ET = convert(datetime,'1900-01-01 07:49:03.000') union all
select ET = convert(datetime,'1900-01-01 02:28:06.000') union all
select ET = convert(datetime,'1900-01-01 03:09:01.003') union all
select ET = convert(datetime,'1900-01-01 06:19:02.040') union all
select ET = convert(datetime,'1900-01-07 06:19:02.000')
) a
Results:
TotalElapsedTime
------------------------------------------------------
1900-01-08 02:04:14.043
(1 row(s) affected)
-
I think this points out how there is not one way to do things. I agree with rbarryyoung the most. There are really two options. We could argue about which is right for ever! 😀
But I think that a date style is over kill for what I read as his need based on the question. It appears to be a system for how long it takes to make a part (sku) on a specific machine. Why would he need to store the date? Duration is the only important aspect. If he needs the date versus the duration then the method of storing a date makes sense and would be correct. Otherwise its wasting overhead on the data type, physical space, etc not to just store duration as tiny/small/int etc. That being said, if the system is a couple thousand records maybe he does not care about the data type impact.
-
dmc (10/31/2008)
I think this points out how there is not one way to do things. I agree with rbarryyoung the most. There are really two options. We could argue about which is right for ever! 😀But I think that a date style is over kill for what I read as his need based on the question. It appears to be a system for how long it takes to make a part (sku) on a specific machine. Why would he need to store the date? Duration is the only important aspect. If he needs the date versus the duration then the method of storing a date makes sense and would be correct. Otherwise its wasting overhead on the data type, physical space, etc not to just store duration as tiny/small/int etc. That being said, if the system is a couple thousand records maybe he does not care about the data type impact.
It's a matter of not having to reinvent the wheel. storing it as date time allows you to use all sorts of built-in functions for formatting, extracting specific elements of the duration (which is a datetime element when you look at the type).
If you don't plan on "doing" anything much to it, then keeping it as an integer won't be the end of the world. Still - it's an implementation question, but I would tend to store it as a datetime, just because I can't and won't trust my users to "know" that they won't want higher precision at some later time in the future (which of course would be my fault if I were to believe them....).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Heh... here's just some of the problems you can run into if you mess around with dates, times, and durations...
http://qa.sqlservercentral.com/Forums/Topic595094-8-1.aspx
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Yes, and then you get into all sorts of issues with rounding, overflow, etc. if you use integers.
declare @t table (ST datetime, ET datetime)
print 'Load Test Data'
insert into @t
select
*
from
( --Test Data
select
ST = convert(datetime,'20081031 13:40:00.997'),
ET = convert(datetime,'20081031 13:40:01.000') union all
select ST = convert(datetime,'20081031 13:40:00.997'),
ET = convert(datetime,'20091031 13:40:01.000')
) a
print 'Elapsed_Time as datetime with accurate time'
select
Elapsed_Time = ET-ST
from
@t a
print 'Elapsed_Seconds, with rounding errors'
select
Elapsed_Seconds = datediff(ss,ST,ET)
from
@t a
print 'Elapsed_MS, with overflow error'
select
Elapsed_MS = datediff(ms,ST,ET)
from
@t a
Results:
Load Test Data
(2 row(s) affected)
Elapsed_Time as datetime with accurate time
Elapsed_Time
------------------------------------------------------
1900-01-01 00:00:00.003
1901-01-01 00:00:00.003
(2 row(s) affected)
Elapsed_Seconds, with rounding errors
Elapsed_Seconds
---------------
1
31536001
(2 row(s) affected)
Elapsed_MS, with overflow error
Elapsed_MS
-----------
3
(2 row(s) affected)
Server: Msg 535, Level 16, State 1, Line 29
Difference of two datetime columns caused overflow at runtime.
-
Thanks for all of these ideas. They've helped me narrow the scope of what I need. I'm most interested in the amount of time that machines take to produce certain products. My company makes many different flavors of products using the same equipment. This value will be used to create a schedule for the machine(s) that includes production, cleaning, maintenance, etc. I've elected to store the amount of time in seconds in an integer field. This answers the question of "How long does it take to make SKU #123?" The amount of time it takes to make each flavor is already known and fairly steady. I'm using seconds because they're granular enough for my purpose and the values will be easy to use. The user will still see HH:MM from the front-end, but the values will be stored as seconds.
Example Schedule (used to determine how much product can be produced in a given time). Schedules will be made weekly.
Make flavor #1: 3600 seconds
Clean: 600 seconds
Make flavor #2: 7200 seconds
Clean: 600 seconds
Maintenance: 1200 seconds
Thanks again for all of you comments!
😀
-
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply