Working With Time Zones And Daylight Savings Time (DST) In ColdFusion And Java

All you need to store in the db is the difference between your server time in UTC and the time of the event you want to fire assuming your server time doesn't change. Calculating the difference would need your time zone info though.

Your SMS system just needs to look at the db and fire when ready.

I always set up servers to run in the GMT timezone and then within my code always store dates in GMT/UTC.

This does mean that you need to:

1. Convert all date/times a user inputs in your site into GMT/UTC.

2. Convert all date/times displaying to a user in their correct timezone.

It's definitely an area of CF that I think is seriously lacking. It really surprises me that there's really been no work with CFML to add easier support of timezones into the language.

Yes, there some functions, but you still end up having to drop down to Java classes to really work up a total solution.

Also, the benefit of storing times in UTC/GMT is that you never have to worry about correcting date/times in your database.

For example, if you're storing an offset for your servers timezone and then using that offset for calculations, what happens if you ever need to change the timezone on your server?

Just changing the offset would mean all archived dates are wrong. You'd have to update all the existing date/time values to match the new offset value.

Storing the dates in UTC makes sure that you're always using a common ground for date objects--regardless of the timezone the server's in.

Also, most major databases should have a way to default a date/time column in a table to a UTC date. For Microsoft SQL Server, it's getutcdate().

I did something like this in a system I wrote to send members of my house text and email notifications when it was their turn to do chores. I expanded it so my brother could use it at his house in California. For mine I just store simple timezone offset for each user and then have the notification process check this in the scheduled task. This of course ignores Arizona and parts of Indiana but since I'm not sure if anyone in those places will ever use it I'll just ignore it till it comes up.

I also used sms email gateways instead of an sms api so that I could have free text messages, though the formatting definitely suffers.

I've been away from ColdFusion for a while, but timezone.cfc was the cat's meow for working with time. I would persist all datetimes in UTC and then use timezone.cfc for the conversions for display etc.

http://www.sustainablegis.com/projects/tz/testTZCFC.cfm

@Ben:

If you want really ugly, the help desk application that I work in used to have the ability to show times in the customer times. This was all done via a very elaborate SQL query.

I ended up killing that feature because it was so slow and really not all that helpful (since people think of times in their timezone--not in other people's timezones.)

I forgot to mention, that was for reports--so if you generate a report that dumped out that last 10,000 tickets, there could have been many date/time fields on each row and each row might have been for a different timezone.

Some folks spend their entire careers tracking time and time conversion issues, especially at the US Naval Observatory, since modern navigation (in the ships-at-sea and missile guidance sense) is based on timekeeping:

http://tycho.usno.navy.mil/

You can spend HOURS wandering around in the Directorate of Time's website. Totally fascinating. The origin of Star Trek's "star date" is there. It's the trailing digits of the number of days since January 0, 4713 BC of the Julian proleptic calendar. Spend enough time at the Directorate of Time and you'll actually understand all of that jargon.

Can you tell I used to work for the Navy? And wrote time conversion routines? Well anyway, speaking as someone who actually KNOWS time quite well, thanks for all the research into java.util.TimeZone and for sharing it with us. That was all new to me, in that it came along after the bad old days of having to do it all yourself.

We are indeed living in happy times, when libraries are so capable.

@Dustin,

You'll need to swap out "parts of Indiana" and replace that with "Hawaii": we started observing DST in 2005, and to my knowledge, Hawaii has never observed it. My first job was with a company that had a significant call center ... but to complicate things, it was in Indiana, so rather than having to adjust things for what was, at the time, Arizona, Hawaii, and parts of Indiana, we had to adjust all existing appointments except in those areas. (This was in the early '90s, so there weren't a lot of options for the third-party software we were using.) So I learned those rules pretty quickly ...

Ben, slight correction: it's Daylight Saving Time, not Savings ...

I notice there seems to be a bug with CF's converting of numeric dates to actual dates on the day DST changes. (probably a new feature in 9.01)

It appears that the first time cf evaluates a numeric date on the day daylight savings starts (or ends?) it applies the DST offset to it.

i.e:

in NZ (dst starts on this date):

CreateODBCDate( 40811.5 ) + 0 = 40811.5416667

or:

dstStart = '2011-09-25 02:02';
writeDump(createODBcdatetime(dstStart));
--> {ts '2011-09-25 03:02:00'}

(note that it has correctly applied the dst offset to this time (there is no 2am on dst change day. 2am -> 3am).

dstStart += 0;

(adding 0 forces the date into the number format as above).

writeDump(createODBcdatetime(dstStart));
--> {ts '2011-09-25 04:02:00'}

and as you can see, it's applied the dst offset a second time.

...

this only happens on the day of the change. and it only applies the offset when the numeric date is past the change time (2am).

I was mostly using the ODBCDateTime() function as a quick way to render a date so I could see what was going on.

Here's another example of how it's broken:

// the day before the change
timeFormat(40810.5,"hh:mm");
-> 12:00

// the day of the change
timeFormat(40811.5,"hh:mm");
-> 01:00

// the day after the change
timeFormat(40812.5,"hh:mm");
-> 12:00

I have a feeling this is probably only an issue in CF9, maybe only in CF9.01. Appears to only be an issue if you use CF's numeric value for a date.

something like this would show up if anyone else has this issue:

for (i='1998-01-01 03:00'; i<'2012-01-01 03:00'; i++) {
if( hour(i) != 3) writeDump(createodbcdatetime(i));
}

for me the hour is incorrect on every dst start/end day since dst started (1974 in NZ it seems).

something to be wary of anyways if you like to pretend dates are numbers.

This is a headache. I have a situation where people different office hours at different times and days of the week in different timezones.
I'd like to be able to present that list ordered in real time relative to a visitor so it is easy to use. It seems like it should be easy but is damnably complicated for the reasons you out line above.

A function that returned the correct offset from UTC for a location and time of year would be sweet. Perhaps another to help a user select the correct time zone.

We have a transportation management system written in CF that has to take in to account the timezone of different locations by zip code, user preferences for displaying date times in a preferred timezone or in the local time of the location associated with the event that the time corresponds to, like a pickup time for instance. On top of that we also calculate timespans for things like a truck or air shipment that spans multiple timezones.

We store everything in UTC and run all of our servers (web and DB) in UTC.

I wish the whole world were one timezone and everybody used 24hr time. It would make life so much easier. I wouldn't mind waking up at 1100 and going to work at 1300.

I second Dan on storing dates and times as UTC in the DB. In my case if we have to convert to "local" time, it is always back to EST/EDT, so its not too crazy...yet.

The whole "Daylight Saving Time" thing is decidedly a hassle. Paul's timezone.cfc is great, but I have found that the function(s) that rely on or returned the UTC offset for a given timezone do NOT automatically account for DST. Not to mention that the government, at least here in the US, has changed the DST start and end times at least twice in the last ten years.

As a result of my own timezone based pain, I have learned the following: When doing any conversion between UTC and local, always be sure to do the following checks:

1. Does the local timezone in question actually observe DST? (As noted above, this gets weird for states like Arizona and Hawaii. Indiana used to be a huge mess, but I believe they finally adopted DST universally across the state so there aren't any local pockets of weirdness in the NW and SE anymore.)

2. Does DST apply to the date/time in question (i.e. is the time in question during DST?) It is an easy mistake to apply the wrong offset based on whether NOW() is in DST as opposed to whether DST applies for the date being converted.)

3. What is the ACTUAL correct UTC offset for the local timezone in question based on the first two questions? Example, the UTC offset for EST is +5, but for EDT it is +4.

Hopefully you can avoid the whole mess by storing and always displaying times in UTC. Unfortunately, that is a luxury for sure.

David

@David,

You are correct about Indiana; see my comment above. Rather than one official time zone and two unofficial ones (Eastern/no DST, Central/DST, Eastern/DST), we're down to two official time zones (Eastern/Central), just like other states split by the boundary.

Cases like that make me a big fan of making time zone offset a user setting: you tell me what time you're on and I'll track it that way. I don't want to figure out what time I think you observe (or more accurately, what time I think you want to see). Store as UTC, and then the only issue is history, as you mention: not all algorithms correctly apply DST by old rules.

We subscribe to services that give us updated US, Canada and Mexico postal codes. It includes lat/lon, time zone and some other useful tidbits. We ETL that into our database and match the timezones to our own time zone table. It gets complicated when you have "mountain time" for Arizona, the rest of the states, Canada and Mexico. One of them doesn't do DST and the others do, but change on different dates. Whenever we get or set a date time in the DB we always have to consider the location, the date time in question and user preferences to determine the offset to use to get to or from UTC. It is a real pain, but we have it pretty well figured out now.

The sun rises tomorrow at 1123Z in Belleville, MI.

Just something to be aware of if using a combination of now() and getDate()

If running SQL on a different machine and the time is not in sync on both machines, you can end up with a, what came first, the chicken or the egg scenario e.g. orders being dated processed a second or more before they are received.

In particular, this applies to my experience with NewTek (CrystalTech) and seems related to when they moved their SQL servers to the cloud. I never had an issue prior to that, except with GoDaddy when they forgot about the leap second a few years ago... I reminded them and got the usual inane replies and no thanks of course.

Anyway, my solution was to forget about getDate() and just use now()

In addition, my main interest in this article is because MySQL's time zone database is incorrect (an hour behind) for conversion of UTC to Australia/Sydney AEDT

Oops! The MySQL time zone tables were actually correct.

That said. MySQL has some rather cool date time functions...

The following will format and convert UTC to the correct Sydney date time, select orders for the previous 24 hours, and compensate for daylight saving...

SELECT
	DATE_FORMAT(CONVERT_TZ(ORDER.DATE_TIME, 'UTC', 'Australia/Sydney'), '%d/%m/%Y %h:%i:%s %p') AS orderDateTime
FROM
	ORDER
WHERE
	DATE_SUB(UTC_TIMESTAMP(), INTERVAL <cfqueryparam value="24" cfsqltype="cf_sql_integer" /> HOUR) < ORDER.DATE_TIME

#BreakingNews: Time Zone Database operators being sued.

http://yro.slashdot.org/story/11/10/06/1743226/civil-suit-filed-involving-the-time-zone-database

#MoreTzDbNews: ICANN has taken over the timezone database:

http://yro.slashdot.org/story/11/10/17/1555201/time-zone-database-has-new-home-after-lawsuit

http://abcnews.go.com/Technology/wireStory/time-zone-database-home-lawsuit-14748312

Geeks Are Sexy has a great video on Daylight Saving time and the great debate as to how useful it really is. Very well done.

http://www.geeksaresexy.net/2011/10/26/daylight-saving-time-for-dummies/

Enjoy.

@Ben,
I have dates that are entered correctly into my mysql the database but when I show them in a coldfusion flash cfform and cfgrid they are a day before that date!
I'm in a timezone one hour behind the now() time shown when I output it on my page, which I think must be my host's timezone.
Very odd - I may have to change the cfform to html as I imagine flash is creating problems- what can I do to sort this out?
Thanks!

@Brian,

I had a situation that was very much like what you describe above: we had people entering a date in a Flex form, but the date that was being stored was a day prior to the date they had entered. (The effect that caused the bug to be noticed was that the user was trying to set the date to tomorrow, so it looked like the change wasn't being saved.)

I found the email chain that described what I found: the hopefully relevant part is below. Note that in this case, it was registry settings on the Windows server that were causing the problem. The server was located in Eastern time; Windows knew it was on Eastern, but Java thought it was on Central, so it was adjusting the "time" back an hour, and because we weren't specifying time, that effectively adjusted the day backward one.

(In this instance, I was a contractor working with my client on a problem on their client's server, so we did not have direct access and could not do anything ourselves. What we were told was that they "installed Java" and the problem was resolved. What I assumed that meant was a) our client contact was either not very smart or was given bad information, and b) they updated Java on the server and it fixed the registry issue.)

----

A page that may explain the situation well is this: http://forums.sun.com/thread.jspa?threadID=525680&start=15&tstart=0. Basically, the problem is that Windows stores time zone information in a certain way, and Java (on which ColdFusion runs) looks for it in a slightly different way. Most of the time, it doesn't make any difference, but in certain situations, it does.

On the production server, the registry key HKEY_LOCAL_MACHINE/SYSTEM/Select shows ControlSet001 as "current", but ControlSet003 as LastKnownGood (this is slightly different than the scenario described in the link on Sun's forums above). Time zone information in set 001 is correct: Eastern Daylight during the summer, Eastern Standard during the winter. Set 003 is not correct: Central Daylight during the summer, Central Standard during the winter. It seems as though what is happening is that Java is looking to the LastKnownGood set to determine the time zone; this is Central, so it happily returns Central time, which would mean 11 PM when Windows thinks it's 12 midnight.

Now, in terms of solutions ... these are possibilities:
1. Try clearing the check box to automatically adjust the clock for daylight saving time, saving changes, then checking the box again and saving changes.
2. Try changing the time zone to Central Time, saving, then back to Eastern and saving changes.
(Those solutions are both based on the idea that something about the current time zone wasn't saved in a way that Java can recognize it; Windows can figure it out, but Java can't.)
It may also be worth checking to ensure that there are not any Java updates pending for that server.

@Dave,

Thanks for the input appreciated, yes well in my case it updates the right date in the database when I select a value from the calendar from the cfform and update the database. However, when I output this value from the database, even though the value is output correctly when I use cfdump, it is a day behind when I ouput it in the cfgrid and in the bind value in the cfinput field. It maybe something to do with the fact that both the cfgrid and cfform are flash so I'm going to change them both to html and see if that helps. Here's my offending cfinput in case that helps! Thanks

<cfinput type="DateField" name="NEXTSTEP" label="Action" width="100"
bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['NEXTSTEP']}"
onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'NEXTSTEP', NEXTSTEP.text);">

@Dave,
I just tried with changing both the cfgrid and cfform to format html.

The date which is 2012-07-05 in the database is now output correctly. If I put a mask on the cfgrid column it changes to a day before again-mask:

mask="yyyy/mm/dd" type="date"

Anyway I think again it is an issue with cfgrid and cfform FLASH format. I'll add this to the other issues with cfform flash format, one of the most annoying being that coldfusion becomes case sensitive. Anyway so I hope that helps someone else, basically I would suggest never use flash format is can be avoided

I stopped Flash forms a few years ago because they didn't work in Safari and from what I recall, there was a base href issue as well. In general, I avoid most really cool ColdFusion features because there are better alternatives... jQuery etc.

@Brian,

That definitely makes it seem like our issues were different. If you were seeing problems in both directions from the database, then it would be more likely that Java was contributing in some way.

I've not used Flash forms myself in CF, so I can't comment on those, but it does seem as though they cause more problems than HTML forms. Like Chris, where I work, we do avoid some CF-based features because there are alternatives we've found that work better. We're actually fortunate when it comes to case-sensitivity, though: because Java itself is case-sensitive, CF has behind-the-scenes code to manage the transition (which is also why some things seem like they shouldn't be case-sensitive, but actually are: those tend to be features that hit Java directly). So it could be worse; we could have to manage all of that ourselves. (My original background is Pascal and C, so I'm accustomed to more rigid constraints, but that's not how a lot of people start programming these days.)

If you continue to have issues with cfform and cfgrid, you might send Ben a question directly to see if he can cover it in an upcoming post. There isn't always an existing post that will cover your question, and I'm not sure how many people follow the older posts closely ...

@Dave,
Yeah well it was only outputting the value in the cfform (adding the value from the cfform to the database was fine)so it must be a cfform issue.

We are setting up new web servers. created three web servers by imaging drives from one server. Result should be identical functions but all three ColFusion 10 webs display different times even though they are set up the same and the windows OS clocks show the same time. Any ideas?