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