GMT timezone

[kidkosmo]

Hi, Gang,

So, I have a database that extracts data from an SQL server. When I
import that data, the incident open time is stated in GMT timezone.
One of the tasks I'm trying to perform is to calculate the amount of
time that incident has been open, but because the "Now()" function
uses the local system time, most of my calculations are coming out to
be negative numbers.

I considered using the formula "DateAdd([Now()],"h",-5)" to get to
Eastern timezone; however, my database is accessed by multiple people
within different timezones as well (ie. one person in New Jersey and
one in Seattle), so having a static "-5" doesn't quite work.

The question then: Is there a function that can automatically convert
local system time to GMT regardless of your timezone?

Thanks!

 

[Clifford Bass]

Hi,

Well, you might try to call out to one of the C runtime library
functions, _get_tzname or _get_timezone. I have tried the second without
success. I have not tried the first.

Alternatively, you might just get the date/time from the server and
then compare it to the local computer's time. Create a pass-through query
with this for the SQL:

select CURRENT_TIMESTAMP as CurrentTimeStamp;

Set it up so it does not always prompt you for the ODBC DSN. Open one
of your SQL Server linked tables in design view and display the property
sheet. Copy the contents of the Description property (it will start with
ODBC. Close and reselect the query so its property sheet shows. Paste the
text you just copied into the ODBC Connect Str property. Edit it to remove
the table part. You can also remove the APP part. You should end up with
something like (all one line):

ODBC;DSN=MyDatabaseDSNName;Description=Some
Description;DATABASE=MyDatabaseName;UseProcForPrepare=0

Close and save. Test by double-clicking the new query to see if it
works. You can now use this query to get the server's date and time. Now
obviously there will be a certain latency between the local computer and the
server computer. Maybe get the server's current date/time when Access starts
up. Then calculate the difference between the server's date/time and the
local computer's date/time in seconds. Store that in a public global
variable for use throughout the session. Alternatively, you can do that
process on the fly. Which may actually be wiser in case the user changes his
computer's clock. Or in case the user has the database open during a shift
between daylight saving time and standard time. Or in case the computer does
synchronization with a time server while the database is open. I think I
have just concluded that it is best to do it on the fly.

Hope that helps,

Clifford Bass

 

[Steve Sanford]

Clifford,

Just curious.... you said:

up. Then calculate the difference between the server's date/time and the
local computer's date/time in seconds.

Why use seconds??

Since the difference between timezones is whole hours, couldn't you just get
the server's time stamp, strip the minutes & seconds, calculate the hours
difference from GMT, calculate the hours difference from you to the server,
then use the difference to convert the local time to GMT.

Just wondering if I'm missing something...

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi,

Well, you might try to call out to one of the C runtime library
functions, _get_tzname or _get_timezone. I have tried the second without
success. I have not tried the first.

Alternatively, you might just get the date/time from the server and
then compare it to the local computer's time. Create a pass-through query
with this for the SQL:

select CURRENT_TIMESTAMP as CurrentTimeStamp;

Set it up so it does not always prompt you for the ODBC DSN. Open one
of your SQL Server linked tables in design view and display the property
sheet. Copy the contents of the Description property (it will start with
ODBC. Close and reselect the query so its property sheet shows. Paste the
text you just copied into the ODBC Connect Str property. Edit it to remove
the table part. You can also remove the APP part. You should end up with
something like (all one line):

ODBC;DSN=MyDatabaseDSNName;Description=Some
Description;DATABASE=MyDatabaseName;UseProcForPrepare=0

Close and save. Test by double-clicking the new query to see if it
works. You can now use this query to get the server's date and time. Now
obviously there will be a certain latency between the local computer and the
server computer. Maybe get the server's current date/time when Access starts
up. Then calculate the difference between the server's date/time and the
local computer's date/time in seconds. Store that in a public global
variable for use throughout the session. Alternatively, you can do that
process on the fly. Which may actually be wiser in case the user changes his
computer's clock. Or in case the user has the database open during a shift
between daylight saving time and standard time. Or in case the computer does
synchronization with a time server while the database is open. I think I
have just concluded that it is best to do it on the fly.

Hope that helps,

Clifford Bass

Hi, Gang,

So, I have a database that extracts data from an SQL server. When I
import that data, the incident open time is stated in GMT timezone.
One of the tasks I'm trying to perform is to calculate the amount of
time that incident has been open, but because the "Now()" function
uses the local system time, most of my calculations are coming out to
be negative numbers.

I considered using the formula "DateAdd([Now()],"h",-5)" to get to
Eastern timezone; however, my database is accessed by multiple people
within different timezones as well (ie. one person in New Jersey and
one in Seattle), so having a static "-5" doesn't quite work.

The question then: Is there a function that can automatically convert
local system time to GMT regardless of your timezone?

Thanks!

 

[Clifford Bass]

Hi Steve,

I was being guided by the _get_timezone function which returns seconds.
Plus, while you can control the server, you have to assume that the user's
computer may be off by more than the timezone. So if the server's time is
8:56 am and the user's coomputer is 9:05 am you might think it is in a
different timezone when in fact it is in the office next to the server room.
Add in to that that there are places in the world where their local time is
shifted by something other than an exact hour. See
<http://en.wikipedia.org/wiki/Timezone> for more information. So if you have
the seconds, you can calculate the server time from the local time, and from
there you can calculate GMT, taking into account daylight saving
time/standard time issues. Probably idealy, you would acquire GMT from a
trusted timeserver and just not mess with the above shenanagins.

Clifford Bass

 

[kidkosmo]

Hi Steve,

     I was being guided by the _get_timezone function which returnsseconds.
 Plus, while you can control the server, you have to assume that the user's
computer may be off by more than the timezone.  So if the server's timeis
8:56 am and the user's coomputer is 9:05 am you might think it is in a
different timezone when in fact it is in the office next to the server room.  
Add in to that that there are places in the world where their local time is
shifted by something other than an exact hour.  See
<http://en.wikipedia.org/wiki/Timezone> for more information.  So if you have
the seconds, you can calculate the server time from the local time, and from
there you can calculateGMT, taking into account daylight saving
time/standard time issues.  Probably idealy, you would acquireGMTfrom a
trusted timeserver and just not mess with the above shenanagins.

                      Clifford Bass









- Show quoted text -

I found a bit of code from Chip Pearson. Seems to work just fine for
my Access needs.

www.cpearson.com/Excel/TimeZoneAndDaylightTime.aspx

Thank you for the input.

 

[Steve Sanford]

Thanks for the explanation, Clifford. I didn't know that a few places were
using less that a 1 hour time zone difference.

I haven't had to deal with time zone calculations yet, but I will tuck this
away for the future (UTC-9).

Thanks again

 

[Clifford Bass]

Hi,

You are welcome. I did see that function also, but realized it would
take a certain amount of work to use it. Glad to see that someone else has
figured it out. Now, the question I have is, what happens when the user's
computer has the wrong time or when it has the wrong timezone configured? Do
you still get the correct GMT?

Clifford Bass

 

[Clifford Bass]

Hi Steve,

You are welcome. There was a time when I thought I would have to deal
with them, but so far we have avoided the issue even though we have an
admissions application that anyone in the world can access. Sometimes the
non-technical solution is easier. We simply state that the deadline is such
and such in our timezone. And on the web page it tells them our current
date/time along with the deadline when they access the page.

When date and time are critical, my personal opinion is to rely only on
the database server's time, because that is the only one you have control
over and therefore can ensure its accuracy, if at all possible. Only use the
local computer's time for approximations.

Clifford Bass