[aclug-L] Re: mysql, timestamps and timezones (oh my)
[Top] [All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index] [Thread Index]
Jesse Kaufman wrote:
>question for any of you that are good with databases and understand
>#$^%#$% timezone stuff ...
>working on an app that tracks hours worked (and much, much more) and am
>needing to add timezone support ... when storing events, i have a table
>that is (basically) like this:
>| eventLogID [PK] |
>| usersID |
>| timeIn |
>| timeOut |
>now, what would be the best storage method for timeIn/timeOut? i have
>been using the mysql DATETIME data type ('YmdHis'), but i'm nervous
>about what happens when you have employees in a timezone different than
>the one the server is in ... (ie: we have 2 employees in florida, but
>the server is here in KS) ... suggestions?
Two choices really.
In either choice, a field needs to be added to indicate the timezone
used at the location where the query is made.
Store the time as local to the server.
In this case all processes using that data should make adjustments
to the location where service is provided, and need to know offsets from
local time to the service location.
This can be a bit confusing in cases where the system has to make
changes to a different time zone, and time is not always used/displayed
the same dependent upon where the user is located.
Store the time as UCT.
In this case all processes need to make adjustments to the location
where the query is being performed, whether data storage or retreival.
The adjustment is from local time to UCT and back.
I like the second choice better, since the data is always stored the
same, and it always appears the same on the output for a given location.
There is no opportunity for a misconfiguration causing confusion since
the difference between local time and UCT is always known and a single
adjustment is always made for /all/ data manipulation.
Also, UCT is not affected by DST changes, but local time can be, and by
storing it UCT then the time is always consistent regardless of the
location or time of year making the query. The only item that changes
the time on the output of the application is the time zone where of the
location where the query is being made.
-- This is the discussion@xxxxxxxxx list. To unsubscribe,