Storing And Retrieving Global Dates And Times In The Database
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:
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.
Want to use code from this post? Check out the license.
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:
All you need to do is grab the offset for the date selected and then add the offset to the date.
@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.
@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.
2) Get a reference to the user's time zone
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.
4) Add the offset to the user's time to get the correct time in UTC
@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)?
@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).
@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.
@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.