[aclug-L] Re: mysql, timestamps and timezones (oh my)

[Jeff Vian <jvian10@xxxxxxxxxxx>]

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?  
>
>thanks,
>jesse
>
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.

First
    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.

Second
    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,
visit http://www.complete.org/cgi-bin/listargate-aclug.cgi