Posting to db as GMT date/time

Hi all, I have a forum app which is hosted in Arizona, but almost all users are here in Ohio.  When a message is posted or replied to, I use #CreateODBCDate(Now())# which inserts the post to Arizona Time (theire time zone doesn't seem to follow anyone elses).  I'm thinking that a better solution might be to post to GMT 0 time, then on db query and display, call the local time zone in Ohio time (EDT). Two issues: 1) How do I code the date as GMT for insertion to SQL 2000 2) How to I create the time difference for Ohio? I'm using CFMX 6.1 with Win 2003. Thanks, Mark

Sounds like a solid plan to me. Adjust to GMT on insert, display in whatever timezone you like. I believe the function GetTimeZoneInfo() will return a struct with the server's offset info. 1) Convert the date from whatever timezone to GMT (use the server's offset). 2) Display using the offset for EST (GMT -5), or whatever timezone you choose Example: PST is -8 hours behind GMT. Say you have a date of 7/2/04 4:00pm PST to insert. You'd insert it as 7/3/04 00:00am (7/2/04 4:00pm + 8 hours) and when you pull it out of the db and want to display it in say EST, you would subtract 5 hours and get 7/2/04 7:00PM Hi all, I have a forum app which is hosted in Arizona, but almost all users are here in Ohio.  When a message is posted or replied to, I use #CreateODBCDate(Now())# which inserts the post to Arizona Time (theire time zone doesn't seem to follow anyone elses).  I'm thinking that a better solution might be to post to GMT 0 time, then on db query and display, call the local time zone in Ohio time (EDT). Two issues: 1) How do I code the date as GMT for insertion to SQL 2000 2) How to I create the time difference for Ohio? I'm using CFMX 6.1 with Win 2003. Thanks, Mark________________________________

Thanks for your insight. Thanks, Mark   _____ Sent: Friday, July 02, 2004 11:47 AM To: CF-Talk Subject: Re: Posting to db as GMT date/time Sounds like a solid plan to me. Adjust to GMT on insert, display in whatever timezone you like. I believe the function GetTimeZoneInfo() will return a struct with the server's offset info. 1) Convert the date from whatever timezone to GMT (use the server's offset). 2) Display using the offset for EST (GMT -5), or whatever timezone you choose Example: PST is -8 hours behind GMT. Say you have a date of 7/2/04 4:00pm PST to insert. You'd insert it as 7/3/04 00:00am (7/2/04 4:00pm + 8 hours) and when you pull it out of the db and want to display it in say EST, you would subtract 5 hours and get 7/2/04 7:00PM Hi all, I have a forum app which is hosted in Arizona, but almost all users are here in Ohio.  When a message is posted or replied to, I use #CreateODBCDate(Now())# which inserts the post to Arizona Time (theire time zone doesn't seem to follow anyone elses).  I'm thinking that a better solution might be to post to GMT 0 time, then on db query and display, call the local time zone in Ohio time (EDT). Two issues: 1) How do I code the date as GMT for insertion to SQL 2000 2) How to I create the time difference for Ohio? I'm using CFMX 6.1 with Win 2003. Thanks, Mark________________________________   _____  

Sorry if this was mentioned before... caught this thread part way through... I personally like to store the UTC in the database using either ColdFusion's functions or by using the getutcdate() function as a default field in SQL server. I then use JavaScript when the person logs in to find the time zone offset of THEIR computer.  Set that in a variable and use it to display the proper date/time in my output.  Works very well... One thing to keep in mind... when doing reports, queries, etc... you'll want to adjust the date/time in your queries in order to get accurate output. -Novak Thanks for your insight. Thanks, Mark   _____ Sent: Friday, July 02, 2004 11:47 AM To: CF-Talk Subject: Re: Posting to db as GMT date/time Sounds like a solid plan to me. Adjust to GMT on insert, display in whatever timezone you like. I believe the function GetTimeZoneInfo() will return a struct with the server's offset info. 1) Convert the date from whatever timezone to GMT (use the server's offset). 2) Display using the offset for EST (GMT -5), or whatever timezone you choose Example: PST is -8 hours behind GMT. Say you have a date of 7/2/04 4:00pm PST to insert. You'd insert it as 7/3/04 00:00am (7/2/04 4:00pm + 8 hours) and when you pull it out of the db and want to display it in say EST, you would subtract 5 hours and get 7/2/04 7:00PM Hi all, I have a forum app which is hosted in Arizona, but almost all users are here in Ohio.  When a message is posted or replied to, I use #CreateODBCDate(Now())# which inserts the post to Arizona Time (theire time zone doesn't seem to follow anyone elses).  I'm thinking that a better solution might be to post to GMT 0 time, then on db query and display, call the local time zone in Ohio time (EDT). Two issues: 1) How do I code the date as GMT for insertion to SQL 2000 2) How to I create the time difference for Ohio? I'm using CFMX 6.1 with Win 2003. Thanks, Mark________________________________   _____   _____  

We'll I'm still banging my head against the wall on this one. I have both the GetTimeZoneInfo() function as well as Paul Hastings' wonderful cfc. I can't seem to figure out how to subtract to GMT difference (in this case - 7:00) from the server time (Arizona Time). Also, as DST changes during the year, I would need to account for that as well. <!--- Time Zone Info Function ---> <cfset MyTimeZoneInfo = GetTimeZoneInfo()> <cfoutput> Arizona Time Information:<br /> Local Time is offset #MyTimeZoneInfo.utcTotalOffset# minutes from UTC.<br /> Local Time is offset #MyTimeZoneInfo.utcHourOffset# hours #MyTimeZoneInfo.utcMinuteOffset# minutes from UTC.<br /> Daylight savings time is<cfif #MyTimeZoneInfo.isDSTOn#> <b>on</b><cfelse> <b>off</b></cfif> for the host. </cfoutput> <!--- Output from Paul's code ---> server datetime ::  02-Jul-04  01:33 PM server timezone ::  Central Daylight Time selected timezone ::  US/Arizona raw offset ::  - 7.00 hrs offset ::  - 7.00 hrs DST savings ::  + 0.00 hrs uses DST ::  NO in DST ::  NO cast to UTC ::  02-Jul-04  08:33 PM cast from UTC ::  02-Jul-04  06:33 AM cast to server ::  02-Jul-04  03:33 PM cast from server ::  02-Jul-04  11:33 AM <!--- What I want to do ---> <cfset VARIABLES.gmtTime = #serverDateTime# + #castToUTC#> (I guess) <!--- Post as a Var to the DB ---> Thanks, Mark   _____   From: cf-talk@linenoize.com [mailto:cf-talk@linenoize.com] Sent: Friday, July 02, 2004 1:46 PM To: CF-Talk Subject: RE: Posting to db as GMT date/time Sorry if this was mentioned before... caught this thread part way through... I personally like to store the UTC in the database using either ColdFusion's functions or by using the getutcdate() function as a default field in SQL server. I then use JavaScript when the person logs in to find the time zone offset of THEIR computer.  Set that in a variable and use it to display the proper date/time in my output.  Works very well... One thing to keep in mind... when doing reports, queries, etc... you'll want to adjust the date/time in your queries in order to get accurate output. -Novak Thanks for your insight. Thanks, Mark   _____ Sent: Friday, July 02, 2004 11:47 AM To: CF-Talk Subject: Re: Posting to db as GMT date/time Sounds like a solid plan to me. Adjust to GMT on insert, display in whatever timezone you like. I believe the function GetTimeZoneInfo() will return a struct with the server's offset info. 1) Convert the date from whatever timezone to GMT (use the server's offset). 2) Display using the offset for EST (GMT -5), or whatever timezone you choose Example: PST is -8 hours behind GMT. Say you have a date of 7/2/04 4:00pm PST to insert. You'd insert it as 7/3/04 00:00am (7/2/04 4:00pm + 8 hours) and when you pull it out of the db and want to display it in say EST, you would subtract 5 hours and get 7/2/04 7:00PM Hi all, I have a forum app which is hosted in Arizona, but almost all users are here in Ohio.  When a message is posted or replied to, I use #CreateODBCDate(Now())# which inserts the post to Arizona Time (theire time zone doesn't seem to follow anyone elses).  I'm thinking that a better solution might be to post to GMT 0 time, then on db query and display, call the local time zone in Ohio time (EDT). Two issues: 1) How do I code the date as GMT for insertion to SQL 2000 2) How to I create the time difference for Ohio? I'm using CFMX 6.1 with Win 2003. Thanks, Mark________________________________   _____   _____   _____  

Looks like this is what I am after. <cfset MyTimeZoneInfo = GetTimeZoneInfo()> <cfset CurrentTime = Now()> <cfset VARIABLES.hourOffset = MyTimeZoneInfo.utcHourOffset> <cfset TimeToAdd = CreateTimeSpan(0,VARIABLES.hourOffset,0,0)> <cfset NewTime = CurrentTime + TimeToAdd> <cfoutput>   Current GMT Time = #createODBCTime(VARIABLES.newTime)# </cfoutput> Thanks, Mark   _____ Sent: Friday, July 02, 2004 2:35 PM To: CF-Talk Subject: RE: Posting to db as GMT date/time We'll I'm still banging my head against the wall on this one. I have both the GetTimeZoneInfo() function as well as Paul Hastings' wonderful cfc. I can't seem to figure out how to subtract to GMT difference (in this case - 7:00) from the server time (Arizona Time). Also, as DST changes during the year, I would need to account for that as well. <!--- Time Zone Info Function ---> <cfset MyTimeZoneInfo = GetTimeZoneInfo()> <cfoutput> Arizona Time Information:<br /> Local Time is offset #MyTimeZoneInfo.utcTotalOffset# minutes from UTC.<br /> Local Time is offset #MyTimeZoneInfo.utcHourOffset# hours #MyTimeZoneInfo.utcMinuteOffset# minutes from UTC.<br /> Daylight savings time is<cfif #MyTimeZoneInfo.isDSTOn#> <b>on</b><cfelse> <b>off</b></cfif> for the host. </cfoutput> <!--- Output from Paul's code ---> server datetime ::  02-Jul-04  01:33 PM server timezone ::  Central Daylight Time selected timezone ::  US/Arizona raw offset ::  - 7.00 hrs offset ::  - 7.00 hrs DST savings ::  + 0.00 hrs uses DST ::  NO in DST ::  NO cast to UTC ::  02-Jul-04  08:33 PM cast from UTC ::  02-Jul-04  06:33 AM cast to server ::  02-Jul-04  03:33 PM cast from server ::  02-Jul-04  11:33 AM <!--- What I want to do ---> <cfset VARIABLES.gmtTime = #serverDateTime# + #castToUTC#> (I guess) <!--- Post as a Var to the DB ---> Thanks, Mark   _____   From: cf-talk@linenoize.com [mailto:cf-talk@linenoize.com] Sent: Friday, July 02, 2004 1:46 PM To: CF-Talk Subject: RE: Posting to db as GMT date/time Sorry if this was mentioned before... caught this thread part way through... I personally like to store the UTC in the database using either ColdFusion's functions or by using the getutcdate() function as a default field in SQL server. I then use JavaScript when the person logs in to find the time zone offset of THEIR computer.  Set that in a variable and use it to display the proper date/time in my output.  Works very well... One thing to keep in mind... when doing reports, queries, etc... you'll want to adjust the date/time in your queries in order to get accurate output. -Novak Thanks for your insight. Thanks, Mark   _____ Sent: Friday, July 02, 2004 11:47 AM To: CF-Talk Subject: Re: Posting to db as GMT date/time Sounds like a solid plan to me. Adjust to GMT on insert, display in whatever timezone you like. I believe the function GetTimeZoneInfo() will return a struct with the server's offset info. 1) Convert the date from whatever timezone to GMT (use the server's offset). 2) Display using the offset for EST (GMT -5), or whatever timezone you choose Example: PST is -8 hours behind GMT. Say you have a date of 7/2/04 4:00pm PST to insert. You'd insert it as 7/3/04 00:00am (7/2/04 4:00pm + 8 hours) and when you pull it out of the db and want to display it in say EST, you would subtract 5 hours and get 7/2/04 7:00PM Hi all, I have a forum app which is hosted in Arizona, but almost all users are here in Ohio.  When a message is posted or replied to, I use #CreateODBCDate(Now())# which inserts the post to Arizona Time (theire time zone doesn't seem to follow anyone elses).  I'm thinking that a better solution might be to post to GMT 0 time, then on db query and display, call the local time zone in Ohio time (EDT). Two issues: 1) How do I code the date as GMT for insertion to SQL 2000 2) How to I create the time difference for Ohio? I'm using CFMX 6.1 with Win 2003. Thanks, Mark________________________________   _____   _____   _____   _____  

>  1) How do I code the date as GMT for insertion to SQL 2000 getUTCDate(). >  2) How to I create the time difference for Ohio? dateDiff or see this: http://www.sustainablegis.com/projects/tz/testTZCFC.cfm