Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with:

Storing And Retrieving Global Dates And Times In The Database

By Ben Nadel on
Tags: ColdFusion

Over the last week, I've been diving deeply into the concept of global times and Daylight Saving Time (DST), exploring the Java Timezone and Calendar classes and seeing how date/time values can be translated around the globe. All of this was set off by my desire to be able to run scheduled tasks based on a given user's local timezone. In order to do this, I would need to be able to determine a user's local time based on the server's local time and run the tasks only when appropriate. Now that I have a better understanding of how time zones work and how date/time values are translated across time zones, I thought I would try running through an INSERT / SELECT / UPDATE life cycle of a database record that has been normalized across time zones.

For this demo, I am going to be working with Wake-Up Calls. And to model the wake-up call, I have created a database table (name: wake_up_call) that has the following fields:

  • id
  • name
  • timezoneID
  • callHour
  • callMinute
  • nextUTCCallTime
  • nextTickCallTime

Of this table, the three critical fields are timezoneID, nextUTCCallTime, and nextTickCallTime. As seen in my previous posts, the timezoneID is a reference to the TimeZone instance that encapsulates all the rules about the given timezone's GMT offset and Daylight Saving Time (DST) logic. This ID will allow us to configure the appropriate calendar object used to navigate through date/time in the given timezone.

The other two fields, nextUTCCallTime and nextTickCallTime, are two different approaches to storing the date/time of the next appropriate wake-up call. I wasn't sure which one would be the easiest, so I wanted to try using both in the same demo.

The nextTickCallTime will store the Epoch offset at which the next wake-up call should be placed. This is the number of milliseconds that have elapsed since 1970 GMT. Since ColdFusion has a getTickCount() function and the Java calendar object has a getTimeInMillis() method, this value feels as if it might be the easiest to work with.

The nextUTCCallTime is basically the nextTickCallTime converted to a GMT date. These two fields should represent the same exact moment in time; however, the nextUTCCallTime will be easier to understand if you were look directly in the database.

There's a lot of code in the following demo, so let me outline the code from the top-down:

INSERT

  • Create a Pacific Standard Time (PST) calendar.
  • Set it to be 9:00 AM today (local to PST time).
  • Increment the date by one day (ie. tomorrow at 9AM).
  • Calculate the tick count and UTC date for the calendar.
  • Insert the record into the database.

SELECT

  • Get tomorrow's date (in local PST time).
  • From tomorrow's date, determine the tick count and UTC date.
  • Select the wake-up calls based on UTC.
  • Select the wake-up calls based on tick count.

UPDATE

  • Create a calendar object for the given wake-up call record (using TimeZone ID).
  • Increment the calendar object by one day.
  • Calculate the tick count and UTC date for the calendar.
  • Update the record in the database.

While there is a lot of code in the following demo, keep in mind that it is actually more complicated than it has to be because we are polling the database for a date in the future. This requires a few more mathematical hurdles. Had we only been concerned with the records needed "now", the getTickCount() and dateConvert() functions would have been much easier to use.

  • <!---
  • For this, imagine that we want to create a record in our
  • datebase that represents a wake-up call at a given time in
  • a given timezone. For the insert, we will need to create an
  • instance of the timezone that encapsulates all the rules for
  • GMT offsets and Daylight Saving Time (DST) applications.
  •  
  • For our demo, we will use Pacific Standard Time (PST).
  •  
  • NOTE: My server is in Eastern Standard Time (EST).
  • --->
  • <cfset pacificTimezone = createObject( "java", "java.util.TimeZone" )
  • .getTimeZone(
  • javaCast( "string", "US/Pacific" )
  • )
  • />
  •  
  • <!---
  • Now, let's create a calendar based on our PST timezone. This
  • will help us set the normalized UTC time in our datebase.
  • --->
  • <cfset pacificCalendar = createObject( "java", "java.util.GregorianCalendar" ).init(
  • pacificTimezone
  • ) />
  •  
  • <!---
  • For this this demo, we're going to be calling at 9AM in the
  • local (PST) time. As such, let's set the time in the calendar.
  • --->
  • <cfset today = createDateTime( 2011, 9, 26, 9, 0, 0 ) />
  •  
  • <!---
  • Set the properties - we don't have to worry about timezones at
  • this point since we just setting individual date parts. The
  • calendar, itself, will prevent us from setting any invalid date
  • based on DST rules.
  •  
  • NOTE: In this case, months start at zero (hence the -1).
  • --->
  • <cfset pacificCalendar.set(
  • javaCast( "int", year( today ) ),
  • javaCast( "int", (month( today ) - 1) ),
  • javaCast( "int", day( today ) ),
  • javaCast( "int", hour( today ) ),
  • javaCast( "int", minute( today ) ),
  • javaCast( "int", second( today ) )
  • ) />
  •  
  • <!---
  • Set the milliseconds to get a truly static calendar; otherwise,
  • the calendar will keep rolling the milliseconds according to
  • the system clock.
  • --->
  • <cfset pacificCalendar.set(
  • javaCast( "int", pacificCalendar.MILLISECOND ),
  • javaCast( "int", 0 )
  • ) />
  •  
  •  
  • <!--- ----------------------------------------------------- --->
  • <!--- ----------------------------------------------------- --->
  •  
  •  
  • <!---
  • Now that we've created the date for the wake-up call, we want
  • to set the first notice of it for tomorrow. As such, we want
  • to add one day to the calendar.
  • --->
  • <cfset pacificCalendar.add(
  • javaCast( "int", pacificCalendar.DAY_OF_MONTH ),
  • javaCast( "int", 1 )
  • ) />
  •  
  • <!---
  • Now, just in case we happened to create an hour that was not
  • valid in the given timezone (ex. 2:30 AM), overwrite the current
  • hour of the "next" (+1) day.
  • --->
  • <cfset pacificCalendar.set(
  • javaCast( "int", pacificCalendar.HOUR_OF_DAY ),
  • javaCast( "int", hour( today ) )
  • ) />
  •  
  •  
  • <!--- ----------------------------------------------------- --->
  • <!--- ----------------------------------------------------- --->
  •  
  •  
  • <!---
  • Now that we have our date set up in the calendar for the
  • Pacific Standard Time (PST) calendar, we want to store it in the
  • database. In this case, I'm going to use two different approaches
  • to storing the database:
  •  
  • - Tick Count (milliseconds since Epoch time)
  • - GMT Time
  •  
  • In reality, you would probably only need ONE of these methods;
  • but since I don't know this stuff very well yet, I'm going with
  • both of them.
  • --->
  •  
  • <!---
  • First, let's get the tick count, that's easy - it's built right
  • into the calendar that we are working with.
  • --->
  • <cfset pacificTickCount = pacificCalendar.getTimeInMillis() />
  •  
  • <!---
  • Getting the GMT time is a bit more complex. To do that, we are
  • going to calculate the local time (server local) based on the
  • tick cound and then convert that to GMT time.
  • --->
  • <cfset pacificUTC = dateConvert(
  • "local2utc",
  • dateAdd(
  • "s",
  • (pacificTickCount / 1000),
  • dateConvert( "utc2local", "1970/01/01" )
  • )
  • ) />
  •  
  • <!---
  • Now that we have BOTH the UTC date and EPOCH offset for the
  • wakeup call tomorrow, let's store it in the database.
  • --->
  • <cfquery name="insertCall" datasource="testing">
  • <!--- For THIS demo, clear out any existing records. --->
  • TRUNCATE TABLE wake_up_call;
  •  
  • <!---
  • Insert the new record. Notice that for this demo, we are
  • storing both the UTC time and the Epoch offset.
  • --->
  • INSERT INTO wake_up_call
  • (
  • name,
  • timezoneID,
  • callHour,
  • callMinute,
  • nextUTCCallTime,
  • nextTickCallTime
  • ) VALUES (
  • <cfqueryparam value="Timezone Call" cfsqltype="cf_sql_varchar" />,
  • <cfqueryparam value="#pacificTimezone.getID()#" cfsqltype="cf_sql_varchar" />,
  • <cfqueryparam value="#hour( today )#" cfsqltype="cf_sql_integer" />,
  • <cfqueryparam value="#minute( today )#" cfsqltype="cf_sql_integer" />,
  • <cfqueryparam value="#pacificUTC#" cfsqltype="cf_sql_timestamp" />,
  • <cfqueryparam value="#pacificTickCount#" cfsqltype="cf_sql_double" />
  • );
  • </cfquery>
  •  
  •  
  • <!--- ----------------------------------------------------- --->
  • <!--- ----------------------------------------------------- --->
  •  
  •  
  • <!---
  • Now that we have put the wakeup call in the database, let's try
  • to query for it. In order to do that, we are going to be using
  • two different methods: one for UTC time and one for Epoch offset
  • (tick count).
  •  
  • To start, let's get the local server date for our query. For
  • this DEMO, we need to add (24 + 3) hours to the TODAY date since
  • we need to make the difference between EST (my server) and PST
  • (the timezone we are working with).
  •  
  • NOTE: In production, we wouldn't have to do that since we
  • wouldn't be trying to trigger things AHEAD of schedule. This has
  • been greatly complicated by the goals of the demo.
  • --->
  • <cfset tomorrow = dateAdd( "h", (24 + 3), today ) />
  •  
  • <!--- Now, let's get the UTC time for the query. --->
  • <cfset tomorrowUTC = dateConvert( "local2utc", tomorrow ) />
  •  
  • <!--- Query for any wake up calls tomorrow. --->
  • <cfquery name="callByUTC" datasource="testing">
  • SELECT
  • c.id,
  • c.name,
  • c.timezoneID,
  • c.callHour,
  • c.callMinute,
  • c.nextUTCCallTime,
  • c.nextTickCallTime
  • FROM
  • wake_up_call c
  • WHERE
  • c.nextUTCCallTime = <cfqueryparam value="#tomorrowUTC#" cfsqltype="cf_sql_timestamp" />
  • </cfquery>
  •  
  •  
  • <!---
  • Now, let's calculate the TICK count for the tomorrow offset.
  • For that, we need to calculate the difference in milliseconds
  • from the local Epoch to tomorrow.
  • --->
  • <cfset tomorrowTickCount = (
  • dateDiff(
  • "s",
  • dateConvert( "utc2local", "1970/01/01" ),
  • tomorrow
  • ) *
  • 1000
  • ) />
  •  
  • <!--- Query for any wake up calls by tick count. --->
  • <cfquery name="callByTickCount" datasource="testing">
  • SELECT
  • c.id,
  • c.name,
  • c.timezoneID,
  • c.callHour,
  • c.callMinute,
  • c.nextUTCCallTime,
  • c.nextTickCallTime
  • FROM
  • wake_up_call c
  • WHERE
  • c.nextTickCallTime = <cfqueryparam value="#tomorrowTickCount#" cfsqltype="cf_sql_double" />
  • </cfquery>
  •  
  •  
  • <!---
  • To make sure we found the correct wake up calls, let's
  • output the data.
  • --->
  • <cfoutput>
  •  
  • Tomorrow UTC:
  • #dateFormat( tomorrowUTC, "mmm d, yyyy" )# at
  • #timeFormat( tomorrowUTC, "HH:mm:ss" )#
  •  
  • <br />
  • <br />
  •  
  • <cfdump
  • var="#callByUTC#"
  • label="Call by UTC"
  • />
  •  
  • <br />
  •  
  • <cfdump
  • var="#callByTickCount#"
  • label="Call by Tick Count"
  • />
  •  
  • </cfoutput>
  •  
  •  
  • <!--- ----------------------------------------------------- --->
  • <!--- ----------------------------------------------------- --->
  •  
  •  
  • <!---
  • Now that we have a wake up call record, let's pretend that we
  • JUST made the call and now need to increment the time by a day.
  • For that, we will need to create the appropriate calendar for
  • the given timezone.
  • --->
  • <cfset calendarTimezone = createObject( "java", "java.util.TimeZone" )
  • .getTimeZone(
  • javaCast( "string", callByUTC.timezoneID )
  • )
  • />
  •  
  • <!--- Get the calendar for the record timezone. --->
  • <cfset calendar = createObject( "java", "java.util.GregorianCalendar" ).init(
  • calendarTimezone
  • ) />
  •  
  • <!---
  • Set the date of the calendar based on the tick count of the
  • recorded wake up call.
  • --->
  • <cfset calendar.setTimeInMillis(
  • javaCast( "long", callByUTC.nextTickCallTime )
  • ) />
  •  
  • <!---
  • Increment the calendar by one day to find the next wake up call.
  • The calendar, itself, will handle any DST rules.
  • --->
  • <cfset calendar.add(
  • javaCast( "int", calendar.DAY_OF_MONTH ),
  • javaCast( "int", 1 )
  • ) />
  •  
  • <!---
  • Now, just in case we happened to create an hour that was not
  • valid in the given timezone (ex. 2:30 AM), overwrite the current
  • hour of the "next" (+1) day.
  • --->
  • <cfset calendar.set(
  • javaCast( "int", calendar.HOUR_OF_DAY ),
  • javaCast( "int", callByUTC.callHour )
  • ) />
  •  
  • <!---
  • Now that we have the date of the next wake up call, let's
  • calculate the tick count and the UTC time. Again, the tick
  • count is super easy.
  • --->
  • <cfset calendarTickCount = calendar.getTimeInMillis() />
  •  
  • <!---
  • Getting the GMT time is a bit more complex. To do that, we are
  • going to calculate the local time (server local) based on the
  • tick cound and then convert that to GMT time.
  • --->
  • <cfset calendarUTC = dateConvert(
  • "local2utc",
  • dateAdd(
  • "s",
  • (calendarTickCount / 1000),
  • dateConvert( "utc2local", "1970/01/01" )
  • )
  • ) />
  •  
  • <!--- Update the record for the next wake up call. --->
  • <cfquery name="updateCall" datasource="testing">
  • UPDATE
  • wake_up_call
  • SET
  • nextUTCCallTime = <cfqueryparam value="#calendarUTC#" cfsqltype="cf_sql_timestamp" />,
  • nextTickCallTime = <cfqueryparam value="#calendarTickCount#" cfsqltype="cf_sql_double" />
  • WHERE
  • id = <cfqueryparam value="#callByUTC.id#" cfsqltype="cf_sql_integer" />
  • ;
  • </cfquery>

When we run this code, we end up getting two queries with the same record - one selected using a UTC date and one selected using a tick count:


 
 
 

 
 Selecting database records using either UTC date or an Epoch offset. 
 
 
 

Despite the unrealistic SELECT (in the future), my initial feeling is that storing records based on tick count (ie. Epoch offset in GMT) is going to make for the most simple logic. The Java Calendar's getTimeInMillis() method makes converting any date to an Epoch offset insanely easy. And, if this were a scheduled task running every few minutes, ColdFusion's getTickCount() function would have made locating the appropriate records a pain-free query.




Reader Comments

@Ben:

Why are you jumping through so many hoops to convert a date from a user's local time into UTC?

Here's all you need:

  • today = createDateTime(2011, 9, 26, 9, 0, 0);
  • TimeZone = createObject("java","java.util.TimeZone").getTimeZone("US/Pacific");
  • offset = TimeZone.getOffset(javacast("long", dateDiff("s", createDateTime(1970, 1, 1, 0, 0, 0), today)*1000))/1000;
  • todayUTC = dateAdd("s", offset*-1, today);

All you need to do is grab the offset for the date selected and then add the offset to the date.

Reply to this Comment

@Dan,

To be completely honest, it's just easier for me to model something that is based on calendars. Right now, I'm at a point where I can understand the conversion of tick-count to and from calendar dates; but, even looking at your code, I'm finding that my mental model is just not strong enough to follow it.

Even when I look at the description for getOffset() in the Java Docs, I find that it is not helping. I think, for me, the concepts of DST and GMT offsets are not deeply engrained enough for certain things to feel natural.

Reply to this Comment

@Ben:

Strange, because I'm the exact opposite. :)

I'll break it down for you, so hopefully the code will be more clear.

1) Set a date in the user's local time.

  • today = createDateTime(2011, 9, 26, 9, 0, 0);

2) Get a reference to the user's time zone

  • TimeZone = createObject("java","java.util.TimeZone").getTimeZone("US/Pacific");

3) Get the offset for the user's date time. The getOffset() uses an Epoch time stamp, so we're converting the user's local time to milliseconds.) It also returns the offset in milliseconds, so we're converting back to seconds by dividing by 1000.

  • offset = TimeZone.getOffset(javacast("long", dateDiff("s", createDateTime(1970, 1, 1, 0, 0, 0), today)*1000))/1000;

4) Add the offset to the user's time to get the correct time in UTC

  • todayUTC = dateAdd("s", offset*-1, today);

Reply to this Comment

@Dan,

I think I'm really stumbling on #3. Is the offset returned the offset between the local time and the current GMT time (ie. for EST time, this would be -5 hours sometimes, -4 hours other times)?

Reply to this Comment

@Dan,

... I understand that I'm talking -5/-4 hours, but the offset is actually in "seconds". I'm just asking in a metric that I can overlay on a known problem (DST in the EST timezone).

Reply to this Comment

@Ben:

The getOffset() returns the total number of milliseconds a current time stamp is away from UTC.

So for the time stamp of "2011-09-26 09:00:00" (which we convert to Epoch) it will use the Java lookup tables to determine whether the current time is in DST, then add that to the base offset for the specified time zone and then return the total milliseconds of the offset.

So, if a time is in EST, you're going to get an offset of -300,000ms (i.e. -5 hours) and if the time is in EDT, you're going to get 240,000ms (i.e. -4 hours.)

In my code, I convert the milliseconds to seconds (which is the reason for the multiple/divisions by 1,000.)

So all my code basically does is go to Java and say "What's the offset for this current time stamp?" and then it applies the offset to the original date.

Reply to this Comment

@Dan,

Ok, I think I'm begging to get it. I had to read this about 4 times; but, I think it's making more sense. I think what was really confusing me was using the CreateDate() to create the 1970 date. I figured that wouldn't work since it would be creating the 1970 date in the server's TZ.... but I guess since it only needs the LONG date, as long as you keep the comparisons relative, it won't matter.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.