Solved

Applying Timezone Offset vs. Coverting Date to UTC Format

Posted on 2006-07-18
6
868 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
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

724 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