Solved

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

Posted on 2014-12-31
6
142 Views
Last Modified: 2015-01-02
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!
0
Comment
Question by:rberke
  • 3
  • 2
6 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 40526354
For now in the US, DST starts on the second Sunday in March and ends on the first Sunday in November.

mlmcc
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 300 total points
ID: 40526358
Here is a link to a full package of date and time related functions

http://www.cpearson.com/excel/DaylightSavings.htm

It was written by a Chip Pearson and he provides a link to download the source code.

mlmcc
0
 
LVL 35

Accepted Solution

by:
Miguel Oz earned 200 total points
ID: 40526916
Any particular reason to use GMT. The  preferred option is to use UTC. and then use Tolocaltime to convert your UTC time to local time. Basically it uses the time zone of the computer you are running to make the conversion.

If you require to display date time in different time zones please check Converting Times Between Time Zones article on how to convert your required local time to UTC and back to a different time zone.

Note: There is no need to use API as .NET framework has classes to help you dealing with date time and time zone.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:rberke
ID: 40527155
"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.
0
 
LVL 5

Author Comment

by:rberke
ID: 40527746
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
0
 
LVL 5

Author Closing Comment

by:rberke
ID: 40527757
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now