Daylight Saving Time Error
It’s DST here in the US and I couldn’t be happier. I don’t care if my drive to work is pitch black, but the drive home? That’s another story.
One thing I learned since the clocks sprung forward is that the GetTimeZoneInformation API doesn’t work the way I thought. The TIME_ZONE_INFORMATION return type has a Bias property. Bias tells you how many minutes you are away from GMT. Or so I thought. It actually tells you how many minutes you are from GMT in standard time. The TIME_ZONE return value tells you if it’s daylight saving time or standard time. So you have to take both into account to get the correct time.
Here’s the API declaration
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(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
”””””””””””””””””””””””
‘ These give symbolic names to the time zone
‘ values returned by GetTimeZoneInformation .
”””””””””””””””””””””””
Private Enum TIME_ZONE
TIME_ZONE_ID_INVALID = 0 ‘ Cannot determine DST
TIME_ZONE_STANDARD = 1 ‘ Standard Time, not Daylight
TIME_ZONE_DAYLIGHT = 2 ‘ Daylight Time, not Standard
End Enum
Private Declare Function GetTimeZoneInformation Lib "kernel32" _
(lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
Private Declare Sub GetSystemTime Lib "kernel32" _
(lpSystemTime As SYSTEMTIME)
And the updated procedure:
Dim tzi As TIME_ZONE_INFORMATION
Dim tz As TIME_ZONE
Dim lGmtOff As Long
tz = GetTimeZoneInformation(tzi)
Select Case UCase(sZone)
Case "EDT"
lGmtOff = -4
Case "EST", "CDT"
lGmtOff = -5
Case "CST", "MDT"
lGmtOff = -6
Case "MST", "PDT"
lGmtOff = -7
Case "PST"
lGmtOff = -8
Case vbNullString
lGmtOff = -tzi.Bias / 60
End Select
If tz = TIME_ZONE_DAYLIGHT Then lGmtOff = lGmtOff – 1
ConvertTimeToLocal = dtTime – (TimeSerial(0, tzi.Bias, 0) + TimeSerial(lGmtOff, 0, 0))
End Function
I also added a UCase around the zone because it’s just stupid not to have that. Enjoy saving the daylight, but remember you’ll owe it back this fall.
Update:
That’s why we write tests people.
Dim tzi As TIME_ZONE_INFORMATION
Dim tz As TIME_ZONE
Dim lGmtOff As Long
Dim lBias As Long
tz = GetTimeZoneInformation(tzi)
lBias = tzi.Bias
If tz = TIME_ZONE_DAYLIGHT Then lBias = lBias – 60
Select Case UCase(sZone)
Case "EDT"
lGmtOff = -4
Case "EST", "CDT"
lGmtOff = -5
Case "CST", "MDT"
lGmtOff = -6
Case "MST", "PDT"
lGmtOff = -7
Case "PST"
lGmtOff = -8
Case vbNullString
lGmtOff = -tzi.Bias / 60
If tz = TIME_ZONE_DAYLIGHT Then lGmtOff = lGmtOff + 1
End Select
ConvertTimeToLocal = dtTime + (TimeSerial(0, lBias, 0) + TimeSerial(lGmtOff, 0, 0))
End Function
Public Sub Test_ConvertTimeToLocal()
Dim dtTestTime As Date
dtTestTime = TimeSerial(9, 46, 13)
Debug.Assert (ConvertTimeToLocal(dtTestTime, vbNullString) – dtTestTime) < TimeSerial(0, 0, 1)
Debug.Assert (ConvertTimeToLocal(dtTestTime, "CDT") – dtTestTime) < TimeSerial(0, 0, 1)
Debug.Assert (ConvertTimeToLocal(dtTestTime, "EST") – dtTestTime) < TimeSerial(0, 0, 1)
Debug.Assert (ConvertTimeToLocal(dtTestTime, "EDT") – (dtTestTime + TimeSerial(1, 0, 0))) < TimeSerial(0, 0, 1)
Debug.Assert (ConvertTimeToLocal(dtTestTime, "CST") – (dtTestTime – TimeSerial(1, 0, 0))) < TimeSerial(0, 0, 1)
Debug.Assert (ConvertTimeToLocal(dtTestTime, "MDT") – (dtTestTime – TimeSerial(1, 0, 0))) < TimeSerial(0, 0, 1)
Debug.Assert (ConvertTimeToLocal(dtTestTime, "MST") – (dtTestTime – TimeSerial(2, 0, 0))) < TimeSerial(0, 0, 1)
Debug.Assert (ConvertTimeToLocal(dtTestTime, "PDT") – (dtTestTime – TimeSerial(2, 0, 0))) < TimeSerial(0, 0, 1)
Debug.Assert (ConvertTimeToLocal(dtTestTime, "PST") – (dtTestTime – TimeSerial(3, 0, 0))) < TimeSerial(0, 0, 1)
End Sub