[plug] Database time fields and DST

[Bernd Felsche]

Adrian Chadd <adrian at creative.net.au> writes:
>On Sun, Mar 25, 2007, Brad Campbell wrote:

>> I'm designing a database. Not a complex one, but a database none
>> the less.

>> I'm playing in a game where accuracy of timestamps is just a not
>> negotiable, so log searches are performed in local time and it's
>> pretty important that DST crossings (which may well be variable
>> as we've seen) are handled smoothly.

>> I'm planning on making all timestamps in the database GMT but
>> with an extra bit to signify if DST was active at the time, so in
>> theory all timestamps should translate to local time perfectly
>> including duplicated times when the clocks roll back.

>* Use UTC time; have the library of your choice handle converting the
>  current timezone + time into UTC time.
>* Then you don't have to worry about a DST bit at all.

But it's useful to keep the "user" timezone in the record
especially if access is possible from more than one location.
You should then always be able to reconstruct the user's wall-clock
time should the need arise; without delving through external log
files.
-- 
/"\ Bernd Felsche - Innovative Reckoning, Perth, Western Australia
\ /  ASCII ribbon campaign | "If we let things terrify us,
 X   against HTML mail     |  life will not be worth living."
/ \  and postings          | Lucius Annaeus Seneca, c. 4BC - 65AD.