I have a question, or maybe it's a feature request. Or maybe it's a question
and a feature request ;)
We would very much like to be able to store time on the server in UTC. The
TIMESTAMP type seems close, but has baggage due to its desire to update
itself as a timestamp on updates.
MySQL handling of dates seems to work poorly for GMT/UTC dates. Let me make
the following summary: (and please correct me if I'm wrong ;)
(1) DATETIME columns are stored in a fixed format (BCD of some sort?)
that is unrelated to the time zone TZ of the server. If I store
a date/time, then change the time zone of the server, then fetch
the field, it will remain unchanged. This is good.
(2) But all MySQL interpretation of such dates is relative to the
server's current time zone. So if I ask for a UNIX_TIMESTAMP of
such a DATETIME, (or even worse maybe, try to use any of the very
very nice looking MySQL date/time functions, the DATETIME field
will be interpreted as if it were expressed in server local time.
This is bad.
(3) TIMESTAMP columns store the time as a true unix epoch time, but
are hamstrung by the timestamping feature when used for regular
date/times.
This leaves us with the following set of seemingly awkward choices:
(a) Offset our DATETIME fields into GMT/UTC time before setting
them on the server.
Advantages:
- If the server, or the database, is moved across timezones
the data won't change.
- Clients of the database don't need to know the timezone
of the MySQL server; they just have to know their own
time zone.
Disadvantages:
- None of (very few of?) the build-in MySQL date/time
functions will work, as they all assume that the datetimes
are in server local time.
(b) Offset times to server local time before storing or, better yet,
let the server do this with From_UnixTime.
Advantages:
- The MySQL date/time functions will work okay
- Use of Unix_TimeStamp() and From_UnixTime() functions
make date/time storage and retrieval fairly easy.
Disadvantages:
- Since times are stored on disk in local time, the database
(or server) may not be moved or transported across date
lines without munging the dates.
- It is likely that clients of the database will need to know
what time-zone it is in.
(c) Always use TIMESTAMP columns.
Advantages:
- The MySQL date/time functions will work (???)
- The database and server may be moved across time zones
without affected the stored dates.
Disadvantages:
- The timestamping features makes such columns very awkward
to use for "normal" date/time storage, as extreme care
must be taken to ensure that the column is properly reset
on each update.
We have chosen choice (a) currently as the best of the three choices, in
part because the the built-in date/time functions don't provide much benefit
since we have to accommodate users in multiple timezones anyway, and these
routines all really only work well if a user is within the server's time
zone.
My Question:
- Is there something we've missed here to make our lives easier? Is
there a better/easier choice that we've missed?
My Feature Request:
- I would ask for a rich set of date/time functions that manipulate a
date stored in a canonical GMT/UTC form that will survive transport across
time zones.
- Such routines (where they place an human readable interpretation or
representation on a date) should take a timezone or locale parameter of some
sort in order that the time can be interpreted according to a given set of
local conventions.
James.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
