Creating date/time for scheduled event while taking user's time zone into consideration

Good morning. I'm building an application for a client (using Coldfusion MX 7 & MySQL) that allows users to schedule email reminders for themselves.  When they schedule this reminder, they choose an event date from a calendar, .. specify the time of day, .. and then indicate specifically how many days before the event they'd like for the email reminder to be sent to them.  This is simple enough to work with, as I can just use ColdFusion's built-in "DateDiff" function to create the proper date/time value.  However, .. the client recently asked me how I would handle the user's different time zones, and I'm stumped as to what I should do.  

First off, .. will it be necessary for me to include a "Choose Your Time Zone" select dropdown box in my form (used for scheduling reminders)?  See this url for an example --> http://www.timeanddate.com/worldclock/converter.html.  Or ---- is there some way for me to automatically detect and extract the time zone from the user's workstation?  

I need some guidance and examples on how I should calculate my date/time value based on the user's time zone.  I'm open to using whatever methods are suggested. Any help is appreciated.

Thanks in advance,
- Yvan  
IDEASDesignAsked:
Who is Participating?
 
aseusaincCommented:
Just reread your above post, and realized you were using 12 hour time.  So you'll probably want to change that to 24 hour time, it's easier to work with.  Assuming the form field name is ampm, something like:

<cfif ampm IS "PM" AND hours NEQ 12>
  <cfset hours = hours + 12>
</cfif>
<cfset thedate = "#date# #hours#:#minutes#:00">
<cfset thedate = DateAdd('h', timeoffset, thedate)>
0
 
aseusaincCommented:
There are ways of extracting time zone info from a client, but they are not very reliable.

Your idea of using a drop down for the user to supply their timezone would be best.  Then just do a datediff to store the data in the db based on GMT-0.  In your event that handles sending reminders, just datediff the local server time to GMT as well.
0
 
IDEASDesignAuthor Commented:
Yes, I pretty much figured that this is what I'd need to do -- just store the GMT or UTC converted date in the database.  I don't know what all the different time zone offsets are, though, .. which is why I was hoping to get some code examples.  For example, .. what types of integer values should be contained within each of the options in my select dropdown? Or do I have to include the specific names of the timezones in each of the select options?

What about GetTimeZoneInfo?
http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=00000501.htm#1105446

I also found this code sample for it:  

<cfset tz = CreateObject("java", "java.util.TimeZone")>
<cfset tz = tz.getDefault()>
<cfoutput>TimeZone: #tz.getDisplayName()# (#tz.getID()#)</cfoutput>

This returned "TimeZone: GMT-07:00 (GMT-07:00)" for me when I ran it off my server, which is apprently the timezone that I'm located in.  But what good does this do me, really? How do I parse this, ... or more specifically, .. how do I extract the offset value as a positive or negative integer and store it in a variable?

Surely, someone must've had to implement something like this at some point in or another.  I would appreciate any code samples which bring me closer to my goal, .. which is to create a GMT/UTC adjusted date time object that's relative to the server time, by whatever means that are necessary.  

Thanks,
- Yvan    



0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
aseusaincCommented:
OKie, here's the skinny...

On your form where the user enters their apppointment/reminder/whatever, they have a dropdown for timezone.  Something like this:

<select name="timeoffset">
<option value="5">GMT -05:00  Indiana East</option>
<option value="5">GMT -05:00  Eastern Time</option>
<option value="6">GMT -06:00  Central Time</option>
<option value="7">GMT -07:00  Mountain Time</option>
<option value="8">GMT -08:00  Pacific Time</option>
</select>

Remember to set the values to the opposite of the actual zone...IE: gmt-5 is really +5.


You'll pass that to your form handler page along with a date and time that they want their reminder.  In that page, you'll want to convert to UTC/GMT-0 to store to your database.  You can just dateadd like so:

<cfset thedate = DateAdd('h', timeoffset, thedate)>


Now, I'm guessing you have a CF Event that runs like every so often to check for reminders, so in that CF page, you want to adjust your checktime to also be true UTC like so:

<cfset checktime = DateAdd('h', 5, now())>    (I used 5 because I'm EST, GMT -05:00, adjust accordingly)

Now when running a select statement, use the #checktime# to base your logic for which records need a reminder.

You'll need to do some stuff during daylight savings time because some freaks dont ever change, so twice a year you may want to modify the values in your dropdown on the form page accordingly.

0
 
IDEASDesignAuthor Commented:
Thanks so much!  So does the list of timezones in your example above contain the actual offset values for the different timezones? (Can I safely just copy & paste?) Also, .. are you sure that the DateAdd function can be used to offset the hour within a date/time object?  I ask because I've never seen it used this way before (I've only used it with calendar dates) and just want to confirm.

It seems that you're suggesting that I store the time the user has entered into the database (along with the chosen timezone) and that I caculate the offset only when my scheduledc process runs, .. . rather than storing the GMT/UTC adjusted date/time value in the database.  Correct?  

One last thing -- when my scheduled process runs, .. what kind of logic shoudl I be using to determine whether or not an email reminder should be sent?  Would it be something as simple as this?

<cfif checktime GTE Now() AND already_sent NEQ 'YES'>

[do stuff / send email]

</cfif>

Or will I need to round my Now() value to the nearest hour and look for an exact match?

Thanks for your help.
- Yvan
   
0
 
aseusaincCommented:
The zones in my example are correct, though you may want to expand on it a bit.  Also, yes dateadd can be used to offset exactly as we're using it.  The 'h' signifies that we're adding hours.

No, you should store the time the user entered AFTER you apply the dateadded conversion.  Store it in UTC (GMT - 0).

How you determine when to send depends on how often you are running.  Lets just say every 15 minutes for example.  Having a column in the database that flags "already_sent" is a good idea so that if anything goes wrong, you aren't spamming their mailbox.  You will probably want to use another dateadd for minutes and set some sort of range.  Something like:


<cfset checktime = DateAdd('h', 5, now())>               server time as GMT0'
<cfset checktime = DateAdd('m', -5, checktime)>             lets just go back 5 minutes to add a llittle buffer time
<cfset checktime2 = DateAdd('m', 15, checktime)>     15 minutes from now GMT0
<cfquery blah blah blah>
SELECT *
FROM sometable
WHERE reminder > '#checktime#' AND reminder < '#checktime2#' AND already_sent = 0

<cfoutput query=whatever">
send emails/etc
also update sometable set already_sent = 1 where some sort of indexkey that uniquely identifies this record.
</cfoutput>

0
 
IDEASDesignAuthor Commented:
Okay, I understand.   I can definitely work with this.  I will store the date in the database in UTC.

One thing that I'm confused about is how to create the "full" date/time object (which I'm assuming will be needed to apply the conversion to).  In my form, for example, I have the following:

1. A javascript calendar (which stores the chosen date in 'm/d/yyyy' format)
2. 2 time selection dropdown boxes (numbers 1-12 in a dropdown box, plus AM & PM selectors)
3. A timezone offset selection dropdown box

Somehow (or so I'm assuming) I will need to stitch these three together and re-format them in such a way that the UTC conversion will be the most reliable.  If I can figure that part out, then I'm confident that I'll be able to wrap the rest of this up on my own. Would you happen to know the proper conversion formula, by any chance?  

By the way, I've decided to NOT including the provision to choose the minutes in my form.  My scheduled process will be running hourly, and I'm thinking that perhaps I could just "round off" the server time to the nearest hour somehow, and just look for exact matches.  Would that be possible, you think? And if so, is there a special formula for that as well?

Thanks!
- Yvan

 
0
 
aseusaincCommented:
Lets just assume your form fields are named date, hours, minutes, timeoffset

To set up for the conversion, you can just combine the strings like so:

<cfset thedate = "#date# #hours#:#minutes#:00">

then adjust to UTC

<cfset thedate = DateAdd('h', timeoffset, thedate)>

thats about it...pretty simple.

As for rounding, that should be fine.  just do your scheduled process hourly.  You may still want to put a few minutes buffer in just to compensate for anything that may cause one to be skipped.
0
 
IDEASDesignAuthor Commented:
Thank you so much for the examples!  I think I've managed to implement this successfully. Fill out the form on this page, submit it, and then look at the output I'm generating and let me know if things seem in order to you:

http://65.110.79.67/staging/reminder.cfm 

I think I'e got it thoguh.  Here is the code I stitched together using your examples:

      <!-- CREATE BACKUP OF VARIABLE FOR DISPLAY PURPOSES //--->

      <cfset FORM.delivery_time12 = #FORM.delivery_time1#>
      
      <!-- CREATE BACKUP OF VARIABLE FOR DISPLAY PURPOSES //--->      

      <!--- CONVERT TO 24 HOUR TIME IF NECESSARY //--->      

      <cfif FORM.delivery_time3 EQ 'PM' AND FORM.delivery_time1 NEQ 12>
      
            <cfset FORM.delivery_time1 = #FORM.delivery_time1# + 12>
        
      </cfif>

      <!--- CONVERT TO 24 HOUR TIME IF NECESSARY //--->

      <!--- CREATE PROPER DATE/TIME OBJECT BY STITCHING TOGETHER FORM VARIABLES //--->      
      
      <cfset dateobject = "#FORM.reminder_date# #FORM.delivery_time1#:00:00">      
      
      <!--- CREATE PROPER DATE/TIME OBJECT BY STITCHING TOGETHER FORM VARIABLES //--->      
      
      <!--- CONVERT DATE/TIME OBJECT TO GMT/UTC FORMAT //--->            

      <cfset utcdate = DateAdd('h', FORM.timezone_offset, dateobject)>      

      <cfoutput>
      
      <p>&nbsp;</p>
      
      <p align="center">#FORM.reminder_date# #FORM.delivery_time12#:00:00 #FORM.delivery_time3# -- COVERTED TO UTC / 24-HOUR TIME =  #DateFormat(utcdate,"m/d/yyyy hh:00:00")#</p>
      
      </cfoutput>      

      <!--- CONVERT DATE/TIME OBJECT TO GMT/UTC FORMAT //--->

Thanks!
- Yvan
0
 
aseusaincCommented:
Looks good, though there is some sort of javascript bug that when I change my date on the calendar, the time dropdowns vanish.  But the code above looks good.

Could possibly be me, I just spent the last 2 hrs removing some Vundo trojan.  Who knows whats hosed on this machine now :(
0
 
IDEASDesignAuthor Commented:
Really?  What browser and OS are you using?  

- Yvan
0
 
aseusaincCommented:
Win XP and IE - no workie...I even TS'd into a W2K3 server/IE at work and also no workie.

Win XP and Firefox works fine.
0
 
IDEASDesignAuthor Commented:
I'm not sure what's up with you guys' PCs, but I just tested it under IE6 under WinXP and am having no problems whatsoever. Everything looks like it's supposed to and is working as expected.  

- Yvan
0
 
aseusaincCommented:
Just tried it from a 3rd PC, WinXP and IE and it works fine.  I think it may be something to do with security settings??
0
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.