GMT(UTC) date Storage Retrieval Questions

[David]

I'm planning on converting all my database times to UTC.

I've have all the procedures set up for converting LocalUTC
and UTCLocal.

One potential issue that comes to mind is what I call "Date RollUp"

That is the time period at UTC time for a date, which will break differently
than at the users TimeZone (e.g. Central Standard Time) when rolling up
"dependent" data because of the time (hh:mm:ss) the data was originally time
stamped (e.g. Central Standard Time). This may have
an effect on dependent field values. For example if you are taking sales
and you want the total sales that occurred during "a" day, the value
returned may be different based on how the query is constructed.

Another issue is all the date conversions (to and from the DB as well as any
date checks, queries, etc) that need to take place which "May" significantly
slow down processing.

Anyone had any experience storing date/times in UTC and offer any
suggestions or pitfalls of concern?

 

[Allen Browne]

I suggest you use two fields:
- a Date/Time to store the value in whatever suits (e.g. UTC.)
- an Integer field to store the number of minutes offset (plus or minus)
from your standard.

This arrangement:
a) Allows a very simple and efficient query manipulation, e.g.:
LocalDateTime: DateAdd("n", [MinutesOffset], [UTCDateTime])

b) Copes with all situations, including non-standard daylight savings
changes. For example, Sydney had different daylight saving rules for 2000
than for other years (due to holding the Olympic Games), and my city (Perth)
has no daylight saving at all this year even though it did for the 3
previous years. (Minutes offset is the most efficient solution for
fractional hour zones.)

c) Still only requires the user to enter a single date/time value for any
record. They only need to assign their offset once, and you can assign it as
a Default Value or in Form_BeforeInsert. (You can give them an unbound text
box to enter the local date/time, and use its AfterUpdate event to calculate
and store the UTC value.)

 

[ahmet ileri]

selamünaleyküm

hello

I'm planning on converting all my database times to UTC.

I've have all the procedures set up for converting LocalUTC
and UTCLocal.

One potential issue that comes to mind is what I call "Date RollUp"

That is the time period at UTC time for a date, which will break
differently than at the users TimeZone (e.g. Central Standard Time) when
rolling up "dependent" data because of the time (hh:mm:ss) the data was
originally time stamped (e.g. Central Standard Time). This may have
an effect on dependent field values. For example if you are taking sales
and you want the total sales that occurred during "a" day, the value
returned may be different based on how the query is constructed.

Another issue is all the date conversions (to and from the DB as well as
any date checks, queries, etc) that need to take place which "May"
significantly slow down processing.

Anyone had any experience storing date/times in UTC and offer any
suggestions or pitfalls of concern?



__________ ESET NOD32 Antivirus Akýllý Güvenlik tarafýndan saðlanan
bilgiler, virüs imza veritabaný sürümü: 4783 (20100118) __________

Ýleti ESET NOD32 Antivirus Akýllý Güvenlik tarafýndan denetlendi.

http://www.nod32.com.tr

__________ ESET NOD32 Antivirus Akýllý Güvenlik tarafýndan saðlanan bilgiler, virüs imza veritabaný sürümü: 4783 (20100118) __________

Ýleti ESET NOD32 Antivirus Akýllý Güvenlik tarafýndan denetlendi.

http://www.nod32.com.tr

 

[David]

Mr. Browne:

Thanks for response.

As you suggested, I had previously tried an offset and DateAdd for test.
purposes as one of my primary concerns is the amount of time it takes to
convert back and forth between the two (UTC other TZ). Seems like the
offset may be the most efficient.

Any experience with "rollups" and returning different values because of
date/time break differences --especially--where historical data already
exists?

I suggest you use two fields:
- a Date/Time to store the value in whatever suits (e.g. UTC.)
- an Integer field to store the number of minutes offset (plus or minus)
from your standard.

This arrangement:
a) Allows a very simple and efficient query manipulation, e.g.:
LocalDateTime: DateAdd("n", [MinutesOffset], [UTCDateTime])

b) Copes with all situations, including non-standard daylight savings
changes. For example, Sydney had different daylight saving rules for 2000
than for other years (due to holding the Olympic Games), and my city
(Perth) has no daylight saving at all this year even though it did for the
3 previous years. (Minutes offset is the most efficient solution for
fractional hour zones.)

c) Still only requires the user to enter a single date/time value for any
record. They only need to assign their offset once, and you can assign it
as a Default Value or in Form_BeforeInsert. (You can give them an unbound
text box to enter the local date/time, and use its AfterUpdate event to
calculate and store the UTC value.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm planning on converting all my database times to UTC.

I've have all the procedures set up for converting LocalUTC
and UTCLocal.

One potential issue that comes to mind is what I call "Date RollUp"

That is the time period at UTC time for a date, which will break
differently than at the users TimeZone (e.g. Central Standard Time) when
rolling up "dependent" data because of the time (hh:mm:ss) the data was
originally time stamped (e.g. Central Standard Time). This may have
an effect on dependent field values. For example if you are taking sales
and you want the total sales that occurred during "a" day, the value
returned may be different based on how the query is constructed.

Another issue is all the date conversions (to and from the DB as well as
any date checks, queries, etc) that need to take place which "May"
significantly slow down processing.

Anyone had any experience storing date/times in UTC and offer any
suggestions or pitfalls of concern?

 

[Allen Browne]

Any experience with "rollups" and returning different values because of
date/time break differences --especially--where historical data already
exists?

No. Others with experience here might contribute.

If you have lots of existing data, and you don't want to convert it, or most
calculations depend on the local time, you could go the other way: store the
local time, and the offset to UTC/GMT.