Adjust time display based on timezone

[dmorton]

Chris Paul write:

It makes a lot of sense I think to have a time zone configuration setting
under Settings. This would specify an offset from the mail servers timezone which 
users could set to have their quarantine display mail items with the local time. It 
could be per-domain and inherited down to the user. It would probably also be a good 
idea to recommend that Maia servers (mail servers, web servers, etc) be set to UTC, 
since the standard and most logical way to offset local time is from UTC.

As noted in email,

The real question I have is one of performance: when do we make the adjustment? If we make it before being stored in the database, it might have ramifications for other processes. If we make the adjustment on every view, it could slow down the view. OTOH, it is stored in a datetime in sql, so it should be a very simple sql adjustment.

It looks like mysql has support:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz

And it appears, postgresql:

http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

This would require a user setting for their own timezone, so it is slated for maia 1.1 at the earliest.

[rjl]

Ultimately I chose a much simpler implementation for [1231]. Rather than figuring out time zones and daylight savings time details, we just compare the system clocks on the server and the client to determine the offset (in seconds) between them, and then add that offset to the received_date timestamp in the SELECT statement used to produce the cache lists. No user settings are required--it's completely transparent. The offset is determined automatically during the login process and stored as a session variable.

[dmorton]

retargetting for 1.0.3

[rjl]

Two small additions:

In [1261] a small typo is corrected in the PostgreSQL code;

In [1286] we no longer make any assumptions about whether the time stored in the system clocks is local or UTC. Instead we convert both times to UTC before determining the offset between the server time and the client time. This is necessary for systems which store UTC on their system clocks but use the operating system to track time zone offsets separately.

[rjl@…]

In [1297] we correct the subtraction order for the offset, such that it's server - client, rather than client - server.