UTC and time zone conversions

[Bryan Haas]

I am working with a database which has a date filed that
uses milliseconds since 1/1/1970 (I believe termed as
UTC). I know what I need to do to convert it manually,
but need a way to apply some sort of function against it
so that end users in any state or country get a report
cut in their regional time zone. Is there any function
or look up table that can be applied to this? Obviously
this is well beyond a nested if/then statement (i.e. if
EST calculate X, if CST calculate Y etc..). Any help is
appreciated!!!

Thank you,
Bryan

 

[Douglas J. Steele]

milliseconds since 1/1/1970 isn't UTC. Rather, UTC is what used to be
referred to as GMT (Greenwich Mean Time). However, maybe I'm misreading your
post, and you're saying that when you convert the milliseconds since
1/1/1970 to a proper timestamp, you know that it's supposed to be in UTC
(actually, are you sure it's milliseconds since 1/1/1970? Usually it's just
seconds since 1/1/1970!)

In any case, Randy Birch has some sample code at
http://vbnet.mvps.org/code/locale/gettimezonebias.htm to "return various
time-related information with regard to the differences between a user's
local machine time and Greenwich Mean Time (GMT), in Windows called the
Coordinated Universal Time (UTC)."

Obligatory warning: Randy's site is aimed at VB programmers. Because of the
differences in controls available on forms in Access and VB, some of the
examples will not port into Access as-is. Looking at this particular
example, though, I don't think there should be any issue using it in Access.

 

[Douglas J. Steele]

Check http://vbnet.mvps.org/code/locale/timezonebiaslookup.htm at Randy's
site.

That one definitely will require some fussing to make it work in Access
because you can't just use .AddItem to add entries to a list box, but
hopefully it'll show you how you can retrieve the information.