Re: Compensating for Server Time
cf-talk wrote: Does that work in SQL 2000? I don't know. It works in Oracle and PostgreSQL. If there was a way to do this all in one single shot via SQL as it seems you have a solution for below I'd love to try it out.Unfortunately I can't seem to make the example you gave below work in SQL 2000. File an enhancement request :-) Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Compensating for Server Time
Tim wrote: If you want to it directly from SQL you can create a function and call that from your sql query create function localtime (@gmtdate datetime, @timezoneoffset int) returns datetime As begin declare @localtime datetime select @localtime = dateadd(hh, -1 * @timezoneoffset, @gmtdate) return @localtime end Then it can be called like this select localtime(getdate(), 3) But that requires you to know the time offset instead of the timezone. And the offset changes with DST. Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Compensating for Server Time
Chris Montgomery wrote: What's the best way to compensate for time on a server in a hosted environment when the time is set to a different time than a user's local time zone? Ex: client is in Central Time, server is on Eastern Time (one hour ahead); client wants everything to reflect local time. I use the AT TIME ZONE functionality from SQL, it even compensates for daylight savings time: testDB= select '2003-12-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-12-12 12:00:00 testDB= select '2003-07-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-06-12 11:00:00 Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Compensating for Server Time
there is a great little cfc @ cfczone.org that paul hastings whipped up that does some timezone work.best way, is to use _javascript_ to get the time of the browser, set that timezone offset as a cookie on the users browser, then access that with the cookie. whatever you call it variable name in cf, and do the time changes that way.i had to use it extensively on our site, www.navtrak.net, since we have clients whose vehicles report in other time zones than where our servers are located, on the east coast. so, if you have any other questions i can probably help. tony -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 3:55 PM To: CF-Talk Subject: Re: Compensating for Server Time Chris Montgomery wrote: What's the best way to compensate for time on a server in a hosted environment when the time is set to a different time than a user's local time zone? Ex: client is in Central Time, server is on Eastern Time (one hour ahead); client wants everything to reflect local time. I use the AT TIME ZONE functionality from SQL, it even compensates for daylight savings time: testDB= select '2003-12-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-12-12 12:00:00 testDB= select '2003-07-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-06-12 11:00:00 Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Compensating for Server Time
Does that work in SQL 2000? I've always used _javascript_ at logon to determine the time zone of the user's computer.I then store the time zone offset in a cookie or session/client variable.I then use a UDF to convert the time from the SQL server to that user's local time. If there was a way to do this all in one single shot via SQL as it seems you have a solution for below I'd love to try it out.Unfortunately I can't seem to make the example you gave below work in SQL 2000. -Novak -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 12:55 PM To: CF-Talk Subject: Re: Compensating for Server Time Chris Montgomery wrote: What's the best way to compensate for time on a server in a hosted environment when the time is set to a different time than a user's local time zone? Ex: client is in Central Time, server is on Eastern Time (one hour ahead); client wants everything to reflect local time. I use the AT TIME ZONE functionality from SQL, it even compensates for daylight savings time: testDB= select '2003-12-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-12-12 12:00:00 testDB= select '2003-07-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-06-12 11:00:00 Jochem -- Who needs virtual reality if you can just dream? - Loesje _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Compensating for Server Time
If you want to it directly from SQL you can create a function and call that from your sql query create function localtime (@gmtdate datetime, @timezoneoffset int) returns datetime As begin declare @localtime datetime select @localtime = dateadd(hh, -1 * @timezoneoffset, @gmtdate) return @localtime end Then it can be called like this select localtime(getdate(), 3) Tim -Original Message- From: cf-talk [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 4:54 PM To: CF-Talk Subject: RE: Compensating for Server Time Does that work in SQL 2000? I've always used _javascript_ at logon to determine the time zone of the user's computer.I then store the time zone offset in a cookie or session/client variable.I then use a UDF to convert the time from the SQL server to that user's local time. If there was a way to do this all in one single shot via SQL as it seems you have a solution for below I'd love to try it out.Unfortunately I can't seem to make the example you gave below work in SQL 2000. -Novak -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 12:55 PM To: CF-Talk Subject: Re: Compensating for Server Time Chris Montgomery wrote: What's the best way to compensate for time on a server in a hosted environment when the time is set to a different time than a user's local time zone? Ex: client is in Central Time, server is on Eastern Time (one hour ahead); client wants everything to reflect local time. I use the AT TIME ZONE functionality from SQL, it even compensates for daylight savings time: testDB= select '2003-12-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-12-12 12:00:00 testDB= select '2003-07-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-06-12 11:00:00 Jochem -- Who needs virtual reality if you can just dream? - Loesje _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Compensating for Server Time
but how are you going to *KNOW* the persons local time? _javascript_ is the only way. -Original Message- From: Tim [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 5:35 PM To: CF-Talk Subject: RE: Compensating for Server Time If you want to it directly from SQL you can create a function and call that from your sql query create function localtime (@gmtdate datetime, @timezoneoffset int) returns datetime As begin declare @localtime datetime select @localtime = dateadd(hh, -1 * @timezoneoffset, @gmtdate) return @localtime end Then it can be called like this select localtime(getdate(), 3) Tim -Original Message- From: cf-talk [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 4:54 PM To: CF-Talk Subject: RE: Compensating for Server Time Does that work in SQL 2000? I've always used _javascript_ at logon to determine the time zone of the user's computer.I then store the time zone offset in a cookie or session/client variable.I then use a UDF to convert the time from the SQL server to that user's local time. If there was a way to do this all in one single shot via SQL as it seems you have a solution for below I'd love to try it out.Unfortunately I can't seem to make the example you gave below work in SQL 2000. -Novak -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 12:55 PM To: CF-Talk Subject: Re: Compensating for Server Time Chris Montgomery wrote: What's the best way to compensate for time on a server in a hosted environment when the time is set to a different time than a user's local time zone? Ex: client is in Central Time, server is on Eastern Time (one hour ahead); client wants everything to reflect local time. I use the AT TIME ZONE functionality from SQL, it even compensates for daylight savings time: testDB= select '2003-12-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-12-12 12:00:00 testDB= select '2003-07-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-06-12 11:00:00 Jochem -- Who needs virtual reality if you can just dream? - Loesje _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Compensating for Server Time
read on tony, sorry, i see you were just explaining the way to the sql in one shot :) tony -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 5:38 PM To: CF-Talk Subject: RE: Compensating for Server Time but how are you going to *KNOW* the persons local time? _javascript_ is the only way. -Original Message- From: Tim [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 5:35 PM To: CF-Talk Subject: RE: Compensating for Server Time If you want to it directly from SQL you can create a function and call that from your sql query create function localtime (@gmtdate datetime, @timezoneoffset int) returns datetime As begin declare @localtime datetime select @localtime = dateadd(hh, -1 * @timezoneoffset, @gmtdate) return @localtime end Then it can be called like this select localtime(getdate(), 3) Tim -Original Message- From: cf-talk [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 4:54 PM To: CF-Talk Subject: RE: Compensating for Server Time Does that work in SQL 2000? I've always used _javascript_ at logon to determine the time zone of the user's computer.I then store the time zone offset in a cookie or session/client variable.I then use a UDF to convert the time from the SQL server to that user's local time. If there was a way to do this all in one single shot via SQL as it seems you have a solution for below I'd love to try it out.Unfortunately I can't seem to make the example you gave below work in SQL 2000. -Novak -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 12:55 PM To: CF-Talk Subject: Re: Compensating for Server Time Chris Montgomery wrote: What's the best way to compensate for time on a server in a hosted environment when the time is set to a different time than a user's local time zone? Ex: client is in Central Time, server is on Eastern Time (one hour ahead); client wants everything to reflect local time. I use the AT TIME ZONE functionality from SQL, it even compensates for daylight savings time: testDB= select '2003-12-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-12-12 12:00:00 testDB= select '2003-07-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-06-12 11:00:00 Jochem -- Who needs virtual reality if you can just dream? - Loesje _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Compensating for Server Time
Yes you can detect their time zone everytime they login or what I've done in the past is to build it in to the users profile, so that the user has the ability to select the time zone their in when they create/update their profile. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 5:38 PM To: CF-Talk Subject: RE: Compensating for Server Time but how are you going to *KNOW* the persons local time? _javascript_ is the only way. -Original Message- From: Tim [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 5:35 PM To: CF-Talk Subject: RE: Compensating for Server Time If you want to it directly from SQL you can create a function and call that from your sql query create function localtime (@gmtdate datetime, @timezoneoffset int) returns datetime As begin declare @localtime datetime select @localtime = dateadd(hh, -1 * @timezoneoffset, @gmtdate) return @localtime end Then it can be called like this select localtime(getdate(), 3) Tim -Original Message- From: cf-talk [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 4:54 PM To: CF-Talk Subject: RE: Compensating for Server Time Does that work in SQL 2000? I've always used _javascript_ at logon to determine the time zone of the user's computer.I then store the time zone offset in a cookie or session/client variable.I then use a UDF to convert the time from the SQL server to that user's local time. If there was a way to do this all in one single shot via SQL as it seems you have a solution for below I'd love to try it out.Unfortunately I can't seem to make the example you gave below work in SQL 2000. -Novak -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 12:55 PM To: CF-Talk Subject: Re: Compensating for Server Time Chris Montgomery wrote: What's the best way to compensate for time on a server in a hosted environment when the time is set to a different time than a user's local time zone? Ex: client is in Central Time, server is on Eastern Time (one hour ahead); client wants everything to reflect local time. I use the AT TIME ZONE functionality from SQL, it even compensates for daylight savings time: testDB= select '2003-12-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-12-12 12:00:00 testDB= select '2003-07-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-06-12 11:00:00 Jochem -- Who needs virtual reality if you can just dream? - Loesje _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Compensating for Server Time
sure, but just getting it from their browser time is the best way, if you let them input it, they can mess it up, user error, one more thing to tech support. either way is fine, but somethign that is easy to abstract from a dumb customer is always nice! tony -Original Message- From: Tim [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 5:52 PM To: CF-Talk Subject: RE: Compensating for Server Time Yes you can detect their time zone everytime they login or what I've done in the past is to build it in to the users profile, so that the user has the ability to select the time zone their in when they create/update their profile. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 5:38 PM To: CF-Talk Subject: RE: Compensating for Server Time but how are you going to *KNOW* the persons local time? _javascript_ is the only way. -Original Message- From: Tim [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 5:35 PM To: CF-Talk Subject: RE: Compensating for Server Time If you want to it directly from SQL you can create a function and call that from your sql query create function localtime (@gmtdate datetime, @timezoneoffset int) returns datetime As begin declare @localtime datetime select @localtime = dateadd(hh, -1 * @timezoneoffset, @gmtdate) return @localtime end Then it can be called like this select localtime(getdate(), 3) Tim -Original Message- From: cf-talk [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 4:54 PM To: CF-Talk Subject: RE: Compensating for Server Time Does that work in SQL 2000? I've always used _javascript_ at logon to determine the time zone of the user's computer.I then store the time zone offset in a cookie or session/client variable.I then use a UDF to convert the time from the SQL server to that user's local time. If there was a way to do this all in one single shot via SQL as it seems you have a solution for below I'd love to try it out.Unfortunately I can't seem to make the example you gave below work in SQL 2000. -Novak -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Sunday, November 16, 2003 12:55 PM To: CF-Talk Subject: Re: Compensating for Server Time Chris Montgomery wrote: What's the best way to compensate for time on a server in a hosted environment when the time is set to a different time than a user's local time zone? Ex: client is in Central Time, server is on Eastern Time (one hour ahead); client wants everything to reflect local time. I use the AT TIME ZONE functionality from SQL, it even compensates for daylight savings time: testDB= select '2003-12-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-12-12 12:00:00 testDB= select '2003-07-31 12:00:00' AT TIME ZONE 'cet'; timezone - 2003-06-12 11:00:00 Jochem -- Who needs virtual reality if you can just dream? - Loesje _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Compensating for Server Time
Sunday, November 16, 2003, 2:55:11 PM, Jochem van Dieten wrote: I use the AT TIME ZONE functionality from SQL, it even compensates for daylight savings time... Thanks Jochem, Tony, Tim, Novak, et. al. I'll look into your recommendations. I should have clarified the situation a bit more, however. The database is MS Access (for now; will probably upsize to MS SQL server soon). I want to keep this is simple as possible (because I'm a simple person). I am going to assume that the user's time zone is always local because my client's business is local. So, in effect, I need only subtract one hour from the server's time for any transaction (storing appointment times, mainly). I guess I could set this as a constant in a request variable in the application.cfm file and then use that for the current time from then on. Would that seem feasible? Cheers. -- Chris Montgomery Airtight Web Serviceshttp://www.airtightweb.com Web Development, Web Project Management, Software Sales 210-490-2415 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Compensating for Server Time
Howdy Tony, Sunday, November 16, 2003, 3:49:51 PM, Tony Weeg wrote: there is a great little cfc @ cfczone.org Ooops, forgot to mention: this is on CF5/Windows. I'll take a look at the CFC anyway, might give me some good tips. -- Chris Montgomery Airtight Web Serviceshttp://www.airtightweb.com Web Development, Web Project Management, Software Sales 210-490-2415 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Compensating for Server Time
Looks like LocalTime() at cflib.org might be just what the doctor ordered ( http://www.cflib.org/udf.cfm?ID=719 ). Gonna give it a try. -- Chris Montgomery Airtight Web Serviceshttp://www.airtightweb.com Web Development, Web Project Management, Software Sales 210-490-2415 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Compensating for Server Time
Sunday, November 16, 2003, 6:14:47 PM, Chris Montgomery wrote:
LocalTime() at cflib.org
This UDF uses cffunction, which I guess is an MX tag. I converted Chad
Jackson's UDF to be compatible with CF5 (in case anyone else wants it).
Note that the offset is for Central Daylight Time (-6).
!---
Function that returns adjusted local server time.
@return Returns a date object.
@orignal_author chad jackson
@author chris montgomery
@version 1, September 24, 2002
@version 2, November 16, 2003 (CF5)
---
cfscript
function LocalTime()
{
var timeZoneInfo = GetTimeZoneInfo();
// local time GMT offset.
var offset = -6;
var GMTtime = DateAdd('s', timeZoneInfo.UTCtotalOffset, Now() );
var theLocalTime = DateAdd('h',offset,GMTtime);
return theLocaltime;
}
/cfscript
pThe date/time on the server is: cfoutput#now()#/cfoutput/p
pThe local date/time is cfoutput#Localtime()#/cfoutput/p
Cheers.
--
Chris Montgomery
Airtight Web Serviceshttp://www.airtightweb.com
Web Development, Web Project Management, Software Sales
210-490-2415
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
