Re: Best Practice: Timezones and web development
Wanted to thank everyone for the insights for some of this date/time issues! Pretty much what I was looking at, just wanted a little confirmation! I will also need to research that PHP function - Thanks - Chris On 3/6/07, Marcus Bointon <[EMAIL PROTECTED]> wrote: On 6 Mar 2007, at 17:12, David T. Ashley wrote: > Best practice is that all times maintained in a database (or > anywhere on the > server) are UTC, and are only converted to local timezone and/or > adjusted to > daylight savings time as required to display data for a specific user. Exactly right. > Now, as far as the best way to implement the two paragraphs above > (especially with DST), I have not a clue. I do this using the date extension that was updated in PHP 5.1. I store the string representation of the time zone, for example 'Europe/ London', and set that as the time environment whenever a session is started using: http://www.php.net/manual/en/function.date-default- timezone-set.php After that it all just magically works - whenever you call date() and friends, it's all corrected for the time zone. A slightly harder question is how to get the user's time zone in the first place. You can take a wild guess according to their IP, but it could well be wrong. Next you could use Javascript to find out the local time and get an offset, but then you have no way of getting DST info, and it doesn't tell you where they really are. Finally, you can just ask - I've made the time zone a user preference, and most systems I've seen do the same. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Best Practice: Timezones and web development
Remember that if you supply an argument to UNIX_TIMESTAMP(), the argument is interpreted according to the time zone of the connection, not the time zone of the server. I wonder if some combination of JavaScript's getTimezoneOffset() method, a hidden form field, and using the returned value to set the connection's time zone would get you anywhere. Perhaps not, since the return value of getTimezoneOffset() is already adjusted for DST and I don't know how you could translate the offset into a MySQL time zone setting anyways. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Dana Diederich [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 06, 2007 2:43 PM > To: David T. Ashley; Chris McKeever > Cc: mysql@lists.mysql.com > Subject: RE: Best Practice: Timezones and web development > > I think the best way is to always store an unsigned integer > epoch time. > > Cheers, > -Dana > > -Original Message- > From: David T. Ashley [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 06, 2007 11:12 AM > To: Chris McKeever > Cc: mysql@lists.mysql.com > Subject: Re: Best Practice: Timezones and web development > > On 2/28/07, Chris McKeever <[EMAIL PROTECTED]> wrote: > > > > Does anyone have any resources, guides, insight into the > best practice > > for storing date/time information when developing a custom web app? > > > > I am mainly concerned with how the TZ should be stored? > Should it go > > in as UTC and the code accounts for the user TZ? How does > one handle > > tracking the users Daylight Savings etc > > > Best practice is that all times maintained in a database (or > anywhere on > the > server) are UTC, and are only converted to local timezone and/or > adjusted to > daylight savings time as required to display data for a specific user. > > This means, for example, that Randy in California and Sven in Sweden > (different users on the same system) will see the same record from a > database displayed with different times (because the time is converted > to > their local timzone before display). > > Now, as far as the best way to implement the two paragraphs above > (especially with DST), I have not a clue. > > > > - > *** > *** This email and any files transmitted with it are confidential > and intended solely for the individual or entity to whom they are > addressed. If you have received this email in error destroy it > immediately. ** > Wal-Mart Confidential > ** > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Best Practice: Timezones and web development
I think the best way is to always store an unsigned integer epoch time. Cheers, -Dana -Original Message- From: David T. Ashley [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 06, 2007 11:12 AM To: Chris McKeever Cc: mysql@lists.mysql.com Subject: Re: Best Practice: Timezones and web development On 2/28/07, Chris McKeever <[EMAIL PROTECTED]> wrote: > > Does anyone have any resources, guides, insight into the best practice > for storing date/time information when developing a custom web app? > > I am mainly concerned with how the TZ should be stored? Should it go > in as UTC and the code accounts for the user TZ? How does one handle > tracking the users Daylight Savings etc Best practice is that all times maintained in a database (or anywhere on the server) are UTC, and are only converted to local timezone and/or adjusted to daylight savings time as required to display data for a specific user. This means, for example, that Randy in California and Sven in Sweden (different users on the same system) will see the same record from a database displayed with different times (because the time is converted to their local timzone before display). Now, as far as the best way to implement the two paragraphs above (especially with DST), I have not a clue. - *** *** This email and any files transmitted with it are confidential and intended solely for the individual or entity to whom they are addressed. If you have received this email in error destroy it immediately. ** Wal-Mart Confidential **
Re: Best Practice: Timezones and web development
On Tue, March 6, 2007 9:40, Marcus Bointon said: > On 6 Mar 2007, at 17:12, David T. Ashley wrote: > >> Best practice is that all times maintained in a database (or >> anywhere on the >> server) are UTC, and are only converted to local timezone and/or >> adjusted to >> daylight savings time as required to display data for a specific user. > > Exactly right. > >> Now, as far as the best way to implement the two paragraphs above >> (especially with DST), I have not a clue. > > I do this using the date extension that was updated in PHP 5.1. I > store the string representation of the time zone, for example 'Europe/ > London', and set that as the time environment whenever a session is > started using: http://www.php.net/manual/en/function.date-default- > timezone-set.php > After that it all just magically works - whenever you call date() and > friends, it's all corrected for the time zone. > > A slightly harder question is how to get the user's time zone in the > first place. You can take a wild guess according to their IP, but it > could well be wrong. Next you could use Javascript to find out the > local time and get an offset, but then you have no way of getting DST > info, and it doesn't tell you where they really are. Finally, you can > just ask - I've made the time zone a user preference, and most > systems I've seen do the same. > > Marcus > -- > Marcus Bointon > Synchromedia Limited: Creators of http://www.smartmessages.net/ > [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ Added problem. What if their computer clock is way off. Before I figured out session cookies I had cookies disappearing on random computers because the computer clock was a couple of days off. FWIW. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Practice: Timezones and web development
On 6 Mar 2007, at 17:12, David T. Ashley wrote: Best practice is that all times maintained in a database (or anywhere on the server) are UTC, and are only converted to local timezone and/or adjusted to daylight savings time as required to display data for a specific user. Exactly right. Now, as far as the best way to implement the two paragraphs above (especially with DST), I have not a clue. I do this using the date extension that was updated in PHP 5.1. I store the string representation of the time zone, for example 'Europe/ London', and set that as the time environment whenever a session is started using: http://www.php.net/manual/en/function.date-default- timezone-set.php After that it all just magically works - whenever you call date() and friends, it's all corrected for the time zone. A slightly harder question is how to get the user's time zone in the first place. You can take a wild guess according to their IP, but it could well be wrong. Next you could use Javascript to find out the local time and get an offset, but then you have no way of getting DST info, and it doesn't tell you where they really are. Finally, you can just ask - I've made the time zone a user preference, and most systems I've seen do the same. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Practice: Timezones and web development
On 2/28/07, Chris McKeever <[EMAIL PROTECTED]> wrote: Does anyone have any resources, guides, insight into the best practice for storing date/time information when developing a custom web app? I am mainly concerned with how the TZ should be stored? Should it go in as UTC and the code accounts for the user TZ? How does one handle tracking the users Daylight Savings etc Best practice is that all times maintained in a database (or anywhere on the server) are UTC, and are only converted to local timezone and/or adjusted to daylight savings time as required to display data for a specific user. This means, for example, that Randy in California and Sven in Sweden (different users on the same system) will see the same record from a database displayed with different times (because the time is converted to their local timzone before display). Now, as far as the best way to implement the two paragraphs above (especially with DST), I have not a clue.
Best Practice: Timezones and web development
Does anyone have any resources, guides, insight into the best practice for storing date/time information when developing a custom web app? I am mainly concerned with how the TZ should be stored? Should it go in as UTC and the code accounts for the user TZ? How does one handle tracking the users Daylight Savings etc Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
