[AccessD] Time zones (was: internationalization)

Gustav Brock gustav at cactus.dk
Wed Jul 14 08:57:48 CDT 2004


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