Re: UK BST Time Settings

Adam Tauno Williams wrote:
> On Mon, 2009-04-06 at 19:33 +0100, Ken Smith wrote:
>   
>> Adam Tauno Williams wrote:
>> {Snippage}
>> Thank you for this. Its very confusing.
>> I've looked at the trace created by enabling logging and its looking to 
>> me as if the database actually has entries that are an hour ahead of 
>> what they should be.
>> My SQL is very basic. Could someone please suggest an sql statement that 
>> will subtract an hour from every entry.
>>     
>
> I recently used SQLs like:
>
> UPDATE date_x
>   SET start_date = start_date - '1 HOUR'::INTERVAL,
>       object_version = object_version + 1
> WHERE (start_date BETWEEN '2009-03-11' AND '2009-03-31') 
>  AND (date_part('Hour', start_date) -5) != 0;
>
> UPDATE date_x
>   SET end_date = end_date - '1 HOUR'::INTERVAL,
>       object_version = object_version + 1
> WHERE (end_date BETWEEN '2009-03-11' AND '2009-03-31') 
>   AND (date_part('Hour', end_date) -5) != 0;
>
>  - to fix a database where the timezone/DST was incorrect.
>
> BACKUP YOUR DATA BEFORE MAKING MANUAL DATABASE CHANGES!  
>
> If you have one-hour-off appointments in the DST revision range
> [2009-03-11' AND '2009-03-31'] created via ZideLook I'd strongly suspect
> that your PC/Workstation has outdated time zone tables (i.e. Outlook
> made the error).
>
>   
Hi All,

Here is what my investigations have found:

SUMMARY OF SITUATION

The configuration is OGo accessed via browser and via Outlook/Zidelook. 
After the change from GMT to BST in the UK appointments as viewed in 
Outlook appear one hour late.

DATA

The server is FC6, operating in London/Europe time zone. OGo has GMT as 
its time zone in the Defaults.

Content of the database

Here is an extract from the database for a repeating appointment

OGo=> select date_id, start_date, start_date AT TIME ZONE 'GMT', 
end_date, end_date AT TIME ZONE 'GMT' from date_x where title = 'Ken in 
office' order by start_date;
 date_id |       start_date       |      timezone       |        
end_date        |      timezone
---------+------------------------+---------------------+------------------------+---------------------
   78992 | 2008-11-13 10:30:00+00 | 2008-11-13 10:30:00 | 2008-11-13 
12:30:00+00 | 2008-11-13 12:30:00

{snip}

   79106 | 2009-03-26 10:30:00+00 | 2009-03-26 10:30:00 | 2009-03-26 
12:30:00+00 | 2009-03-26 12:30:00
   79112 | 2009-04-02 11:30:00+01 | 2009-04-02 10:30:00 | 2009-04-02 
13:30:00+01 | 2009-04-02 12:30:00

{snip}

   79286 | 2009-10-22 11:30:00+01 | 2009-10-22 10:30:00 | 2009-10-22 
13:30:00+01 | 2009-10-22 12:30:00
   79292 | 2009-10-29 10:30:00+00 | 2009-10-29 10:30:00 | 2009-10-29 
12:30:00+00 | 2009-10-29 12:30:00
 

In the OGo web interface, all of these show as starting at 10:30

In Sunbird (I don't have a Zidelook licence in my test setup) these 
appointments show as follows:-

2008-11-13 10:30
2009-03-26 10:30
2009-04-02 11:30
2009-10-22 11:30
2009-10-29 10:30

MY ANALYSIS

It would appear that Postgres is correctly taking account of the 
machines own time zone.

The data offered to Outlook/Zidelook and Sunbird appears to correctly 
reflect the content of the database as viewed in the UK/London timezone. 
BUT, the database entries (Created by OGO's web interface) are incorrect 
during the BST part of the year. The data in the BST part of the year 
should be an hour earlier.

The data in /usr/local/share/libFoundation/TimeZoneInfo/GMT has a 
constant time offset (0:00) all through the year. The OGo web interface 
is using this. This would work acceptably if no-one in another time zone 
used the WebUI or Outlook/Zidelook or Sunbird...

There is an entry /usr/local/share/libFoundation/TimeZoneInfo/GB-Eire 
which appears to have good data in it, but 
/usr/local/share/libFoundation/RegionsDictionary does not have an entry 
that invokes that data.


POSSIBLE SOLUTIONS

(1) Correct the data in the database by subtracting an hour from the 
entries for the BST part of the year. Manually set OGo webui to GMT+1 
during the BST part of the year.

(2) Correct the data as in (1) and create a new time zone in the OGo 
WebUI that follows the GMT/BST switch in the UK.

EXPERIMENT

It seems wrong that we in the UK call the time in one part of our year 
GMT and the other BST. Whereas, for example, EST is the name for the 
whole of the year in the US eastern zone.

Taking suggestions from 
http://bugzilla.opengroupware.org/bugzilla/show_bug.cgi?id=1978, I have 
added a time zone called GBT to libFoundation that invokes the GB-Eire 
data and I have changed the Defaults in OGo on my test system to use this.

I notice in 
http://bugzilla.opengroupware.org/bugzilla/show_bug.cgi?id=1270 what 
seems to be a Default called ListTimeZones is mentioned. Can this be 
used to configure the time zones that appear in the WebUI? I don't see 
that parameter on my system or in WMOGAG. Is this a potential solution 
that would make the timezone GBT appear as a choice in the WebUI and 
allow solution (2) to be deployed or is there a better way to do this?

Many thanks

Ken


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

-- 
OpenGroupware.org Users
[email protected]
http://mail.opengroupware.org/mailman/listinfo/users