Rick Strahl's FoxPro and Web Connection Web Log
White Papers | Products | Message Board | News |

UTC Time in FoxPro


Monday, June 9, 2014, 11:07:46 PM

Representing dates and times across timezones can be a challenge especially if you don’t lay out a plan up front on how to store dates consistently. The sneaky thing with date time management in larger applications and especially applications that live on the Web or are shared across locations, is that problems don’t usually show up until much later in the lifetime of the application. For FoxPro in particular it’s not natural to store dates in anything but local machine format as the language doesn’t support direct UTC formats so it’s very common to see FoxPro applications use local dates which is usually a bad idea.

Here’s why and how we can address these issues…

TimeZones and Offsets

Depending on where you are in the world your local time is defined by an offset from UTC (Coordinated Universal Time) time or the baseline zero time. As you know if you’ve ever talked to somebody half way across the world at a certain time of day, while you just got done with breakfast, they are getting ready to go to bed on the other side of the world. This is the timezone offset. If you build applications that deal with customers that enter data into a system from multiple locations then using local times becomes problematic.

The problem of local times is made worse by Daylight Savings time. Most of the world – especially those further away from the equator – have daylight savings time which is applied on different dates in different locations around the world. Usually it’s “spring forward” and “fall back” with time getting set one hour forward for the summer.  Some countries have it, others don’t, and surprisingly – some countries actually half hour DST offsets. You can see where this is going – dealing with dates from multiple locations around the world can get complicated fast.

UTC Dates

A generally accepted solution to this problem is to store date values using a single time format that is adjusted from local time. Typically this date format is UTC time – or zero offset time.

I often get questions about why you should – and you REALLY, REALLY should - store dates in UTC format. The simple reason is: Things change! You never know how the data that you are capturing today will be used in the future. Maybe today you’re using the data in one location but maybe in the future you have a Web application and you have multiple locations that access the data. Or you end up building a service for other people to consume your data. If your data is in local time, the data will be much less useful then being in a universal format.

While it’s possible to convert data later on.

In fact on this project I worked on the client insisted on going with local dates over my vehement protests. The argument almost always is that ‘hey, we just have one location – everybody’s running in this domain and we want to see data from the database in local time.’ Convenient – yes absolutely. A good idea: NEVER! In every application where this has come up for consideration it’s always caused a problem eventually. Yes you may not see this right away because when you choose to stick with local time it’s usually based on the assumption you’re staying with inputs from a single timezone. Over time, as applications age however, things change. Data is accessed in other ways, possibly from different applications or shared with other customers around the world. And all of a sudden you have a problem that you never thought would happen.

It’s a generally accept good software practice to store Date and Time values in a consistent format and the easiest way to do this is to use UTC dates. The idea is simple: All data that is persisted to a permanent store is turned into UTC dates and written out that way. Any data retrieved is converted into a local date explicitly – only for display purposes. For things like queries input dates that are locale specific are converted into UTC dates first before the query is applied. IOW, if you use a common date format there will be conversion, but typically only when accessing/querying the data from the UI.

Most software systems provide easy support for date conversions. In .NET for example, there’s a DateTime.UtcNow value you can use to get the UTC time and there FromUniversalTime() and ToUniversalTime() and ToLocal

FoxPro and Date and Time

FoxPro doesn’t make this easy because it can only represent dates in local time – that is the time that is current for the computer that the machine is running on. However, it’s quite common in other environments such as .NET and Java to always write out date time values as UTC time. UTC time is Zero time, Greenwich (England) time, Zulu time – whatever you want to call it, it’s the time that doesn’t have an offset.

At the very least if you need to interact with systems that use UTC time you’ll need to make FoxPro play nice in this space. But I would urge you to consider to ALWAYS use UTC time for applications. While it is definitely a little more work to deal with UTC deformatting it’s not that much effort as long as you realize that the only time you care about this is when you convert dates to and from the User interface. Internally all date operations can relatively easily be made with native UTC.

Some UTC functions for FoxPro

If you are using any of our West Wind products – West Wind Web Connection, West Wind Client Tools or Internet Protocols – you already have this functionality I’m going to describe below. It’s built in with two functions (contained in wwAPI.prg):

GetUtcTime(ltTime)
Gets the current UTC time, or converts a FoxPro local DateTime to a UTC time.

FromUtcTime(ltTime)
Converts a date in UTC time format to local time.

Additionally there’s also:

GetTimeZone()

Returns the current timezone offset from UTC for the local machine. This is useful if you DIDN’T use UTC dates and are later forced to adjust dates based on local time and calculating time offsets based on user options or external locale access (ie. over a service). Essentially what this allows you to do is calculate relative offsets between two timezones and calculate a time for a different timezone. This function is also used by GetUtcTime() and FromUtcTime().

 

If you’re wondering about the inconsistent naming – the original function that existed in the framework for years was GetUtcTime which simply returned the current UTC time. Then at a later point I added the functionality to arbitrarily convert any DateTime value to a UTC data, so the function name stayed.

So using the two Utc conversion functions you can do the following:

? "Timezone: " + TRANSFORM(GetTimeZone()) +  " minutes"
ltTime = DATETIME()
? "Current Time: ", ltTime
 
ltUtc = GetUtcTime(ltTime)
? "UTC Time: ", ltUtc
 
ltTime = FromUtcTime(ltUtc)
? "Back to local: ", ltTime

I’m currently in the PDT (Pacific Daylight Time) zone and I get:

  • 06/09/2014 07:45:36 PM  - current
  • 06/10/2014 02:45:36 AM  - UTC
  • 06/09/2014 07:45:36 PM  - back to current
  • 420   -  timezone offset in minutes (-7 hours)

Note that GetTimeZone() will change if you change your system timezone, but VFP doesn’t see the change until you restart. The GetTimeZone() value also seems backwards: It’s +420 for Portland Oregon (PDT)  and –600 for Sydney Australia, but that’s how the Windows API is actually returning it. Essentially you can add the GetTimeZone() value to a local date to get a UTC date.

Implementation

This is all nice and neat if you have West Wind tools, but what about the rest of you that don’t? Ok, here’s some code that provides this same functionality (or pretty close to it actually):

*** Code exists also in wwAPI of any West Wind Tools!
*** SET PROCEDURE TO wwAPI Additive
#define Testing .t.
 
SET PROCEDURE TO TimeZone additive
 
#IF Testing
 
? "Timezone: " + TRANSFORM(GetTimeZone()) +  " minutes"
ltTime = DATETIME()
? "Current Time: ", ltTime
 
ltUtc = GetUtcTime(ltTime)
? "UTC Time: ", ltUtc
 
ltTime = FromUtcTime(ltUtc)
? "Back to local: ", ltTime
#ENDIF
 
 
************************************************************************
*  GetUtcTime
****************************************
***  Function: Returns UTC time from local time
***    Assume:
***      Pass:
***    Return:
************************************************************************
FUNCTION GetUtcTime(ltTime)
 
IF EMPTY(ltTime)
    ltTime = DATETIME()
ENDIF
 
*** Adjust the timezone offset
RETURN ltTime + (GetTimeZone() * 60)    
ENDFUNC
*   GetUtcTime
 
************************************************************************
*  FromUtcTime
****************************************
***  Function: Returns local time from UTC Time
***    Assume:
***      Pass:
***    Return:
************************************************************************
FUNCTION FromUtcTime(ltTime)
RETURN ltTime - (GetTimeZone() * 60)
ENDFUNC
*   FromUtcTime
 
************************************************************************
FUNCTION GetTimeZone
*********************************
***  Function: Returns the TimeZone offset from GMT including
***            daylight savings. Result is returned in minutes.
************************************************************************
PUBLIC __TimeZone
 
*** Cache the timezone so this is fast
IF VARTYPE(__TimeZone) = "N"
   RETURN __TimeZone
ENDIF
 
DECLARE integer GetTimeZoneInformation IN Win32API ;
   STRING @ TimeZoneStruct
   
lcTZ = SPACE(256)
 
lnDayLightSavings = GetTimeZoneInformation(@lcTZ)
lnOffset = CharToBin(SUBSTR(lcTZ,1,4),.T.)
 
*** Subtract an hour if daylight savings is active
IF lnDaylightSavings = 2
   lnOffset = lnOffset - 60
ENDIF
 
__TimeZone = lnOffset
    
RETURN lnOffSet
 
************************************************************************
FUNCTION CharToBin(lcBinString,llSigned)
****************************************
***  Function: Binary Numeric conversion routine. 
***            Converts DWORD or Unsigned Integer string
***            to Fox numeric integer value.
***      Pass: lcBinString -  String that contains the binary data 
***            llSigned    -  if .T. uses signed conversion
***                           otherwise value is unsigned (DWORD)
***    Return: Fox number
************************************************************************
LOCAL m.i, lnWord
 
lnWord = 0
FOR m.i = 1 TO LEN(lcBinString)
 lnWord = lnWord + (ASC(SUBSTR(lcBinString, m.i, 1)) * (2 ^ (8 * (m.i - 1))))
ENDFOR
 
IF llSigned AND lnWord > 0x80000000
  lnWord = lnWord - 1 - 0xFFFFFFFF
ENDIF
 
RETURN lnWord

The code is pretty self-explanatory. GetTimeZone() makes a call to a Windows API function to retrieve the Timezone structure and then needs to do some binary conversion to peel out the timezone offset. The timezone value is cached so only the first call actually makes the API call for efficiency.

Again if you are already using any West Wind tools you won’t want to use this code as it’s already included, but if you don’t then these functions are feature compatible with the West Wind Versions.

Working with UTC Dates

Using UTC dates in your application is pretty straight forward. Your user interface captures dates and times as local datetime values as it always has, but when you actually write the data to the database you convert the date to UTC dates before writing them.

Note that you do have to be somewhat careful to ensure dates are always normalized. FoxPro has no concept of a date kind – strongly typed languages like .NET and Java actually treat dates as structures that contain additional information that identify the date type. So if you tried to convert a date to UTC that is already a UTC kind it won’t convert again and hose that object. Some languages are even better about his: JavaScript stores all dates as UTC dates, and only the string functions that convert or print dates actually convert the date to local time (by default). Other overloads allow getting the raw UTC dates out. This is actually an ideal case – you get safe date values and the language itself drives the common use case that dates are used as local dates at the application UI level.

Unfortunately in FoxPro there no safeguards for this situation as dates are always local dates with no built in way to convert. So it’s up to you to make sure that you know which format a date is stored in.

When running queries against the data on disk with dates input by users or from other sources that are in local date format, you first convert the input dates to UTC dates, then run your queries with the adjusted date values:

*** some date that comes from the UI
ltUserDate1 = DATETIME()
ltUserDate2 = DATETIME() - 3600 * 24 * 30
 
ltTo = GetUtcDate(ltUserDate1)
ltFrom = GetUtcDate(ltUserDate2)
 
SELECT * FROM orders ;
  WHERE OrderDate >= ?ltFrom AND OrderDate <= ?ltTo
  into cursor Orders

Likewise if you write data to disk that was captured from user input you have to capture the local date and convert it. If you’re displaying value you have to convert them to local dates. If you’re using business objects, you can do this as part of the business object’s save operation which can automatically update dates as they are saved. Properties can have setters and getters that automatically convert dates to the right format.

Typically this will be a two step process – loading and saving.

For saving you might do:

*** Save Operation
loOrderBus = CREATEOBJECT("Order")
loOrderBus.New()
 
loOrder = loOrderBus.oData
 
IF EMPTY(loOrder.Entered)
    loOrder.Entered = GetUtcDate()       
ELSE    
    loOrder.Entered = GetUtcDate(loOrder.Entered)
ENDIF

and for reading you would do the opposite:

*** Load Operation
loOrderBus = CREATEOBJECT("Order")
loOrderBus.Load(lcOrderId)
 
loOrder = loOrderBus.oData
 
IF !EMPTY(loOrder.Entered)
    loOrder.Entered = FromUtcDate(loOrder.Entered)
ENDIF

If you’re using business objects like the above you can make this even more transparent by automatically doing these transformations right inside of the business object itself:

DEFINE CLASS busOrder as wwBusiness
 
FUNCTION Load(lcId)
 
IF (!base.Load(lcId)
  RETURN .F.
ENDIF
 
this.oData.Entered = FromUtcDate(this.oData.Entered)
 
RETURN .T.
ENDFUNC
 
FUNCTION Save()
this.oData.Entered = GetUtcDate(this.oData.Entered)
RETURN base.Save()
ENDFUNC
  
ENDDEFINE  

which makes the assumption that your user code deals with local timezones while the data saved is UTC.

Dated

Clearly all of this isn’t just totally transparent, even in languages that support UTC more easily there’s some effort involved to make this work. The main reason being that database – the storage mechanism in most cases doesn’t differentiate between dates either. FoxPro data doesn’t, neither does SQL Server. NoSQL solutions like Mongo do because they’re using JSON values which are ALWAYS UTC dates – you don’t get a choice (which in my opinion is the right way).

It’s not a totally transparent process, but it’s a good idea to do this nevertheless especially if you’re building applications that run on the Web or in other places where the applications are accessed from multiple locations – which is most applications these days. It’s worth the effort for peace of mind in the future and a good skill to learn as this is the norm for other platforms that are more date aware than FoxPro.

Posted in: FoxPro

Feedback for this Weblog Entry


re: UTC Time in FoxPro



Luis Guerra
Hi Rick, interesting your article on UTC time. I've seen you're using functions from your tools, but, what about those people that do not use your tools but they want to begin using UTC time?

I'd would like you to point me in the right direction to begin using UTC time.

Best Regards,

Luis GUerra

re: UTC Time in FoxPro


Luis - not sure what you mean. All the code you need is here...

re: UTC Time in FoxPro



Paul Gibson
Very good post Rick, totally agree. Even if you don't use datetimes from different geographical timezones it is still relevant if you deal with DST and the pertinence of this goes up every 6 months when systems transition from DST to non-DST. And so it is that I've landed on this page just a few days after the UK has reverted back to GMT from DST and I've hit a couple of little niggles with VFP's inability to natively resolve datetimes correctly.

You are spot on that this is par for the course and almost second nature when coding in .NET so it's frustrating that it is a little awkward in VFP.

I have an exe that was built just over a week ago (20/10/2015) while we were still in DST, it was built at 11:24 AM Local time which would have been 10:24 UTC. I'm ignoring the date-part for brevity because that's not changing due to the UK being so close to UTC and the file being built near midday. Up until Sunday morning VFP reported the time of the file correctly as 11:24 and you could confirm this by looking at the file in Windows Explorer. Since the clocks went back VFP now reports the file's (local) time as 10:24 so any comparisons might assume that the file was changed. Windows Explorer is still showing the time correctly as 11:24 but FDATE() and ADIR() in VFP cannot give me the correct local time now that it's running in the different TimeZone.

This is particular annoying for a routine I wrote which monitors changes in JPEGs. Once of the metrics which it stores to detect if the files have changed is the date modified - obtained via ADIR(). When the routine ran before Sunday it recorded all the Date modifies of all the pertinent files and that was fine but since the clocks changed the routine has scanned the (unchanged) files and the datetimes appear to have all changed when they haven't in reality, it's just VFPs (in)ability to read the correct datetime for files created in a different timezone which is causing a problem. My routine therefore assumed that all the JPEGs had changed since the previous time it scanned them and performed a task it thought was necessary when in fact it wasn't. It'll only hit this problem once every six months because it then records the new datetimes but it's not good for this routine to have this problem at all, let alone a reoccurring one.

The solution, as you say, is to store those datetimes using the UTC standard and potentially have a general rule that any datetime used for comparison purposes be stored in UTC. I'm working in a 16+ year-old application, so it's difficult to retrofit storing ALL datetimes in UTC across the whole system but for new facilities it should be possible.

One thing I thought was interesting in your implementation is that you cache the TimeZone offset for speed so you only need to query it once, does that make the implementation require a restart or a specific clearing of the cache when the computer system transitions between DST and non-DST? On my computer that function would return 0 or -60 depending on the DST-ness of the system but if it caches that value and uses the cache then the function risks returning the wrong value if it was cached before the transition and then called after the transition. I've seen this before on applications that run 24/7 and use the SECONDS() function due to the "caching" VFP does to facilitate SECONDS().

Paul

re: UTC Time in FoxPro


Hi Rick, The code examples are great.. many thanks... but do you know a way of determining the UTC of a particular country on a particular date... I am attempting to write code to calculate the the flight time from one location to another, on a given date, and passing back the correct local time of arrival... Any help would be much appreciated.
Vince

re: UTC Time in FoxPro



Jon
Almost any time I have a VFP question or problem, I need look no further than your blog--very helpful, as always. Thanks for continuing to be such a great resource for VFP developers.

re: UTC Time in FoxPro


I am working on a delivery routing module for my application and I decided to use javascript to pass the users browser time to vfp. I then convert the hex value to a fox datetime() easily. Javascript returns the time in hex starting from 1/1/1970 (aka "epoch time).

lnUnixEpochTime = 1500847437204
? lnUnixEpochTime/(60*60*60*60*60*60) + DATETIME()

This returns: 07/23/2017 05:16:29 PM

 



© Rick Strahl, West Wind Technologies, 2003 - 2018