Universal Time (also known as UTC/Unix time/Posix time) is a DateTime value that stores the number of seconds since 1/1/1970 at the Royal Observatory in Greenwich, England.  The values for 2009 are around 1.2 billion. One of my readers did some work to convert these values into normal DateTime values and wanted to work on adjusting for her local time zones, as well as adjusting for Daylight Saving Time.   She contributed some logic and I polished it up a bit and the end result is posted below.


//Calculate UTC as a DateTime
DateTimeVar UTC:= DateAdd ('s' , {Your.UTCField} , Datetime (1970,1,1,0,0,0));
//Adjust UTC to Eastern Standard Time (or your time zone)
DateTimeVar EST:= DateAdd ('h' , -5 , UTC);
//The optional line below does the DST adjustment for 2009 in Eastern Time
If EST in DateTime (year(EST),3,8,2,0,0)  to DateTime (year (EST),11,1,0,59,59)
then DateAdd ('h' , 1 , EST)
else  EST

If you don’t know how to convert Greenwich time to your local US timezone you can refer to this chart.

In this example you would have to change the DST dates each year based on when DST starts and stops. I have a formula in Expert Techniques v3 that can automatically calculate these dates.   There are also custom function DLLs that you can use to do these calculations for you.  See my previous blog post for more information on using these DLLs.

Note that some databases have something like a UTC that counts seconds since 1900 and some mainframe systems even count one hundredths of a second.  If you don’t know how to make adjustments for these situations, give me a call.  And if you like tips like this you should check out my Experts Technique series.