Re: [PHP] Holding "datetimes" in a DB.
Paul McGarry wrote: My heads trying to remember something I may or may not have known to start with. > >If I hold datetimes in a DB in UTC and can represent a date to a user >based upon a user preference Timezone (not an offset, but a real >timezone : Europe/Berlin, etc.) am I missing anything? Perhaps the "best" alternative is to store the data in the DB in a datatype that includes timezone, eg "timestamp with timezone" http://www.postgresql.org/docs/9.1/static/datatype-datetime.html That way you can let the DB take care of any conversions when you need it, ie you can insert using Europe/Berlin and then select using UTC (etc etc). If you are just quickly writing your app for one timezone you can have that as the 'default' but then if you want to expand later then all your data is already in the most useful format. The only clean way of doing any of this is NOT to bundle an offset in with the time. Simply because that offset has EXACTLY the same problem as the simplistic time offset provided by the browser. Can we move that meeting to the same time next week can result in an hours error? Even the current timezone abbreviations are not totally consistent, only a clean timezone/daylight saving will give the real time around a change in daylight saving! UTC is a constant, and a proper TZ/DS for the client location completes the picture. Running the underlying server on anything other than UTC simply doubles the chance of problems if there are four differences between the server and client time zones, but what is NEEDED is that the client browser returns a client timezone rather than a simple offset! -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Holding "datetimes" in a DB.
On Fri, Mar 1, 2013 at 9:49 PM, Richard Quadling wrote: > Hi. > > My heads trying to remember something I may or may not have known to start > with. > > If I hold datetimes in a DB in UTC and can represent a date to a user > based upon a user preference Timezone (not an offset, but a real > timezone : Europe/Berlin, etc.) am I missing anything? Perhaps the "best" alternative is to store the data in the DB in a datatype that includes timezone, eg "timestamp with timezone" http://www.postgresql.org/docs/9.1/static/datatype-datetime.html That way you can let the DB take care of any conversions when you need it, ie you can insert using Europe/Berlin and then select using UTC (etc etc). If you are just quickly writing your app for one timezone you can have that as the 'default' but then if you want to expand later then all your data is already in the most useful format. Paul Paul -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Holding "datetimes" in a DB.
On Fri, Mar 1, 2013 at 7:04 PM, Sebastian Krebs wrote: > 2013/3/2 tamouse mailing lists > > > On Fri, Mar 1, 2013 at 11:53 AM, Matijn Woudt wrote: > > > On Fri, Mar 1, 2013 at 11:49 AM, Richard Quadling > >wrote: > > > > > >> Hi. > > >> > > >> My heads trying to remember something I may or may not have known to > > start > > >> with. > > >> > > >> If I hold datetimes in a DB in UTC and can represent a date to a user > > >> based upon a user preference Timezone (not an offset, but a real > > >> timezone : Europe/Berlin, etc.) am I missing anything? > > >> > > >> Richard. > > >> > > > > > > I would only use this if you're planning to have servers all around the > > > world in different timezones, then it would be easier to interchange > > data. > > > Otherwise, stick with ur local timezone and it will save you a lot of > > > unneeded timezone conversions probably. > > > > > > - Matijn > > > > This may be just me, but I've always preferred my servers, database, > > and such to run UTC, and let users select their own time zone they'd > > like to see. > > > > Well, imo it depends ;) There are cases, where it is interesting to know, > when from the users point of view they have created the entity (like a blog > post, a comment, or something like that). The TZ is part of the data and > converting it silently to UTC is always data-loss. So in most cases it is > OK, but not in every :) You can still convert from entity-TZ to UTC to > user-TZ later. Its just one additional step. > > Regards, > Sebastian > > > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > -- > github.com/KingCrunch > I have found that it's always best to stick to UTC ... regardless of anything else ... since saving the users local and going to and from can easily be wrapped into a single entry/exit function and I've had to many instances where it was saved as local and it came back to bite simply to save a few minutes. That said this is something I feel should be documented since for many newcomers they usually don't think about timezones until it becomes a problem ... Cheers! -- Nickolas Whiting - prggmr.org - Remember to write less code that does more faster -
Re: [PHP] Holding "datetimes" in a DB.
2013/3/2 tamouse mailing lists > On Fri, Mar 1, 2013 at 11:53 AM, Matijn Woudt wrote: > > On Fri, Mar 1, 2013 at 11:49 AM, Richard Quadling >wrote: > > > >> Hi. > >> > >> My heads trying to remember something I may or may not have known to > start > >> with. > >> > >> If I hold datetimes in a DB in UTC and can represent a date to a user > >> based upon a user preference Timezone (not an offset, but a real > >> timezone : Europe/Berlin, etc.) am I missing anything? > >> > >> Richard. > >> > > > > I would only use this if you're planning to have servers all around the > > world in different timezones, then it would be easier to interchange > data. > > Otherwise, stick with ur local timezone and it will save you a lot of > > unneeded timezone conversions probably. > > > > - Matijn > > This may be just me, but I've always preferred my servers, database, > and such to run UTC, and let users select their own time zone they'd > like to see. > Well, imo it depends ;) There are cases, where it is interesting to know, when from the users point of view they have created the entity (like a blog post, a comment, or something like that). The TZ is part of the data and converting it silently to UTC is always data-loss. So in most cases it is OK, but not in every :) You can still convert from entity-TZ to UTC to user-TZ later. Its just one additional step. Regards, Sebastian > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- github.com/KingCrunch
Re: [PHP] Holding "datetimes" in a DB.
On Fri, Mar 1, 2013 at 11:53 AM, Matijn Woudt wrote: > On Fri, Mar 1, 2013 at 11:49 AM, Richard Quadling wrote: > >> Hi. >> >> My heads trying to remember something I may or may not have known to start >> with. >> >> If I hold datetimes in a DB in UTC and can represent a date to a user >> based upon a user preference Timezone (not an offset, but a real >> timezone : Europe/Berlin, etc.) am I missing anything? >> >> Richard. >> > > I would only use this if you're planning to have servers all around the > world in different timezones, then it would be easier to interchange data. > Otherwise, stick with ur local timezone and it will save you a lot of > unneeded timezone conversions probably. > > - Matijn This may be just me, but I've always preferred my servers, database, and such to run UTC, and let users select their own time zone they'd like to see. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Holding "datetimes" in a DB.
On Fri, Mar 1, 2013 at 11:49 AM, Richard Quadling wrote: > Hi. > > My heads trying to remember something I may or may not have known to start > with. > > If I hold datetimes in a DB in UTC and can represent a date to a user > based upon a user preference Timezone (not an offset, but a real > timezone : Europe/Berlin, etc.) am I missing anything? > > Richard. > I would only use this if you're planning to have servers all around the world in different timezones, then it would be easier to interchange data. Otherwise, stick with ur local timezone and it will save you a lot of unneeded timezone conversions probably. - Matijn
Re: [PHP] Holding "datetimes" in a DB.
Richard Quadling wrote: My heads trying to remember something I may or may not have known to start with. If I hold datetimes in a DB in UTC and can represent a date to a user based upon a user preference Timezone (not an offset, but a real timezone : Europe/Berlin, etc.) am I missing anything? The simple answer is ... No Slightly more complex ... If you are displaying timed events in the future and need to support multiple time zones, then using the browser TZ setting is useless, you need to work with a daylight saving setting instead. The site 'default' can either be UTC or it can be a particular timezone, but what will not work reliably is to offset from the browser information. If all of your users are in the same timezone, then UTC without setting a timezone makes perfect sense and you even ignore even the daylight saving changes. Where the fun comes is where you are displaying times which straddle the daylight saving switches such as a train movement where the time you need to display jumps. In this case you have to use UTC for the movement, and display with the correct daylight saving offest. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Holding "datetimes" in a DB.
Hi, Richard I, too, tought about switching to UTC times in my database. The only reason for me was to get prepared for globalisation. A good article about this consideration is written at PHP Gangsta (sorry, German only): http://www.phpgangsta.de/die-lieben-zeitzonen The reason, why I did not switch to UTC, was the reason, that most of the pages I host have visitors mostly from Germany. If you have visitors from different countries, you first have to find out which time-zone they're in, which can be quite messy. Just watch this video about timezones (I added a jump-node that get's you directly to the interesting stuff): http://www.youtube.com/watch?feature=player_detailpage&v=84aWtseb2-4#t=230s But basically that doesn't matter that much, since you - anyways - don't get a time from the client, so you're stuck in this problem either way ... Each example, I can come up with, does apply to UTC saved timestamps as well as to non-UTC based timestamps. One consideration, if you only have to operate in one timezone, like I do, is stuff like cron-jobs. Since I only have Germany as main-visitor-source, I can safely configure the server to Europe/Berlin and pull out stuff like birthday-information at midnight. Please do also keep in mind here, that there are not only full-hour-timezones :) http://en.wikipedia.org/wiki/List_of_UTC_time_offsets But I can't come up with a problem that you wouldn't have with a local timezone instead of UTC ... Bye Simon On Fri, Mar 1, 2013 at 11:49 AM, Richard Quadling wrote: > Hi. > > My heads trying to remember something I may or may not have known to start > with. > > If I hold datetimes in a DB in UTC and can represent a date to a user > based upon a user preference Timezone (not an offset, but a real > timezone : Europe/Berlin, etc.) am I missing anything? > > Richard. > -- > Richard Quadling > Twitter : @RQuadling > EE : http://e-e.com/M_248814.html > Zend : http://bit.ly/9O8vFY > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
