[AccessD] Time zones (was: internationalization)

Gustav Brock gustav at cactus.dk
Wed Sep 1 08:52:11 CDT 2004


Hi all

I just saw that Helen has published the part two of this horror code
and checked if anything had been changed - but no.

Just in case you should be involved in time zones, I will just say:
look everywhere else. 

But how do you US people deal with time zones? In Europe we seldom do
(the countries are too small) but you have to shift between Pacific
and Mountain etc. all day, except for local compannies, of course.

/gustav


> Hi all

> This topic popped up in all its glory in the recent issue of "Woody's
> Access Watch" where Helen Feddema is goofing around trying to parse
> date/time strings to figure out time zones. Of course, this dirty
> deroute is paved with traps so it doesn't work outside the US where
> other regional settings are used.

> Just in case anyone should have a need for dealing with time zones,
> here are a couple of functions we use. You'll quickly notice that
> essentially it is nothing more than a little clean up and one single
> line of code:

>   datRemote = DateAdd("n", lngBias, datLocal)

> If you have nothing to do Friday you may wish to browse Helen's demo:

>   ftp://ftp.helenfeddema.com/pub/accarch121.zip
  

> <code>

> Public Function TimeZoneBiasDiff( _
>   ByVal lngLocalTimeBias As Long, _
>   ByVal lngRemoteTimeBias As Long) _
>   As Long
  
> ' Calculates the difference in minutes between two time zones,
> ' typically the local time zone and a remote time zone.
> ' Both time zones must be expressed by their bias relative to
> ' UTC (Coordinated Universal Time) which is measured in minutes.
> '
> ' 2000-05-30. Cactus Data ApS, CPH.

>   ' Minimum amount of minutes for a time zone bias.
>   Const clngTimeZoneBiasMin As Long = 15

>   Dim lngTimeZoneBiasDiff As Long
  
>   ' Round off time zone bias by minimum time zone difference.
>   lngLocalTimeBias = lngLocalTimeBias \ clngTimeZoneBiasMin
>   lngLocalTimeBias = lngLocalTimeBias * clngTimeZoneBiasMin
>   lngRemoteTimeBias = lngRemoteTimeBias \ clngTimeZoneBiasMin
>   lngRemoteTimeBias = lngRemoteTimeBias * clngTimeZoneBiasMin
  
>   ' Calculate difference in time zone bias.
>   lngTimeZoneBiasDiff = lngRemoteTimeBias - lngLocalTimeBias
  
>   TimeZoneBiasDiff = lngTimeZoneBiasDiff

> End Function

> Public Function DateAddTimeZoneDiff( _
>   ByVal datLocal As Date, _
>   ByVal lngLocalBias, _
>   ByVal lngRemoteBias) _
>   As Date
  
> ' Calculates the date/time of datLocal in a remote time zone.
> ' The difference in minutes will be the difference between the
> ' local time zone bias and the remote time zone bias where the
> ' bias are relative to UTC.
> '
> ' Examples:
> '
> '   datRemote = DateAddTimeZoneDiff(Now(), 60, -600)
> ' will return datRemote as eleven hours behind local time.
> '
> '   datRemote = DateAddTimeZoneDiff(Now(), -600, 60)
> ' will return datRemote as eleven hours ahead of local time.
> '
> ' 2000-05-30. Cactus Data ApS, CPH.

>   Dim datRemote As Date
>   Dim lngBias   As Long
  
>   ' Get difference (in minutes) in time zone bias.
>   lngBias = TimeZoneBiasDiff(lngLocalBias, lngRemoteBias)
>   ' Calculate remote date/time.
>   datRemote = DateAdd("n", lngBias, datLocal)
  
>   DateAddTimeZoneDiff = datRemote
  
> End Function

> </code>

> Also, note "GMT" has been considered obsolete for years and has for
> every serious use been replaced with UTC.

> /gustav


>> On 27 Nov 2002 at 15:45, Gustav Brock wrote:

>>> Hi Drew
>>> 
>>> > Thanks.  Next version I'll incorporate that.
>>> 
>>> OK.
>>> 
>>> > Time zones are pretty easy.  In fact, all of this date stuff is too.  The
>>> > only real monster is Arizona, which decreed to ignore daylight savings time.
>>> 
>>> So how DO you deal with the time zones?
>>> 
>>> I have seen no options in Access for this contrary to Oracle which can
>>> record date/time including the time zone.
>>> 
>>> My thought was to use a byte field with a shifted value similar to how
>>> MySQL can store a year value in a byte field ...
>>> 

>> You could get the Bias member of the Time_Zone_Information structure 
>> retrieved with the GetTimeZoneInformation API function and either 
>> apply it to the time in question or store it in another field as a 
>> long (or integer if space is critical)
>>
>> Stuart




More information about the AccessD mailing list