UTC Time Offset Table (1 Viewer)

[Sweetnuff38]

I am trying to figure out how to handle working with ZULU Time in a database

Time Zone and difference to Universal Time
Dealing with 2 time zones
Dealing with daylight savings time

Someone suggested :establish a table with the ZULU offset for each time zone witht he logical field that identifies the default time zone for your computer. As for daylight savings time, the ZULU offset table could include a start and end date for daylight savings time by time zone."

No quite wrapping my brain around what its going to look like.

Does anyone have the patients for more input? If so highly appreciated!

Thanks
Tracy

 

[Sweetnuff38]

Help?.. just need a little push.. insight..

 

[jdraw]

This is the site I found a couple of years ago. It is often referenced, but there is a caution. The author, Randy, has coded for VB6 and some things "may" not port directly to Access.

http://vbnet.mvps.org/index.html?code/locale/gettimezonebias.htm

 

[DCrake]

This is a little demo of reading the time zones from the pc' registry

 

[jdraw]

David,

That's a nice tool. I had to play with code behind Form1 to get it to populate the text2 textbox. Does clicking an enty in the lstbox work in your copy?
I didn't realize that much info was held locally.

jack

 

[DCrake]

You can play with the listbox selected item to calculate or display the offset/time in the chosen location.

 

[DCrake]

Take a look at this Link This is where I sourced it from. It is in VB though.

As a caveat, I liked the custom buttons he made but could never convert them to work in Access.

 

[jdraw]

You can play with the listbox selected item to calculate or display the offset/time in the chosen location.

I tried to click on an entry in the list, but it didn't populate the textbox.

So I modified the code behind the form, and added a function that I had to get the GMT, and adjusted the current time base on the value in the listbox.

It now shows the current time for the entry I select in the list.

 

[DCrake]

That was the intention of the demo, but it looks like I never got around to finishing it. Although there is a sample in the Sample databases section if I remember correctly

 

[ChrisO]

Just a start, still work in progress…

Dump the following into a standard module and run Sub TestTheCall()

Option Explicit
Option Compare Text


[color=green]' Date/Time structure for the API calls[/color]
Private Type udtTimeOfDayInfo
    lngElapsedTime  As Long
    lngMilliSeconds As Long
    lngHour         As Long
    lngMin          As Long
    lngSecond       As Long
    lngHundreds     As Long
    lngTimeZone     As Long
    lngInterval     As Long
    lngDay          As Long
    lngMonth        As Long
    lngYear         As Long
    lngWeekday      As Long
End Type

[color=green]' From the http://allapi.mentalis.org/apilist/NetRemoteTOD.shtml[/color]
Private Declare Function NetRemoteTOD Lib "NETAPI32.DLL" (ByVal lngServer As Long, _
                                                          ByRef lngBufPtr As Long) As Long
 
Private Declare Function NetApiBufferFree Lib "NETAPI32.DLL" (ByVal lngBufPtr As Long) As Long

Private Declare Sub RtlMoveMemory Lib "kernel32" (ByRef pDest As Any, _
                                                  ByVal pSrc As Any, _
                                                  ByVal lngByteLen As Long)

[color=green]' Server name constant, insert as required to suit your system[/color]
Private Const conServerName As String = ""


Sub TestTheCall()

    MsgBox GetTimeFromServer(, "Local")
    MsgBox GetTimeFromServer(, "GMT")
    MsgBox GetTimeFromServer(conServerName, "Local")
    MsgBox GetTimeFromServer(conServerName, "GMT")

End Sub


Public Function GetTimeFromServer(Optional ByVal strServer As String = vbNullString, _
                                  Optional ByVal strLocation As String = "Local") As Variant
    
    [color=green]' Function: Gets the time of day from the specified server
    ' Original example by Chaz Branham (bran2@zande.com)
    ' Modified by Ken Henderson (1/2002) & ChrisO (6/2003)
    '
    ' Modified by ChrisO 4/2011 (Work in progress.)
    ' udtTODI.lngTimeZone is the local machine daylight saving time offset from GMT.
    ' To get Local machine time, add the lngTimeZone offset.
    ' To get GMT time, do NOT add lngTimeZone.[/color]

    Dim udtTODI   As udtTimeOfDayInfo
    Dim vntReturn As Variant
    Dim lngBufPtr As Long
    
    If NetRemoteTOD(StrPtr(strServer), lngBufPtr) = 0 Then
        RtlMoveMemory udtTODI, lngBufPtr, Len(udtTODI)
        
        With udtTODI
            Select Case strLocation
                Case "Local"
                    [color=green]' Raw GMT time + adjust for local time.[/color]
                    vntReturn = DateSerial(.lngYear, .lngMonth, .lngDay) + _
                                TimeSerial(.lngHour, .lngMin, .lngSecond) _
                                - (.lngTimeZone / 1440)
                Case "GMT"
                    [color=green]' Raw GMT time, no adjust.[/color]
                    vntReturn = DateSerial(.lngYear, .lngMonth, .lngDay) + _
                                TimeSerial(.lngHour, .lngMin, .lngSecond)
                
                Case Else
                    MsgBox "Only arguments allowed are 'Server name' and 'Local' or 'GMT' location." & _
                           vbNewLine & vbNewLine & _
                           "Please fix.", vbCritical + vbOKOnly, "No can do."
                           
                    vntReturn = "Twelfth of never."
            End Select
        End With
        
        NetApiBufferFree lngBufPtr
        GetTimeFromServer = vntReturn
        
    End If

End Function

Hope that helps for the moment.

Chris.

 

[Sweetnuff38]

Ugh... is there no easier way of dealing with this!! sigh... I found this but its backwards from what I need and not sure if it will work for what I need to do. It was posted to covert GMT to local.

Option Compare Database
'*** start code ***
Option Explicit
'following code adapted from
'http://www.access-programmers.co.uk/
' forums/showthread.php?s=&threadid=55810
Private Declare Function GetTimeZoneInformation Lib "kernel32" _
(lpTimeZoneInformation As TimeZoneInfo) As Long
Private Type SystemTime
intYear As Integer
intMonth As Integer
intwDayOfWeek As Integer
intDay As Integer
intHour As Integer
intMinute As Integer
intSecond As Integer
intMilliseconds As Integer
End Type
Private Type TimeZoneInfo
lngBias As Long
intStandardName(32) As Integer
intStandardDate As SystemTime
intStandardBias As Long
intDaylightName(32) As Integer
intDaylightDate As SystemTime
intDaylightBias As Long
End Type

Public Function fGetUTCLocalBiasMinutes() As Long
On Error GoTo Err_fGetUTCLocalBiasMinutes
Dim lngRet As Long
Dim udtTZI As TimeZoneInfo
'UTC = local time + bias
'The bias is the difference, in minutes,
'between UTC time and local time.
'==> local time = UTC - bias minutes

lngRet = GetTimeZoneInformation(udtTZI)
fGetUTCLocalBiasMinutes = udtTZI.lngBias
Exit_fGetUTCLocalBiasMinutes:
Exit Function
Err_fGetUTCLocalBiasMinutes:
MsgBox Err.Description
Resume Exit_fGetUTCLocalBiasMinutes
End Function
Public Function fGetUTCLocalBiasHours() As Long
On Error GoTo Err_fGetUTCLocalBiasHours
Dim lngRet As Long
Dim udtTZI As TimeZoneInfo
'UTC = local time + bias
'The bias is the difference, in minutes,
'between UTC time and local time.
'==> local time = UTC - (bias / 60) hours
lngRet = GetTimeZoneInformation(udtTZI)
fGetUTCLocalBiasHours = udtTZI.lngBias / 60
Exit_fGetUTCLocalBiasHours:
Exit Function
Err_fGetUTCLocalBiasHours:
MsgBox Err.Description
Resume Exit_fGetUTCLocalBiasHours
End Function
Public Function fConvertUTCtoLocalTime(pUTC As Date) As Date
On Error GoTo Err_fConvertUTCtoLocalTime
Dim lngRet As Long
Dim udtTZI As TimeZoneInfo
Dim lngBiasMinutes As Long
'UTC = local time + bias
'The bias is the difference, in minutes,
'between UTC time and local time.
'==> local time = UTC - bias
'==> local time = DateAdd("n",-bias, UTC)
lngRet = GetTimeZoneInformation(udtTZI)
lngBiasMinutes = udtTZI.lngBias
fConvertUTCtoLocalTime = DateAdd("n", -lngBiasMinutes, pUTC)
Exit_fConvertUTCtoLocalTime:
Exit Function
Err_fConvertUTCtoLocalTime:
MsgBox Err.Description
Resume Exit_fConvertUTCtoLocalTime
End Function
'*** end code ***

Any input would be extremely appreciated.

Tracy

 

[ChrisO]

Well, Tracy, I don’t think there is an easy way to do it but see if the attached Access 2003 demo helps.

It uses the local machine time but can be changed to use server time if you prefer.

Chris.

 

[darbid]

Please excuse me if your examples far exceed my simply input but if time is important here is what i would be looking at.

I think windows has a number of API functions

- GetLocalTime() returns the current local time.
- GetSystemTime() returns the UTC(GMT) time.

I would be aiming at always saving the UTC time in a table.

Thus if User 1 wants to save a time to your table you use the above to get the difference between the User 1's local time and UTC time. Then take the time he wants to save and apply the difference. Then save this in your table.

Then if User 2 gets Users 1's time back from the database first the UTC time is given to your code. You then get the difference between User 2's local and UTC using the above API functions and apply that to the retreived time from your database.

The most major assumption here is that the person has their computer time set correctly during daylight savings times.

 

[Sweetnuff38]

Please excuse me if your examples far exceed my simply input but if time is important here is what i would be looking at.

I think windows has a number of API functions

- GetLocalTime() returns the current local time.
- GetSystemTime() returns the UTC(GMT) time.

I would be aiming at always saving the UTC time in a table.

Thus if User 1 wants to save a time to your table you use the above to get the difference between the User 1's local time and UTC time. Then take the time he wants to save and apply the difference. Then save this in your table.

Then if User 2 gets Users 1's time back from the database first the UTC time is given to your code. You then get the difference between User 2's local and UTC using the above API functions and apply that to the retreived time from your database.

The most major assumption here is that the person has their computer time set correctly during daylight savings times.

I love simple.. I will try to digest this one... thanks

 

[Sweetnuff38]

Well, Tracy, I don’t think there is an easy way to do it but see if the attached Access 2003 demo helps.

It uses the local machine time but can be changed to use server time if you prefer.

Chris.

Thanks Ill have a look.

 

[Sweetnuff38]

Hmmm maybe I should mention as well that I only need to consider two time zones, Pacific and Mountain.....

Tracy

 

[ChrisO]

Maybe something like this attachment???

If you could give us a better idea of what you require, how you want to use it, we could nail it.

Chris.

 

[Sweetnuff38]

We cross time zones in travel and my need is to ensure that data entry is entered is such a way that it recognizes both the movement between pacific to mountain (vice versa) time zones as well as daylight savings...

Did that make any sense?

Tracy

 

[ChrisO]

Tracy

Your last description sounds to me like both the computer and data entry person are traveling between time zones and you need to automatically correct for the time zone and daylight saving time.

If that is correct I think we would have our work cut out.

The computer would need a GPS for location and a lookup table (large) for GPS to time zone conversion.

Am I getting any closer to the scope of work?

Chris.