[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sr-dev
Subject:    Re: [Serdev] Timezones in PostgreSQL driver in 2.0
From:       Maxim Sobolev <sobomax () sippysoft ! com>
Date:       2008-09-30 19:49:43
Message-ID: 48E282D7.2010300 () sippysoft ! com
[Download RAW message or body]

Jan Janak wrote:
> Hi Maxim,
> 
> On 30-09 01:11, Maxim Sobolev wrote:
>> Hi,
>>
>> Not sure what exactly has changed since 0.9.x, but it appears that 
>> postgresql module in 2.0 does funny things with timezones. From what I 
>> see here it writes time in UTC, but makes the server believe it's local 
>> time, so that the server messes it up, which makes proper integration 
>> with external software difficult. I found that setting PGTZ to UTC in 
>> startup script works fine, not sure if we should set it automatically 
>> within the portgresql driver itself.
>>
>> Any comments/ideas?
> 
>   What has changed is the way how varius data type conversions are done
>   in the postgres driver in 2.0. My intention was to store all date-time
>   related information in UTC in the database. Could you let me know what

Well, if you store everything in UTC, which makes sense, you need to let 
server know that you are doing so. Otherwise it assumes that everything 
is in the local TZ and applies timezone conversion for you before 
storing the data.

>   version of postgresql are you using? Also more information, such as
>   examples, and columnt types being used would help.

I have seen it with PostgreSQL 8.2. The datatype is either TIMESTAMP or 
TIMESTAMP WITH TIME ZONE. It's location.expires field I am having the 
problem with. Following are the results I am getting by querying this 
field via psql command-line tool (SER has been restarted between attempts):

1. Type TIMESTAMP WITH TIME ZONE. PGTZ is not set. System TZ is PDT.

sippy=# select now(), expires from location;
               now              |        expires
-------------------------------+------------------------
  2008-09-30 10:36:48.860252-09 | 2008-09-30 19:40:29-09
(1 row)

2. Type TIMESTAMP. PGTZ is not set. System TZ is PDT.

sippy=# select now(), expires from location;
               now              |       expires
-------------------------------+---------------------
  2008-09-30 10:38:28.283053-09 | 2008-09-30 19:43:26
(1 row)

3. Type TIMESTAMP. PGTZ=UTC. System TZ is PDT.

sippy=# select now(), expires from location;
               now              |       expires
-------------------------------+---------------------
  2008-09-30 10:41:30.671116-09 | 2008-09-30 19:46:23
(1 row)

4. Type TIMESTAMP WITH TIME ZONE. PGTZ=UTC. System TZ is PDT.

sippy=# select now(), expires from location;
               now              |        expires
-------------------------------+------------------------
  2008-09-30 10:44:21.868843-09 | 2008-09-30 10:49:20-09
(1 row)

As you can see the only correct answer is the case of (4).

Hopefully it helps. Please let me know if you want me to try anything else.

Regards,
-- 
Maksym Sobolyev
Sippy Software, Inc.
Internet Telephony (VoIP) Experts
T/F: +1-646-651-1110
Web: http://www.sippysoft.com

[prev in list] [next in list] [prev in thread] [next in thread]