Re: 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