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 Rocks (SOTR) 2011 (Edinburgh) with:

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

By Ben Nadel on
Tags: ColdFusion

I'm working on a personal project that was going to have some Twilio-powered mobile integration. This feature depended on being able to send an SMS text message to a user at a given time each day. While this might sound simple, it is actually quite a complex matter. See, my server's 9PM is probably not your 9PM. As such, I need to calculate your time based on my server's time and our individual time zones. Throw Daylight Savings Time (DST) into the mix and suddenly, this seemingly simple concept becomes an almost unbearable problem.

Before I get into this, I just want to give special thanks to Paul Hastings. His work with globalization and his understanding of time zones really helped me through these dark times. I really appreciate all the time he took exchanging ideas with me on Facebook!

The primary stumbling block with time zone translation is the fact that Daylight Savings Time (DST) is completely arbitrary. Some countries use it, some don't. Some states use it, some don't. Some counties use it, some don't. When I was growing up, my world consisted of three time zones:

  • Eastern
  • Central
  • Pacific

Heck, I didn't even know there was a "Mountain" time zone until I was older. While these are clearly the time zones for the United States, even with the addition of Mountain time, this set is not nearly specific enough. As it turns out, many places within this country have different DST laws. And, to complicate matters, those laws have changed over time.

As I was digging into timezone logic, I kept trying to keep my code as simple as possible. I kept thinking it doesn't have to be so complicated. But, this mindset was getting me nowhere (3 days of nowhere). This is complicated stuff. Luckily, the JRE (Java Runtime Environment) that powers ColdFusion has the latest version of the Olson Timezone Database. This database provides encapsulated logic for each timezone such that we don't have to kill ourselves trying to manage all the rules and applications.

This timezone database can be accessed using the java.util.TimeZone class. Each timezone is referenced by an ID that is specific to a location, timezone, and daylight savings time (DST) combination. To see the enormous number of timezones, take a look at this code:

  • <!---
  • The first thing we want to do is get a list of all the timezone
  • IDs available in the Java library. So, let's get the library.
  • --->
  • <cfset timezoneClass = createObject( "java", "java.util.TimeZone" ) />
  •  
  • <!---
  • Now, get the IDs. Each ID is a simple string that represents a
  • specific location, timezone, and daylight savings time
  • combination. There are an insane number of these combinations!
  • --->
  • <cfset timezoneIDs = timezoneClass.getAvailableIDs() />
  •  
  • <!--- Let's output the IDs. --->
  • <cfoutput>
  •  
  • <cfloop
  • index="timezoneID"
  • array="#timezoneIDs#">
  •  
  • #timezoneID#<br />
  •  
  • </cfloop>
  •  
  • </cfoutput>

Here, we are simply getting a list of all the known time zones in the database and then iterating over their IDs. When we run the above code, we get the following output:

Etc/GMT+12
Etc/GMT+11
MIT
Pacific/Apia
Pacific/Midway
Pacific/Niue
Pacific/Pago_Pago
Pacific/Samoa
US/Samoa
America/Adak
America/Atka
Etc/GMT+10
HST
Pacific/Fakaofo
Pacific/Honolulu
Pacific/Johnston
Pacific/Rarotonga
Pacific/Tahiti
SystemV/HST10
US/Aleutian
US/Hawaii
Pacific/Marquesas
AST
America/Anchorage
America/Juneau
America/Nome
America/Sitka
America/Yakutat
Etc/GMT+9
Pacific/Gambier
SystemV/YST9
SystemV/YST9YDT
US/Alaska
America/Dawson
America/Ensenada
America/Los_Angeles
America/Metlakatla
America/Santa_Isabel
America/Tijuana
America/Vancouver
America/Whitehorse
Canada/Pacific
Canada/Yukon
Etc/GMT+8
Mexico/BajaNorte
PST
PST8PDT
Pacific/Pitcairn
SystemV/PST8
SystemV/PST8PDT
US/Pacific
US/Pacific-New
America/Boise
America/Cambridge_Bay
America/Chihuahua
America/Dawson_Creek
America/Denver
America/Edmonton
America/Hermosillo
America/Inuvik
America/Mazatlan
America/Ojinaga
America/Phoenix
America/Shiprock
America/Yellowknife
Canada/Mountain
Etc/GMT+7
MST
MST7MDT
Mexico/BajaSur
Navajo
PNT
SystemV/MST7
SystemV/MST7MDT
US/Arizona
US/Mountain
America/Bahia_Banderas
America/Belize
America/Cancun
America/Chicago
America/Costa_Rica
America/El_Salvador
America/Guatemala
America/Indiana/Knox
America/Indiana/Tell_City
America/Knox_IN
America/Managua
America/Matamoros
America/Menominee
America/Merida
America/Mexico_City
America/Monterrey
America/North_Dakota/Beulah
America/North_Dakota/Center
America/North_Dakota/New_Salem
America/Rainy_River
America/Rankin_Inlet
America/Regina
America/Swift_Current
America/Tegucigalpa
America/Winnipeg
CST
CST6CDT
Canada/Central
Canada/East-Saskatchewan
Canada/Saskatchewan
Chile/EasterIsland
Etc/GMT+6
Mexico/General
Pacific/Easter
Pacific/Galapagos
SystemV/CST6
SystemV/CST6CDT
US/Central
US/Indiana-Starke
America/Atikokan
America/Bogota
America/Cayman
America/Coral_Harbour
America/Detroit
America/Fort_Wayne
America/Grand_Turk
America/Guayaquil
America/Havana
America/Indiana/Indianapolis
America/Indiana/Marengo
America/Indiana/Petersburg
America/Indiana/Vevay
America/Indiana/Vincennes
America/Indiana/Winamac
America/Indianapolis
America/Iqaluit
America/Jamaica
America/Kentucky/Louisville
America/Kentucky/Monticello
America/Lima
America/Louisville
America/Montreal
America/Nassau
America/New_York
America/Nipigon
America/Panama
America/Pangnirtung
America/Port-au-Prince
America/Resolute
America/Thunder_Bay
America/Toronto
Canada/Eastern
Cuba
EST
EST5EDT
Etc/GMT+5
IET
Jamaica
SystemV/EST5
SystemV/EST5EDT
US/East-Indiana
US/Eastern
US/Michigan
America/Caracas
America/Anguilla
America/Antigua
America/Argentina/San_Luis
America/Aruba
America/Asuncion
America/Barbados
America/Blanc-Sablon
America/Boa_Vista
America/Campo_Grande
America/Cuiaba
America/Curacao
America/Dominica
America/Eirunepe
America/Glace_Bay
America/Goose_Bay
America/Grenada
America/Guadeloupe
America/Guyana
America/Halifax
America/La_Paz
America/Manaus
America/Marigot
America/Martinique
America/Moncton
America/Montserrat
America/Port_of_Spain
America/Porto_Acre
America/Porto_Velho
America/Puerto_Rico
America/Rio_Branco
America/Santiago
America/Santo_Domingo
America/St_Barthelemy
America/St_Kitts
America/St_Lucia
America/St_Thomas
America/St_Vincent
America/Thule
America/Tortola
America/Virgin
Antarctica/Palmer
Atlantic/Bermuda
Atlantic/Stanley
Brazil/Acre
Brazil/West
Canada/Atlantic
Chile/Continental
Etc/GMT+4
PRT
SystemV/AST4
SystemV/AST4ADT
America/St_Johns
CNT
Canada/Newfoundland
AGT
America/Araguaina
America/Argentina/Buenos_Aires
America/Argentina/Catamarca
America/Argentina/ComodRivadavia
America/Argentina/Cordoba
America/Argentina/Jujuy
America/Argentina/La_Rioja
America/Argentina/Mendoza
America/Argentina/Rio_Gallegos
America/Argentina/Salta
America/Argentina/San_Juan
America/Argentina/Tucuman
America/Argentina/Ushuaia
America/Bahia
America/Belem
America/Buenos_Aires
America/Catamarca
America/Cayenne
America/Cordoba
America/Fortaleza
America/Godthab
America/Jujuy
America/Maceio
America/Mendoza
America/Miquelon
America/Montevideo
America/Paramaribo
America/Recife
America/Rosario
America/Santarem
America/Sao_Paulo
Antarctica/Rothera
BET
Brazil/East
Etc/GMT+3
America/Noronha
Atlantic/South_Georgia
Brazil/DeNoronha
Etc/GMT+2
America/Scoresbysund
Atlantic/Azores
Atlantic/Cape_Verde
Etc/GMT+1
Africa/Abidjan
Africa/Accra
Africa/Bamako
Africa/Banjul
Africa/Bissau
Africa/Casablanca
Africa/Conakry
Africa/Dakar
Africa/El_Aaiun
Africa/Freetown
Africa/Lome
Africa/Monrovia
Africa/Nouakchott
Africa/Ouagadougou
Africa/Sao_Tome
Africa/Timbuktu
America/Danmarkshavn
Atlantic/Canary
Atlantic/Faeroe
Atlantic/Faroe
Atlantic/Madeira
Atlantic/Reykjavik
Atlantic/St_Helena
Eire
Etc/GMT
Etc/GMT+0
Etc/GMT-0
Etc/GMT0
Etc/Greenwich
Etc/UCT
Etc/UTC
Etc/Universal
Etc/Zulu
Europe/Belfast
Europe/Dublin
Europe/Guernsey
Europe/Isle_of_Man
Europe/Jersey
Europe/Lisbon
Europe/London
GB
GB-Eire
GMT
GMT0
Greenwich
Iceland
Portugal
UCT
UTC
Universal
WET
Zulu
Africa/Algiers
Africa/Bangui
Africa/Brazzaville
Africa/Ceuta
Africa/Douala
Africa/Kinshasa
Africa/Lagos
Africa/Libreville
Africa/Luanda
Africa/Malabo
Africa/Ndjamena
Africa/Niamey
Africa/Porto-Novo
Africa/Tunis
Africa/Windhoek
Arctic/Longyearbyen
Atlantic/Jan_Mayen
CET
ECT
Etc/GMT-1
Europe/Amsterdam
Europe/Andorra
Europe/Belgrade
Europe/Berlin
Europe/Bratislava
Europe/Brussels
Europe/Budapest
Europe/Copenhagen
Europe/Gibraltar
Europe/Ljubljana
Europe/Luxembourg
Europe/Madrid
Europe/Malta
Europe/Monaco
Europe/Oslo
Europe/Paris
Europe/Podgorica
Europe/Prague
Europe/Rome
Europe/San_Marino
Europe/Sarajevo
Europe/Skopje
Europe/Stockholm
Europe/Tirane
Europe/Vaduz
Europe/Vatican
Europe/Vienna
Europe/Warsaw
Europe/Zagreb
Europe/Zurich
MET
Poland
ART
Africa/Blantyre
Africa/Bujumbura
Africa/Cairo
Africa/Gaborone
Africa/Harare
Africa/Johannesburg
Africa/Kigali
Africa/Lubumbashi
Africa/Lusaka
Africa/Maputo
Africa/Maseru
Africa/Mbabane
Africa/Tripoli
Asia/Amman
Asia/Beirut
Asia/Damascus
Asia/Gaza
Asia/Istanbul
Asia/Jerusalem
Asia/Nicosia
Asia/Tel_Aviv
CAT
EET
Egypt
Etc/GMT-2
Europe/Athens
Europe/Bucharest
Europe/Chisinau
Europe/Helsinki
Europe/Istanbul
Europe/Kaliningrad
Europe/Kiev
Europe/Mariehamn
Europe/Minsk
Europe/Nicosia
Europe/Riga
Europe/Simferopol
Europe/Sofia
Europe/Tallinn
Europe/Tiraspol
Europe/Uzhgorod
Europe/Vilnius
Europe/Zaporozhye
Israel
Libya
Turkey
Africa/Addis_Ababa
Africa/Asmara
Africa/Asmera
Africa/Dar_es_Salaam
Africa/Djibouti
Africa/Kampala
Africa/Khartoum
Africa/Mogadishu
Africa/Nairobi
Antarctica/Syowa
Asia/Aden
Asia/Baghdad
Asia/Bahrain
Asia/Kuwait
Asia/Qatar
Asia/Riyadh
EAT
Etc/GMT-3
Europe/Moscow
Europe/Samara
Europe/Volgograd
Indian/Antananarivo
Indian/Comoro
Indian/Mayotte
W-SU
Asia/Riyadh87
Asia/Riyadh88
Asia/Riyadh89
Mideast/Riyadh87
Mideast/Riyadh88
Mideast/Riyadh89
Asia/Tehran
Iran
Asia/Baku
Asia/Dubai
Asia/Muscat
Asia/Tbilisi
Asia/Yerevan
Etc/GMT-4
Indian/Mahe
Indian/Mauritius
Indian/Reunion
NET
Asia/Kabul
Antarctica/Mawson
Asia/Aqtau
Asia/Aqtobe
Asia/Ashgabat
Asia/Ashkhabad
Asia/Dushanbe
Asia/Karachi
Asia/Oral
Asia/Samarkand
Asia/Tashkent
Asia/Yekaterinburg
Etc/GMT-5
Indian/Kerguelen
Indian/Maldives
PLT
Asia/Calcutta
Asia/Colombo
Asia/Kolkata
IST
Asia/Kathmandu
Asia/Katmandu
Antarctica/Vostok
Asia/Almaty
Asia/Bishkek
Asia/Dacca
Asia/Dhaka
Asia/Novokuznetsk
Asia/Novosibirsk
Asia/Omsk
Asia/Qyzylorda
Asia/Thimbu
Asia/Thimphu
BST
Etc/GMT-6
Indian/Chagos
Asia/Rangoon
Indian/Cocos
Antarctica/Davis
Asia/Bangkok
Asia/Ho_Chi_Minh
Asia/Hovd
Asia/Jakarta
Asia/Krasnoyarsk
Asia/Phnom_Penh
Asia/Pontianak
Asia/Saigon
Asia/Vientiane
Etc/GMT-7
Indian/Christmas
VST
Antarctica/Casey
Asia/Brunei
Asia/Choibalsan
Asia/Chongqing
Asia/Chungking
Asia/Harbin
Asia/Hong_Kong
Asia/Irkutsk
Asia/Kashgar
Asia/Kuala_Lumpur
Asia/Kuching
Asia/Macao
Asia/Macau
Asia/Makassar
Asia/Manila
Asia/Shanghai
Asia/Singapore
Asia/Taipei
Asia/Ujung_Pandang
Asia/Ulaanbaatar
Asia/Ulan_Bator
Asia/Urumqi
Australia/Perth
Australia/West
CTT
Etc/GMT-8
Hongkong
PRC
Singapore
Australia/Eucla
Asia/Dili
Asia/Jayapura
Asia/Pyongyang
Asia/Seoul
Asia/Tokyo
Asia/Yakutsk
Etc/GMT-9
JST
Japan
Pacific/Palau
ROK
ACT
Australia/Adelaide
Australia/Broken_Hill
Australia/Darwin
Australia/North
Australia/South
Australia/Yancowinna
AET
Antarctica/DumontDUrville
Asia/Sakhalin
Asia/Vladivostok
Australia/ACT
Australia/Brisbane
Australia/Canberra
Australia/Currie
Australia/Hobart
Australia/Lindeman
Australia/Melbourne
Australia/NSW
Australia/Queensland
Australia/Sydney
Australia/Tasmania
Australia/Victoria
Etc/GMT-10
Pacific/Chuuk
Pacific/Guam
Pacific/Port_Moresby
Pacific/Saipan
Pacific/Truk
Pacific/Yap
Australia/LHI
Australia/Lord_Howe
Antarctica/Macquarie
Asia/Anadyr
Asia/Kamchatka
Asia/Magadan
Etc/GMT-11
Pacific/Efate
Pacific/Guadalcanal
Pacific/Kosrae
Pacific/Noumea
Pacific/Pohnpei
Pacific/Ponape
SST
Pacific/Norfolk
Antarctica/McMurdo
Antarctica/South_Pole
Etc/GMT-12
Kwajalein
NST
NZ
Pacific/Auckland
Pacific/Fiji
Pacific/Funafuti
Pacific/Kwajalein
Pacific/Majuro
Pacific/Nauru
Pacific/Tarawa
Pacific/Wake
Pacific/Wallis
NZ-CHAT
Pacific/Chatham
Etc/GMT-13
Pacific/Enderbury
Pacific/Tongatapu
Etc/GMT-14
Pacific/Kiritimati

Ha ha, and you thought there were gonna be 24 time zones! Beginners! They so stupid!

If I look at this list and try to translate it back into the list I understood as a child, here is what I come up with:

  • US/Eastern
  • US/Central
  • US/Mountain
  • US/Pacific

In the massive timezone list, you might notice that we have several IDs beginning with "US/". Yeah, even the United States is not nearly that simple. For example, we have:

  • US/Arizona

This is because, while Arizona falls within the "Mountain" timezone location, it doesn't adhere to the standard Daylight Savings Time (DST) rules. Now, not only does this make relative time offsets more complicated, with ColdFusion alone, it makes it impossible. To see what I'm talking about, imagine that I was trying to create a date/time object in the midst of DST for Arizona:

  • <!--- Create 2:30 AM on Mar 13, 2011. --->
  • <cfset arizonaMorning = createDateTime( 2011, 3, 13, 2, 30, 0 ) />

Instead of returning a proper date/time object, ColdFusion throws the following error:

Date value passed to date function CreateDateTime is unspecified or invalid. Specify a valid date in CreateDateTime function.

The problem here is that while this date is valid for Arizona, it is not valid for my Server which is running on a machine that is using the Eastern Standard Time (EST) timezone. As such, the ColdFusion layer of my application stack does not have the capabilities of modeling that date/time properly.

Luckily, we can dip back down into the Java layer and use the java.util.TimeZone and the java.util.GregorianCalendar classes to make up the difference. The TimeZone class encapsulates the logic surrounding the GMT (Greenwich Mean Time) and DST (Daylight Savings Time) rules while the Calendar class allows us to navigate through dates and times in the given timezone.

To see this in action, let's create a calendar for both the US/Mountain and the US/Arizona time zones. Then, we can use the individual calendars to navigate over the the start of daylight savings time (DST) and see how those changes manifest themselves over the three days.

  • <!---
  • A lot of our interaction comes through the Timezone class. As
  • such, let's create a utility instance of it.
  • --->
  • <cfset timezoneClass = createObject( "java", "java.util.TimeZone" ) />
  •  
  • <!---
  • For this demo we are gonna look at two time zones that are
  • related, but do not honor the same daylight savings time. While
  • "Mountain Time" is GMT-7, Arizona does not honor daylight savings
  • time; so, for part of the year, Mountain time is GMT-6 while
  • Arizona is always GMT-7.
  •  
  • To handle this, Arizona has been given its own timezone ID.
  • --->
  • <cfset mountainTimezoneID = "US/Mountain" />
  •  
  • <!--- Arizona's own timezone. --->
  • <cfset arizonaTimezoneID = "US/Arizona" />
  •  
  •  
  • <!--- ----------------------------------------------------- --->
  • <!--- ----------------------------------------------------- --->
  •  
  •  
  • <!---
  • Now, let's get the actual timezone instances that are responsible
  • for knowing and powering the rules of date-change in the given
  • timezone. Each of these can be used with a calendar to drive a
  • different set of rules and behaviors.
  • --->
  • <cfset mountainTimezone = timezoneClass.getTimeZone(
  • javaCast( "string", mountainTimezoneID )
  • ) />
  •  
  • <!--- Get Arizona's timezone instance. --->
  • <cfset arizonaTimezone = timezoneClass.getTimeZone(
  • javaCast( "string", arizonaTimezoneID )
  • ) />
  •  
  •  
  • <!--- ----------------------------------------------------- --->
  • <!--- ----------------------------------------------------- --->
  •  
  •  
  • <!---
  • Now that we have the timezone instances, we are going to
  • create a Calendar instance using each timezone. This calendar
  • will allow us to navigate the date/time properties of the
  • given timezone without running in to any errors that result
  • from "invalid" dates.
  • --->
  • <cfset mountainCalendar = createObject( "java", "java.util.GregorianCalendar" ).init(
  • mountainTimezone
  • ) />
  •  
  • <!--- Get Arizona's calendar. --->
  • <cfset arizonaCalendar = createObject( "java", "java.util.GregorianCalendar" ).init(
  • arizonaTimezone
  • ) />
  •  
  •  
  • <!--- ----------------------------------------------------- --->
  • <!--- ----------------------------------------------------- --->
  •  
  •  
  • <!---
  • Now that we have the calendar instances, we want to test them
  • to see the rules of date-based augmentation. To do this, we
  • will start on a day BEFORE daylight savings time and then
  • increment a day at a time to see how the hours change from
  • calendar to calendar.
  •  
  • In 2011, daylight savings time started on March 13. As such,
  • we'll start our dates on March 12 at 2:30 AM and go to March 14.
  • --->
  • <cfset beforeDST = createDateTime( 2011, 3, 12, 2, 30, 0 ) />
  •  
  • <!---
  • Set the mountain time. This takes:
  • - Year
  • - Month (NOTE: N - 1 for Java)
  • - Day
  • - Hour
  • - Minute
  • - Second
  •  
  • NOTE: In the case of Java, months start at zero, not 1
  • (like ColdFusion).
  • --->
  • <cfset mountainCalendar.set(
  • javaCast( "int", year( beforeDST ) ),
  • javaCast( "int", (month( beforeDST ) - 1) ),
  • javaCast( "int", day( beforeDST ) ),
  • javaCast( "int", hour( beforeDST ) ),
  • javaCast( "int", minute( beforeDST ) ),
  • javaCast( "int", second( beforeDST ) )
  • ) />
  •  
  • <!---
  • Set the milliseonds. If we don't do this, the milliseconds will
  • continue to roll forward based on the system's clock.
  • --->
  • <cfset mountainCalendar.set(
  • javaCast( "int", mountainCalendar.MILLISECOND ),
  • javaCast( "int", 0 )
  • ) />
  •  
  •  
  • <!--- Set the same start date for Arizona. --->
  • <cfset arizonaCalendar.set(
  • javaCast( "int", year( beforeDST ) ),
  • javaCast( "int", (month( beforeDST ) - 1) ),
  • javaCast( "int", day( beforeDST ) ),
  • javaCast( "int", hour( beforeDST ) ),
  • javaCast( "int", minute( beforeDST ) ),
  • javaCast( "int", second( beforeDST ) )
  • ) />
  •  
  • <!---
  • Set the milliseonds. If we don't do this, the milliseconds will
  • continue to roll forward based on the system's clock.
  • --->
  • <cfset arizonaCalendar.set(
  • javaCast( "int", arizonaCalendar.MILLISECOND ),
  • javaCast( "int", 0 )
  • ) />
  •  
  •  
  • <!--- Now, let's do some testing. --->
  • <cfoutput>
  •  
  •  
  • <strong>Mountain Time</strong><br />
  •  
  • <!--- Show the time for the three days surrounding DST. --->
  • <cfloop
  • index="dayOffset"
  • from="0"
  • to="2"
  • step="1">
  •  
  • <!--- Increment the calendar. --->
  • <cfset mountainCalendar.add(
  • javaCast( "int", mountainCalendar.DAY_OF_MONTH ),
  • javaCast( "int", dayOffset )
  • ) />
  •  
  • <!--- Output the time for the current calendar setting. --->
  • Time:
  • #mountainCalendar.get( mountainCalendar.HOUR )# :
  • #mountainCalendar.get( mountainCalendar.MINUTE )#
  •  
  • <!--- Also, output the Epoch offset. --->
  • (
  • Epoch:
  • #mountainCalendar.getTimeInMillis()#
  • )
  •  
  • <br />
  •  
  • </cfloop>
  •  
  •  
  • <!--- For a final comparison, set the HOURS to 3AM. --->
  • <cfset mountainCalendar.set(
  • javaCast( "int", mountainCalendar.HOUR ),
  • javaCast( "int", 3 )
  • ) />
  •  
  • <!--- Output the time for the current calendar setting. --->
  • Time:
  • #mountainCalendar.get( mountainCalendar.HOUR )# :
  • #mountainCalendar.get( mountainCalendar.MINUTE )#
  •  
  • <!--- Also, output the Epoch offset. --->
  • (
  • Epoch:
  • #mountainCalendar.getTimeInMillis()#
  • )
  • <br />
  •  
  •  
  • <!--- ------------------------------------------------- --->
  • <br />
  • <!--- ------------------------------------------------- --->
  •  
  •  
  • <strong>Arizona[Mountain] Time</strong><br />
  •  
  • <!--- Show the time for the three days surrounding DST. --->
  • <cfloop
  • index="dayOffset"
  • from="0"
  • to="2"
  • step="1">
  •  
  • <!--- Increment the calendar. --->
  • <cfset arizonaCalendar.add(
  • javaCast( "int", arizonaCalendar.DAY_OF_MONTH ),
  • javaCast( "int", dayOffset )
  • ) />
  •  
  • <!--- Output the time for the current calendar setting. --->
  • Time:
  • #arizonaCalendar.get( arizonaCalendar.HOUR )# :
  • #arizonaCalendar.get( arizonaCalendar.MINUTE )#
  •  
  • <!--- Also, output the Epoch offset. --->
  • (
  • Epoch:
  • #arizonaCalendar.getTimeInMillis()#
  • )
  • <br />
  •  
  • </cfloop>
  •  
  •  
  • <!--- For a final comparison, set the HOURS to 3AM. --->
  • <cfset arizonaCalendar.set(
  • javaCast( "int", mountainCalendar.HOUR ),
  • javaCast( "int", 3 )
  • ) />
  •  
  • <!--- Output the time for the current calendar setting. --->
  • Time:
  • #arizonaCalendar.get( arizonaCalendar.HOUR )# :
  • #arizonaCalendar.get( arizonaCalendar.MINUTE )#
  •  
  • <!--- Also, output the Epoch offset. --->
  • (
  • Epoch:
  • #arizonaCalendar.getTimeInMillis()#
  • )
  • <br />
  •  
  •  
  • </cfoutput>

As you can see, we are starting on Mar 12, 2011 at 2:30 AM. Then, we increment each calendar a day at a time to see how Mar 13, 2011 - 2:30 AM (invalid in Mountain time) will work. Furthermore, we are outputting the Epoch time for each date increment.

NOTE: The Calendar class works off the System time. As such, we have to set all the date/time fields of the calendar, including Milliseconds, in order to get a completely static calendar.

When we run the above code, we get the following output:

Mountain Time
Time: 2 : 30 ( Epoch: 1299922200000 )
Time: 1 : 30 ( Epoch: 1300005000000 )
Time: 1 : 30 ( Epoch: 1300174200000 )
Time: 3 : 30 ( Epoch: 1300181400000 )

Arizona[Mountain] Time
Time: 2 : 30 ( Epoch: 1299922200000 )
Time: 2 : 30 ( Epoch: 1300008600000 )
Time: 2 : 30 ( Epoch: 1300181400000 )
Time: 3 : 30 ( Epoch: 1300185000000 )

As you can see, Arizona time, which doesn't use daylight savings time (DST) was able to create the 2:30AM time on Mar 13th. The Mountain timezone calendar, on the other hand, implicitly went to 1:30AM on Mar 13th. Unlike ColdFusion, it didn't throw an invalid date/time error - it simply adjusted based on its encapsulated rules and best practices.

For the final part of the test of each calendar, I set the explicit time to be 3:30 AM on Mar 14th (the day after daylight savings time has started). For this test, I output the Epoch time for each calendar:

Time: 3 : 30 ( Epoch: 1300181400000 )
Time: 3 : 30 ( Epoch: 1300185000000 )

Notice that while the date and times are the "same," the Epoch time is different. In fact, it is different by 3,600,000 milliseconds. When we break this value down into larger time units we get:

  • 3,600,000 milliseconds
  • 3,600 seconds
  • 60 minutes
  • 1 hour

The day after daylight savings time has been put into effect in Mountain time, the two time zones are off by one hour, relative to Epoch time. This makes perfect sense since the clocks in one of the time zones went back one hour. So, while the manifestation of time appears to be the same, clock-wise, the underlying offsets represented by the calendars are different.

This seems like a lot of work; however, when you realize that ColdFusion's getTickCount() function returns the Epoch offset, you can start to get a sense that storing a timezone ID and an Epoch offset in the database might be all that is necessary for scheduled interactions across time zones. At least, that's what I'm starting to think; but then again, this whole journey has been far more complicated than I had originally anticipated.




Reader Comments

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.

Reply to this Comment

@Roger,

That sounds good. I think I'll be using that approach. However, if I want to do that every day, I believe I will need to store the Timezone in the database so that I can use it to increment the Epoch offset every day.

Then, the queries become much more straightforward:

WHERE nextEpochOffset <= #getTickCount()#

That's what I'm thinking. I'll try to implement something soon.

Reply to this Comment

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.

Reply to this Comment

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().

Reply to this Comment

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.

Reply to this Comment

@Dan,

Before this, I had never really put a lot of thought into how the server was configured. I probably just put it into *my* timezone so that dates made sense to *me* :) Yeah, I'm pretty forward thinking that way ;)

Also, I've not often dealt with the time portion of a date/time stamp. Typically, my apps display date. Which, of course is influenced by timezone, but is less likely to cause a noticeable discrepancy from user to user.

@Dustin,

At first, I was trying to store just the offset. But, it kept hurting my head :( I really had a serious stumbling block with all of this stuff. It was extremely difficult for me to wrap my head around it.

I also used to use the email gateways. Twilio is really awesome though and fairly inexpensive.

Reply to this Comment

@Terry,

I got a lot of advice from Paul (creator of the Timezone CFC). I took a look at it in the beginning; but, I didn't know enough about timezone stuff in the beginning to make heads and tails of it. Now, I'm in a much better place to learn from it!

Reply to this Comment

@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.)

Reply to this Comment

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.

Reply to this Comment

@Dan,

Ha ha, yikes! I can't imagine having to change thousands of date/times in one go for a display. That's gotta have a performance hit to some extent.

Reply to this Comment

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.

Reply to this Comment

@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 ...

Reply to this Comment

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).

Reply to this Comment

@WebManWalking,

I cannot even imagine having to do any of this by yourself! Just getting this far took me some serious mental gymnastics and frustrating and several bouts of wanted to never deal with time zones again :) Glad that this was useful even to someone like you who has such a deep level of understanding and experience with globalization.

Ha ha, I can't believe the Star Date is actually using logic. That's awesome. I used to just make it up:

http://www.youtube.com/watch?v=Ivn44-TKwZs&feature=player_detailpage#t=23s

@Dave,

Oops :) Thanks for the spelling update - I guess my mind is just "Savings" oriented ;)

@Nick,

I wonder if this has to do with the fact that you are using ODBC date functions rather than just createDateTime()? Perhaps the error is happening at the ColdFusion level (when an "invalid" date is being used)?

When I was playing with the Java TimeZone class, I did notice that if I tried to set() an invalid date, the Java class would automatically round up (I think) for me.

I've not done too much with ODBC date functions (I'm not actually sure what they do differently). Also, I was running my code above on 8.0.1. (I don't use CF9 as much as I should!).

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

@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.

Reply to this Comment

@Nick,

I'll definitely keep my eye open for that; certainly, I do like to think of my dates as numbers a lot :D I'm still trying to make my way into this crazy, funky world of timezones and DST, though, so I probably won't have any great insights!

@Magnus,

Definitely a way to help the user select their correct timezone would be a must! It looks like there are some ways, in JavaScript, to get "offsets", but not necessarily a timezone ID. As such, I think some fuzzy matching can be made, but not necessarily one that is perfect. I'll be playing around with that at some point soon.

@Pete,

Ha ha, one timezone for the win!!! I really like the idea of linking timezones to zip codes. I did find some stuff online for that; but, it looked like it only used about 10 different timezones... that seemed crazy at the time given the list above; however, now that I think about it, it was a US set of zip codes so, perhaps, 10 different timezones may have been fairly usable. I'll have to look into that again.

@David L,

I like the idea of storing all the times in UTC. And actually, this morning, I looked at using the TimeZone and Calendar Java classes as a way to translate values across time zones... to see if it was any easier. See the link below. Hopefully, that will help simplify some of the checks... but then again, this is all super new to me.

@All,

This morning, I looked at using the Java TimeZone and GregorianCalendar classes as a way to convert date/time values across time zones without having to worry about any localization rules:

http://www.bennadel.com/blog/2260-Converting-Dates-Across-Time-Zones-Using-ColdFusion-And-Java.htm

Seems pretty cool. Just poking around, though, seeing how all this stuff works. Not sure if any of it will be super useful yet.

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

@Pete,

This stuff is crazy :)

@Chris,

Oh, vey cool! I use MySQL and had no idea that they had timezone related functions built in. Arrg!, I really need to dig through the MySQL docs one of these days. They're seem to be so many gems hidden within the engine.

@WebManWalking,

The whole legal battle for the Timezone stuff is crazy. I saw it start to pop up on Flipboard (an iPad app where I read my tech news... I never know which site it's coming from -- usually TechCrunch I think). I did see something that it all ended "ok"... but not sure what that means. I mean, Java 6 already has this stuff installed locally, so what can really happen? The whole thing seems too weird.

I guess, from the articles that you posted, the database gets updates a number of times each year; so, I suppose it's a lot less static that I would have though (hoped).

@David,

Cool video and cool site. I have never heard of that one. I'll be checking it out :)

Reply to this Comment

@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!

Reply to this Comment

@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.

Reply to this Comment

@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);">

Reply to this Comment

@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

Reply to this Comment

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.

Reply to this Comment

@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 ...

Reply to this Comment

@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.

Reply to this Comment

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?

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.