ACCESS FEED: RE: [MS_AccessPros] now() with time zone adjustment

Kamis, 05 Juni 2014

RE: [MS_AccessPros] now() with time zone adjustment

Hi Liz

The computer already stores its time as UTC. I suggest you use API calls to get the system time and convert it to date/time format, and store that instead of Now().

Try the code below – not sure how well it will copy and paste from the forum L

It includes a function to get the current UTC time, which you can use instead of Now(), and some functions to convert both ways between UTC and local time. You don’t need to worry about the time zone because the system takes care of that.
============ Start code =================
Option Compare Text
Option Explicit

Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Private Type TIME_ZONE_INFORMATION
Bias As Long
'StandardName As String * 64
StandardName(0 To 31) As Integer
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(0 To 31) As Integer
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type

Private Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)

Private Declare Function SystemTimeToTzSpecificLocalTime _
Lib "kernel32" ( _
    lpTimeZoneInformation As TIME_ZONE_INFORMATION, _
    lpUniversalTime As SYSTEMTIME, _
    lpLocalTime As SYSTEMTIME _
) As Long

Private Declare Function TzSpecificLocalTimeToSystemTime _
Lib "kernel32" ( _
    lpTimeZoneInformation As TIME_ZONE_INFORMATION, _
    lpLocalTime As SYSTEMTIME, _
    lpUniversalTime As SYSTEMTIME _
) As Long

Private Declare Function GetTimeZoneInformation _
Lib "kernel32" ( _
    lpTimeZoneInformation As TIME_ZONE_INFORMATION _
) As Long

Private Function SystemTimeToDateTime(t As SYSTEMTIME) As Date
Const cMsPerDay = 86400000#
SystemTimeToDateTime = DateSerial(t.wYear, t.wMonth, t.wDay) _
    + TimeSerial(t.wHour, t.wMinute, t.wSecond) _
    + (t.wMilliseconds / cMsPerDay)
End Function

Private Function DateTimeToSystemTime(tDate As Date) As SYSTEMTIME
With DateTimeToSystemTime
    .wYear = YEAR(tDate)
    .wMonth = Month(tDate)
    .wDay = Day(tDate)
    .wHour = Hour(tDate)
    .wMinute = Minute(tDate)
    .wSecond = Second(tDate)
   .wMilliseconds = 0
End With
End Function

' Convert UTC date/time to the local time zone
Public Function UTCtoLocal(ByVal tDate As Date) As Date
Dim tzi As TIME_ZONE_INFORMATION
Dim stUTC As SYSTEMTIME
Dim stLocal As SYSTEMTIME
Dim lRes As Long
lRes = GetTimeZoneInformation(tzi)
stUTC = DateTimeToSystemTime(tDate)
lRes = SystemTimeToTzSpecificLocalTime(tzi, stUTC, stLocal)
UTCtoLocal = SystemTimeToDateTime(stLocal)
End Function

' Convert local date/time to UTC
Public Function LocalToUTC(ByVal dtLocal As Date) As Date
Const cMinsPerDay = 1440
Dim tzi As TIME_ZONE_INFORMATION
Dim stUTC As SYSTEMTIME
Dim stLocal As SYSTEMTIME
Dim dtUTC As Date
Dim lRes As Long
lRes = GetTimeZoneInformation(tzi)
stLocal = DateTimeToSystemTime(dtLocal)
lRes = TzSpecificLocalTimeToSystemTime(tzi, stLocal, stUTC)
LocalToUTC = SystemTimeToDateTime(stUTC)
End Function

' Return current time as UTC
Public Function UTCTime() As Date
Dim t As SYSTEMTIME
GetSystemTime t
UTCTime = SystemTimeToDateTime(t)
End Function

' Return the exact local time
' - like Now() but includes milliseconds
Public Function LocalTime() As Date
Dim t As SYSTEMTIME
GetLocalTime t
LocalTime = SystemTimeToDateTime(t)
End Function
================ End Code =============

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, 6 June 2014 01:07
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] now() with time zone adjustment

Pros, I have a database used in 5 different countries, India being one of them. I capture log in time and log out time as well as other activities with now(). Is there a way I can adjust the time because it is fouling up my stats. I just got informed I have 30 more users in India, so it is getting to be more important.

Respectfully,
Liz Ravenwood
Programmer / Analyst
B/E Aerospace | Super First Class Environments

1851 S Pantano Road | Tucson, Arizona 85710
Office +1.520.239.4808 |
beaerospace.com
Passion to Innovate. Power to Deliver

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.