Date Issue

[AJBJ]

I have a timestamp in the database based on Eastern standard time.  How do I subtract 3 hours so that when I run a report for the client that it shows West Coast time.

Thanks

Anthony

[Adam Cameron.]

There's a couple of answers to this.

Is the client always going to be on the West Coast, or is that just an example?  IE: do you need to adjust the times to be in [some other timezone], depending on where the person is viewing the report from, or just "they're on the West Coast"?

Do you want to make this date alteration on the database side of things (which is where I'd normally try to be massaging my data, before sending it back to CF), or does it need to be done after CF receives the data.  If it's best done on the DB side of things, then you need to look @ what your DB offers by way of date manipulation functions.  If it's best done on the CF side of things, then CF has a whole bunch of date-manipulation functions - http://livedocs.adobe.com/coldfusion/8/htmldocs/functions-pt0_05.html#1098968 - and in particular it has a dateAdd() function for modifying dates: http://livedocs.adobe.com/coldfusion/8/htmldocs/functions_c-d_25.html#1102869.

The best first port of call when you're wondering how to do stuff with CF is to look @ the docs.  For API stuff like functions and tags, the docs are all broken down into categories, so it's pretty easy to find stuff.

--

Adam

[Guest]

Hi:

    Normally you would get the clients timezone JavaScript and store it in Session or somewhere else in the application prior to this.  Hard coding it is a bad idea.  Here is one Blog article that shows how.

  http://josephscott.org/archives/2009/08/detecting-client-side-time-zone-offset-via-javascript/

   So lets say that you have a timestamp that was originally taken on the ColdFusion server.  To complicate things lets say it was not stored in GMT but rather that it was created on a server that was using its own Local Time.  (I am not going to consider DST at this time but as a side note if you are going to use Local Server Timestamps you want to have a method to also stores the offset in use at the time the stamp was generated.)

<cfscript>
   ClientTimeZone = 1;                 // Normally this would be acquired from the Brower via JavaScript and stored in Session.
   Timestamp = Qry.TimeStamp;  // I am assuming this is retrieved in a query but it could be any number of ways.
   tzInfo = GetTimeZoneInfo();       // will allow us to get the servers current timezone offset in hours.
   tsGMTTime = DateAdd("h", tzInfo.utcHourOffset, Timestamp);
   clientTime = DateAdd("h", ClientTimeZone, tsGMTTime);
</cfscript>
<cfoutput>
    Timestamp in UTC: #tsGMTTime#<br />
    TimeStamp in Clients Time: #clientTime#
</cfoutput>

    Note that one could in fact simply add the ClientTimeZone to the utcHourOffset of the server and just perform one DateAdd().   This is just one example.  I could have written this calc several ways.

    Of course you would also want to format the time for the the user.  Assuming that you already know the locale from the browser you can easily take the timestamp and format it with LSDateFormat() and LSTimeFormat();

    In your case it would be possible to simply use DateAdd to add 3 hours.  However the above method would be more realistic for code reuse.  Ultimately you would want to move it into a UDF or CFC.

    There is alot more to consider depending on your use case however this should get you most of the way there.

-Joe