Solved

Applying Timezone Offset vs. Coverting Date to UTC Format

Posted on 2006-07-18
6
864 Views
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
 




 

0
Comment
Question by:IDEASDesign
[X]
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
6 Comments
 
LVL 13

Expert Comment

by:usachrisk1983
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.
0
 
LVL 3

Expert Comment

by:bhinshawnc1
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#'
</cfquery>

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 />--->

<cfscript>

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;
      
      vCal = vCal & "DESCRIPTION;ENCODING=QUOTED-PRINTABLE:";
      // 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;
      
}
</cfscript>


<!--- 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">
    </cfmail>      
</cflock>
0
 

Author Comment

by:IDEASDesign
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() ?

Thanks,
 - Yvan
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 13

Accepted Solution

by:
usachrisk1983 earned 500 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.
0
 
LVL 3

Expert Comment

by:bhinshawnc1
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.
0
 

Author Comment

by:IDEASDesign
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.

Thanks!
- Yvan
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

737 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