Applying Timezone Offset vs. Coverting Date to UTC Format

I'm developing an application for a client which allows registered users to schedule email reminders to be sent to themselves on a specific day and time.  

In the database, for each scheduled reminder, .. I am storing the following information:

1. The date of the actual event
2. The number of days before the event that the reminder email should be sent
3. The Timezone offset value (based on the user's U.S. timezone selection - 4 different choices)
4. The specific time of day that the reminder email should be sent (based on 24 hour time)

I now need to set up a scheduled process on the server which will run once every hour, and which will send out the email reminders if the calculated date/time values match.

For example, .. when the scheduled process runs at 7:00 am on 7/18/2006, .. and if there is a record in the database which indicates that there is an event on 7/19/2006 for which a reminder email should be sent one day in advance (also at 7:00 am) then the reminder email should be sent.

The problem that I'm having is that I was advised to convert all of my dates to UTC using the DateConvert() function, .. and I'm beginning to suspect that this step might NOT necessary, and that my simply adjusting the date/time values based on the timezone offset (for both the server time and the date/time on which the reminder email shoud be sent) should be adequate.  

So my question is this:  If I know where the server is physically located (and what the timezone offset value for it is), .. and if I have the timezone offset value for each reminder stored in the database, .. shouldn't that be enough for me to work with and send out my reminder emails on/at the correct day and time?  Or is still necessary for me to convert the date/time values to UTC format using DateConvert() ?  

Thanks in advance,
- Yvan


Who is Participating?
usachrisk1983Connect With a Mentor Commented:
To answer you question specifically --- yes, that it enough.  You can use the offset with your servers offset to determine when to send the notifications.  

To go a little more indepth - keep in mind offsets change for Daylight Savings Time, and they change on different days depending on where you are (not everyone changes their clock in the same month).  Also, it's possible that a location that uses DST may not in the future, or vice versa -- or to change offsets altogether.  This is all something you need to keep in the back of your head if you're going to go this route.
This is something I've strugged with like mad.  I have an application that runs in about 7 timezones, and I need to keep them all in sync.  First, I would just use EST, and then put everywhere in the application " in EST" next to date/times.  When revamping a year and a half back, I decided to let everyone see everything in their TZ, so I converted all my times to UTC in the database, and then created a huge conversion script (this is in the CF5 days) to convert server time to utc, and then utc to the users time.  I had to worry about everyones daylight savings settings, and all that craziness.

Then, in the last few months (and with an upgrade to CF7), I've discovered the wonders of timeZone.cfc (free, 3rd party).   This CFC will do all the conversions between any timezones, and uses Java's built-in functions to determine offsets, etc (since they vary by location, and also could change in the future).  

So although I don't have a "definately do this for this reason" type of answer, I would recommend using UTC.  Converting from UTC to a local time using java is much easier because you don't have to worry about anyones timezone, you just let them pick the TZ they want, and then do your conversion with a simple line of code.

Pros of using UTC:
- Server moves?  No biggie, UTC stays the same
- Server in different TZ than database? Doesn't matter.
- Don't have to worry about managing offsets, Java stores them.
- Offsets change?  DST changes?  Just update Java.

Cons of using UTC:
- Need to develop conversion script or use 3rd party tool.
- Need to remember all the date/times are in UTC in the DB.
I went through a similar project in the past. Converting to UTC gave us the best results. We stored our events in CST and then used the server offset to change it to the user's time zone when the calendar item was sent. Sending GMT instead of tracking what timezone each user was in and then lettiing their calendar app do the conversion from GMT. left in some comment lines for debug

For example:

<cfquery name="qry_training_for_cal" >
SELECT     dbo.Training_Events.Event_ID, dbo.Training_Events.Event_Title, dbo.Training_Events.Location, dbo.Training_Events.Event_Start_Date,  dbo.Training_Events.Event_End_Date, dbo.Training_Events.Event_Start_Time, dbo.Training_Events.Event_End_Time, dbo.Training_Events.description,
DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), getdate()), Event_Start_Time) AS GMT_Start_time,
DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), getdate()), Event_End_Time) AS GMT_End_time
FROM         dbo.Training_Events
WHERE     dbo.Training_Events.Event_ID = '#cfgridkey#'

check for Daylight Savings Time (DST) on: #mytime.isDSTon#

<cfset mytime = GetTimeZoneInfo()>

<!---difference in server time to UTC in hours #mytime.utcHourOffset# <br />
<cfset servertimeoffset = #mytime.utcHourOffset# >

<cfset neweventstart = dateadd('h',#servertimeoffset# + 0,qry_training_for_cal.GMT_Start_Time)>

<!---neweventstart = #timeformat(neweventstart,"HH:MM:SS")# <br />--->

<cfset neweventend = dateadd('h',#servertimeoffset# + 0,qry_training_for_cal.GMT_End_Time)>

<!---neweventend = #timeformat(neweventend,"HH:MM:SS")# <br />--->


stEvent = StructNew();

vCalOutput = vCal(stEvent);

 * Produces output used by the vCalendar standard for PIM's (such as Outlook).
 * There are other tags available such as (CF_AdvancedEmail) that will support multi-part mime encoding where the text of the attachment can be imbeded right into the email
 * @param stEvent        Structure containg the key/value pairs comprising the vCalendar data.  Keys are shown below:
 * @param stEvent.description        Description for the event.
 * @param stEvent.subject        Subject of the event.
 * @param stEvent.location        Location for the event.
 * @param stEvent.startTime        Event's start time in GMT.
 * @param stEvent.endTime        Event's end time in GMT.
 * @param stEvent.priority        Numeric priority for the event (1,2,3).
 * @return Returns a string.
 * @author Chris Wigginton (
 * @version 1.1, April 10, 2002
function vCal(stEvent)

      var description = "";
      var vCal = "";
      var CRLF=chr(13)&chr(10);
      if (NOT IsDefined("stEvent.startTime"))
            stEvent.startTime = DateConvert('local2utc',#neweventstart#);
      if (NOT IsDefined("stEvent.endTime"))
            stEvent.endTime = DateConvert('local2utc',#neweventend#);
      if (NOT IsDefined("stEvent.location"))
            stEvent.location = "#qry_training_for_cal.location#";
      if (NOT IsDefined("stEvent.subject"))
            stEvent.subject = "Training: #qry_training_for_cal.Event_Title#";
      if (NOT IsDefined("stEvent.description"))
            stEvent.description = "#qry_training_for_cal.description#";
      if (NOT IsDefined("stEvent.priority"))
            stEvent.priority = "1";
            stEvent.startDate = "#qry_training_for_cal.Event_Start_Date#";
            stEvent.endDate =  "#qry_training_for_cal.Event_End_Date#";

      vCal = "BEGIN:VCALENDAR" & CRLF;
      vCal = vCal & "PRODID:-//Microsoft Corporation//OutlookMIMEDIR//EN" & CRLF;
      vCal = vCal & "VERSION:1.0" & CRLF;
      vCal = vCal & "BEGIN:VEVENT" & CRLF;
      vCal = vCal & "DTSTART:" & DateFormat(stEvent.startDate,"yyyymmdd") & "T" & TimeFormat(stEvent.startTime, "HHmmss") & "Z" & CRLF;
      vCal = vCal & "DTEND:" & DateFormat(stEvent.endDate,"yyyymmdd") & "T" & TimeFormat(stEvent.endTime, "HHmmss") & "Z" & CRLF;
      vCal = vCal & "LOCATION:" & stEvent.location & CRLF;
      vCal = vCal & "SUMMARY;ENCODING=QUOTED-PRINTABLE:" & stEvent.subject & CRLF;
      // Convert CF_CRLF (13_10) into =0D=0A with CR/LF and indent sequences
      description = REReplace(stEvent.description,"[#Chr(13)##Chr(10)#]", "=0D=0A=#Chr(13)##Chr(10)#     ", "ALL");
      vCal = vCal & description & CRLF;
      vCal = vCal & "PRIORITY:" & stEvent.priority & CRLF;
      vCal = vCal & "END:VEVENT" & CRLF;
      vCal = vCal & "END:VCALENDAR" & CRLF;      
      return vCal;

<!--- debug to see vcal info
#vCalOutput# --->
<!--- write the ics and then send it from application mailbox --->
<!--- using san here, but cffile is the same commands --->

<cflock name="#request.physicalroot#\event_registration.ics" timeout="10" type="exclusive">

<cf_sanfile action="write" file="#request.physicalroot#\event_registration.ics" output="#vcaloutput#">
     <cfmail to="#to_parameter#" from="#from_parameter#" subject="Training Registration: qry_training.Event_Title#">
     <cfmailparam file="#request.physicalroot#\event_registration.ics">
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

IDEASDesignAuthor Commented:
Thank you both for your in-depth responses.  However, note that I already have a great deal of time invested in the project, and that I would prefer to not have to re-program everything -- especially if it isn't necessary.  So on that note, I reiterate my specific question:

If I know where the server is physically located (and what the timezone offset value for it is), .. and if I have the timezone offset value for each reminder stored in the database, .. shouldn't that be enough for me to work with and send out my reminder emails on/at the correct day and time?  

Or is still necessary for me to convert the date/time values to UTC format using DateConvert() ?

 - Yvan
Concur with usachrisk1983, I've tried to do the math to keep up with different zones and it was painful. Use the conversion and it will be less support tickets down the road.
IDEASDesignAuthor Commented:
Yes, .. you are right about daylight savings time variances.  I have made the conscious decision to ignore the possible inconsistencies that may arise because of that, however - though thankyou for  mentioning it.

I have accepted your answer and awarded you the 500pts.

- Yvan
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.