[plug] Database time fields and DST

[Fred Janon]

I would check the database documentation about timestamps and
timezones. It is almost always better to store UTC times (GMT for the
Pommies :) ) in a database. There is rarely a need to store the
timezone of the user who created the record. Databases have time dates
functions to assist you in the proper transformation and calculations.
mySQL (check your DB) has timezone features. Also, if you are using a
browser to access your application, Javascript does a good job in
picking up the locale of the client machine and it also has time
functions to transform a UTC time into the timezone set on the client.

I do store my time/dates in UTC on the server and use Javascript on the
browser to display time/dates in the user's locale.

Like in Aviation, do all your calculations in UTC and convert to local
time when needed...

Fred

>From the mySQL 5.x documentation:
====================================================
11.3.1
To specify automatic default or updating for a TIMESTAMP column other
than the first one, you must suppress the automatic initialization and
update behaviors for the first TIMESTAMP column by explicitly assigning
it a constant DEFAULT value (for example, DEFAULT 0 or DEFAULT
'2003-01-01 00:00:00'). Then, for the other TIMESTAMP column, the rules
are the same as for the first TIMESTAMP column, except that you cannot
omit both of the DEFAULT and ON UPDATE clauses. If you do so, then no
automatic initialization or updating occurs. 

Example. These statements are equivalent: 

CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                  DEFAULT CURRENT_TIMESTAMP);

You can set the current time zone on a per-connection basis, as
described in Section 5.10.8, “MySQL Server Time Zone Support”.
TIMESTAMP values are stored in UTC, being converted from the current
time zone for storage, and converted back to the current time zone upon
retrieval. As long as the time zone setting remains constant, you get
back the same value you store. If you store a TIMESTAMP value, change
the time zone and then retrieve the value, it is different than the
value you stored. This occurs because the same time zone was not used
for conversion in both directions. The current time zone is available
as the value of the time_zone system variable. 
==============================================================
5.10.8. MySQL Server Time Zone Support
The MySQL server maintains several time zone settings: 

The system time zone. When the server starts, it attempts to determine
the time zone of the host machine and uses it to set the
system_time_zone system variable. 

The server's current time zone. The global time_zone system variable
indicates the time zone the server currently is operating in. The
initial value is 'SYSTEM', which indicates that the server time zone is
the same as the system time zone. The initial value can be specified
explicitly with the --default-time-zone=timezone option. If you have
the SUPER privilege, you can set the global value at runtime with this
statement: 

mysql> SET GLOBAL time_zone = timezone;

Per-connection time zones. Each client that connects has its own time
zone setting, given by the session time_zone variable. Initially this
is the same as the global time_zone variable, but can be reset with
this statement: 

mysql> SET time_zone = timezone;

The current values of the global and per-connection time zones can be
retrieved like this: 

mysql> SELECT @@global.time_zone, @@session.time_zone;

timezone values can be given as strings indicating an offset from UTC,
such as '+10:00' or '-6:00'. If the time zone-related tables in the
mysql database have been created and populated, you can also used named
time zones, such as 'Europe/Helsinki', 'US/Eastern', or 'MET'. The
value 'SYSTEM' indicates that the time zone should be the same as the
system time zone. Time zone names are not case sensitive. 
=================================================================

--- Brad Campbell <brad at wasp.net.au> wrote:

> G'day all,
> 
> 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.
> 
> I'm just fishing for input.. good, bad, ugly? Am I doing something
> dumb?
> I was kinda thinking of encoding it as (time<<1)+DST or similar to
> keep the format uniform in the 
> database while not adding extra columns or other silly conversions.
> 
> Brad
> -- 
> "Human beings, who are almost unique in having the ability
> to learn from the experience of others, are also remarkable
> for their apparent disinclination to do so." -- Douglas Adams
> _______________________________________________
> PLUG discussion list: plug at plug.org.au
> http://www.plug.org.au/mailman/listinfo/plug
> Committee e-mail: committee at plug.linux.org.au
>