UTC Time in FoxPro

June 09, 2014 •

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.

Blog Stats

Rick's Sites

Archives

West Wind News