Applying Timezone Offset vs. Coverting Date to UTC Format

Posted on 2006-07-18
Medium Priority
Last Modified: 2013-12-24
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


Question by:IDEASDesign
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
LVL 13

Expert Comment

ID: 17131920
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.

Expert Comment

ID: 17132152
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 (cwigginton@macromedia.com)
 * @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">

Author Comment

ID: 17133113
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

LVL 13

Accepted Solution

usachrisk1983 earned 2000 total points
ID: 17133172
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.

Expert Comment

ID: 17133250
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.

Author Comment

ID: 17133257
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

Featured Post

Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month9 days, 5 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question