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

On 2/28/07, Chris McKeever <cgmckeever@gmail.com> 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.

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/
marcus@synchromedia.co.uk | http://www.synchromedia.co.uk/

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/
> marcus@synchromedia.co.uk | 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.

I think the best way is to always store an unsigned integer epoch time.

Cheers,
-Dana

-----Original Message-----
From: David T. Ashley [mailto:dashley@gmail.com]
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 <cgmckeever@gmail.com> 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 doesone 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 ****************************
******************************************

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 [mailtoana.Diederich@wal-mart.com]
> 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:dashley@gmail.com]
> 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 <cgmckeever@gmail.com> 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 ****************************
> ******************************************
>

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 <marcus@synchromedia.co.uk> 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/
> marcus@synchromedia.co.uk | http://www.synchromedia.co.uk/
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=cgmckeever@gmail.com
>
>