Best Way of Storing a Time value

[Mark]

Hi,

Can anyone suggest the best way of storing a time value.

I have a simple db table and need to track time taken on a particular job -
if I choose Date/Time seems ok until a job takes more than 24 hrs.

 

[Fred]

Mark,

While your question was somewhat unclear, whatever it is, the storage /
calculation aspect is easy; it really needs to be defined by what
specifically you are trying to do.....how you intend to enter data, and
specifically what you want to get out of it.

For example:

Data/Time fields can store times precisely enough, and enable substractions
(e.g. between start and end times) to calculate elapsed time.

 

[Mark]

Hi,

OK - if I need to enter a time I use hh:mm format. I can enter 2:30 for 2
hrs 30 mins.

The real issue is if i need to enter 1 day, 2 hrs - i.e. 26 hours.

 

[John W. Vinson]

Hi,

Can anyone suggest the best way of storing a time value.

I have a simple db table and need to track time taken on a particular job -
if I choose Date/Time seems ok until a job takes more than 24 hrs.

Access Date/Time values are best used for storing specific points in time.
Internally they're stored as a Double Float number, a count of days and
fractions of a day since midnight, December 30, 1899. As such, a time value of
27 hours 30 minutes actually corresponds to #12/31/1899 03:30:00AM#; and there
is no format (as there is in Excel, say) to display this as 27:30.

Your best bet is to not store durations in a Date/Time field at all. You can
store start time (e.g. #4/29/2009 11:30AM#) and end time (#4/30/2009
08:45AM#), and then use DateDiff("n", [StartTime], [EndTime]) to get the time
elapsed in minutes. These integer minutes can be summed, averaged, etc. just
like any other number. To display the elapsed time you can use an expression
like

ShowTime: [elapsed] \ 60 & Format([elapsed] MOD 60, "\:00")

The \ isn't a typo, it's an integer divide operator.

 

[Mark]

Thank you very much for this. It does make me think more about this.
Am doubtful if I can record Start Time (date) and End Time (date) - it make
things very difficult to record the extra data.

Maybe it would be better to store the time taken as text and export this to
Excel?

Hi,

Can anyone suggest the best way of storing a time value.

I have a simple db table and need to track time taken on a particular job -
if I choose Date/Time seems ok until a job takes more than 24 hrs.

Access Date/Time values are best used for storing specific points in time.
Internally they're stored as a Double Float number, a count of days and
fractions of a day since midnight, December 30, 1899. As such, a time value of
27 hours 30 minutes actually corresponds to #12/31/1899 03:30:00AM#; and there
is no format (as there is in Excel, say) to display this as 27:30.

Your best bet is to not store durations in a Date/Time field at all. You can
store start time (e.g. #4/29/2009 11:30AM#) and end time (#4/30/2009
08:45AM#), and then use DateDiff("n", [StartTime], [EndTime]) to get the time
elapsed in minutes. These integer minutes can be summed, averaged, etc. just
like any other number. To display the elapsed time you can use an expression
like

ShowTime: [elapsed] \ 60 & Format([elapsed] MOD 60, "\:00")

The \ isn't a typo, it's an integer divide operator.

 

[John W. Vinson]

Thank you very much for this. It does make me think more about this.
Am doubtful if I can record Start Time (date) and End Time (date) - it make
things very difficult to record the extra data.

You can make a distinction between data STORAGE and data DISPLAY. They are two
different operations!

I'd really suggest storing the time duration in Long Integer minutes; you can
*display* it on a Form with two unbound textboxes for Hours and Minutes (with
a : in a label between them perhaps). There'd be an invisible textbox bound to
the duration field on the form.

You can populate the two textboxes in the form's Current event, e.g.

Private Sub Form_Current()
If Not IsNull(Me!txtDuration) Then
Me!txtHours = Me!txtDuration \ 60
Me!txtMinutes = Me!txtDuration MOD 60
End If
End Sub

You'ld also use code in the txtMinutes and txtHours textboxes' AfterUpdate
events to sum the hours * 60 to the minutes and populate the bound txtDuration
textbox.

Maybe it would be better to store the time taken as text and export this to
Excel?

If you really want to triple your effort and make your application that much
more complex... you're welcome to do so.