Rip's Domain

Need some locale advice.

Posted in ColdFusion by rip747 on November 9, 2006

Need to reach out to the community here for some help.

My current project coming up involves taking a couple of websites and merging them so that run off one centralized database and site. This kinda like what Instantspot and about 1000 other websites do (including wordpress!). The issue is that the current sites are all over the country so obviously I’m going to have to display the date and time for that site’s current timezone.

The question is how would take the timestamp that is currently stored in the database and convert it to UTC time? What is the best way to go about doing this. Are there any CFCs out there that could take care of this for me.

6 Responses

Subscribe to comments with RSS.

  1. dan said, on November 9, 2006 at 9:05 pm

    I will think about it some more but why not read all of the records into a cfquery. Then you could loop the query and update the date using the cf built in function

    DateConvert(conversion-type, date)

    conversion-type
    * “local2Utc” Converts local time to UTC time
    * “utc2Loca” Converts UTC time to local time
    date
    ColdFusion date and time string. To create a ColdFusion date and time, use CreateDateTime.

  2. Rob Gonda said, on November 10, 2006 at 12:14 am

    you would need to store the UTC time and time-zone… then depending on what you need to do with that time, you may need a local time field in the DB as well. I need it for scheduled events … If you do that, then you need to schedule a task to update that in the DB, since different time zones update their time (relative to UTC) at different dates… moreover, some countries are not even near standard, and they have a 2h difference for DLS.
    If you only need the simple route and store UTC and time zones, Java has built-in support to timezone transformations…
    Summarizing, depending on your needs, this task may take you a few hours or a few weeks.

    Give me some more direction and I’ll add some more thoughts/experiences.

  3. Al Davidson said, on November 10, 2006 at 7:05 am

    When I’ve done things like this before, I’ve always stored every date/time behind the scenes in UTC. The time zone and locale-specific time is really only a property of the interface, so if you go with that principle, you just convert UTC datetimes to whatever locale the current site uses on display, and make sure that :
    – whenever you display a date/time, you show the UTC offset that’s been applied, and
    – whenever a datetime is being *inputted*, you let the user choose the UTC offset. You can default this to the timezone stored against the site. Then you just use the given offset to convert it to UTC before storing.

    That way, you always know that your date/time calculations will be consistent, and you also get the added flexibility (should you want it) of letting each individual *visitor* choose their timezone if they want to. Just because the site might be based in New York doesn’t mean that everyone looking at it will be🙂

    Good luck!

  4. Jason said, on November 10, 2006 at 9:15 am

    You might want to look at the a CFC called Timezone.cfc if you have not already. I use it for this type of stuff all the time.

    http://www.sustainablegis.com/projects/tz/testTZCFC.cfm

    This takes into account Daylight Savings Time, whereas I don’t think the built-in conversion in CF does. I could be wrong on that…. I always use this so I never worry about it.

  5. PaulH said, on November 10, 2006 at 1:20 pm

    if casting to a user/standardized tz is critical, then you can’t use cf’s datetimes as it always assumes it’s server tz. see this:

    http://www.sustainablegis.com/blog/cfg11n/index.cfm?mode=entry&entry=77223B6A-20ED-7DEE-2AB7FBB1F37ABD77

    to move the existing database datetimes to UTC you’ll need to know what tz these were in the first place, i guess assume server tz.

  6. rip747 said, on November 10, 2006 at 1:56 pm

    I want to thank everyone who gave me some ideas. You guys have no idea how much help you gave me. Thank you all. Sorry for the all inclusive thank you, but I’m up to my eyeballs in work at the moment.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: