Solved

Applying Timezone Offset vs. Coverting Date to UTC Format

Posted on 2006-07-18
6
851 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
  • 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now