asked on

Is a specific day EST or EDT (daylight saving) using Windows API

I need a GmtToObservedTime function that will convert an Eastern Standard Time date to the "Observed Date".

For instance in New York City on Sunday 11/2/2014 I sent email #1 at 5:55 GMT and one hour later email #2 was sent at 6:55 GMT. My watch was set to Eastern Standard Time, so email #1 looked to me like it went out at 1:55 AM.

At 2:30 AM that Sunday morning I reset my watch to "fall back". So, it looked to me like email #2 WAS ALSO SENT AT 1:55.

I want my routine to run now (in December), but to show the same thing my watch showed that morning. In other words both emails should show as being sent at 1:55 AM Sunday 11/2/2014.

I want to use a windows api so that my routine will work for many years.

I modified the following code from http://www.cpearson.com/excel/LocalAndGMTTimes.htm and it will "work" for the next few years.

How can I replace the hard-coded date ranges near line 70 with an API call, so the code will work for many years?

Option Explicit
' Private Const TIME_ZONE_ID_UNKNOWN = 0
' Private Const TIME_ZONE_ID_STANDARD = 1
' Private Const TIME_ZONE_ID_DAYLIGHT = 2
' Private Const TIME_ZONE_ID_INVALID = -1
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(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 Function GetTimeZoneInformation Lib "kernel32" ( _
    lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long



Public Function GMTToLocaltime(GMT As Date) As Date
' this converts GMT to LocalTime.
' When run in eastern USA during December  #6/1/2014 8:00:00 AM# WILL BECOME #6/1/2014 3:00:00 PM#
' When run in eastern USA during July      #6/1/2014 8:00:00 AM# WILL BECOME #6/1/2014 4:00:00 PM#
'

 Const TIME_ZONE_ID_DAYLIGHT = 2
Dim TZI As TIME_ZONE_INFORMATION
Dim IsRunningDST As Boolean

If GetTimeZoneInformation(TZI) = TIME_ZONE_ID_DAYLIGHT Then IsRunningDST = True

GMTToLocaltime = GMT - time(0, TZI.Bias, 0) + time(Abs(IsRunningDST), 0, 0)
End Function




Public Function GMTToObservedtime(GMT As Date) As Date
' this converts GMT to LocalTime as it was being observed on my computer AT THAT TIME.
'
' for instance on Sunday 11/2/2014 I sent email #1 at 5:55 GMT and one hour later email #2 was sent at 6:55 GMT
' I reset my watch that Sunday so my watch showed that both emails were sent at 1:55.
' Today, I run the following on a computer with Eastern Time Zone. I should see the following
'   Debug.Print GMTToObservedtime(#3/9/2014 6:55:00 AM#) ' should show  #3/9/2014 1:55:00#)
'   Debug.Print GMTToObservedtime(#3/9/2014 7:55:00 AM#) ' should show  #3/9/2014 3:55:00#)

'   Debug.Print GMTToObservedtime(#11/2/2014 5:55:00 AM#) ' should show  #11/2/2014 1:55:00#)
'   Debug.Print GMTToObservedtime(#11/2/2014 6:55:00 AM#) ' should show  #11/2/2014 1:55:00#)

Dim TZI As TIME_ZONE_INFORMATION
Dim UseDst As Long

Call GetTimeZoneInformation(TZI)


GMTToObservedtime = GMT - TimeSerial(0, TZI.Bias, 0)
Select Case GMTToObservedtime
    Case #3/10/2013 2:00:00 AM# To #11/3/2013 1:00:00 AM#: Stop: UseDst = 1
    Case #3/9/2014 2:00:00 AM# To #11/2/2014 1:00:00 AM#: Stop: UseDst = 1
    Case #3/8/2015 2:00:00 AM# To #11/1/2015 1:00:00 AM#: Stop: UseDst = 1
    Case #3/13/2016 2:00:00 AM# To #11/6/2016 1:00:00 AM#: Stop: UseDst = 1
    Case #3/12/2017 2:00:00 AM# To #11/5/2017 1:00:00 AM#: Stop: UseDst = 1

End Select
GMTToObservedtime = GMTToObservedtime + TimeSerial(UseDst, 0, 0)
End Function
Sub t605()
Debug.Print GMTToObservedtime(#3/9/2014 6:55:00 AM#) ' should show  #3/9/2014 1:55:00#)
Debug.Print GMTToObservedtime(#3/9/2014 7:55:00 AM#) ' should show  #3/9/2014 3:55:00#)

Debug.Print GMTToObservedtime(#11/2/2014 5:55:00 AM#) ' should show  #11/2/2014 1:55:00#)
Debug.Print GMTToObservedtime(#11/2/2014 6:55:00 AM#) ' should show  #11/2/2014 1:55:00#)

End Sub

Open in new window

For giggles, you might also want to look at

more-falsehoods-programmers-believe-about-time

For instance, I learned that it’s "daylight saving time" and not "daylight savings time". I’ve been saying it wrong my whole life!

Mike McCracken

For now in the US, DST starts on the second Sunday in March and ends on the first Sunday in November.

mlmcc

SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Miguel Oz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Berke

ASKER

"Observed time" is my own concept, but I wonder if there is a more formal name.

Converting UTC to localtime does not match the ObservedTime concept.

GMTToObservedtime(#3/9/2014 7:55:00 AM#) ' should show #3/9/2014 3:55:00#) whereas
GMTToLocalTime(#3/9/2014 7:55:00 AM#) ' would show #3/9/2014 2:55:00#)

The DST rules cited by the experts will work for my needs, for instance TransitionDateDstToStandard(YYYY As Integer) in the Pearson routines.

But, what if I wanted the routine to work in other timezones? For instance daylight time in southern hemisphere and europe have different transitions.

that is why I expected to find an API to handle mutliple time zones.
Also, I am using vba, not .net.

Robert Berke

ASKER

When I get some more time, I will convert the following vb code into vba. For right now, I will simply use TransitionDateDstToStandardYYYY code from the cpearson link.

Thanks for everybody's help.

Bob

Try
Dim cstZone As TimeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time")
Dim cstTime As Date = TimeZoneInfo.ConvertTimeFromUtc(timeUtc, cstZone)
Console.WriteLine("The date and time are {0} {1}.", _
cstTime, _
IIf(cstZone.IsDaylightSavingTime(cstTime), _
cstZone.DaylightName, cstZone.StandardName))
Catch e As TimeZoneNotFoundException
Console.WriteLine("The registry does not define the Central Standard Time zone.")
Catch e As InvalidTimeZoneException
Console.WriteLine("Registry data on the Central Standard Time zone has been corrupted.")
End Try

rberke

Robert Berke

ASKER

mimcc gets more points, because he has the solution I used.

Miguel gets "best solution" because he gave a link to a vb routine that will work for many different time zones. The only reason I didn't use that link is that I don't have time to convert it from vb to vba.