[Pypgsql-users] Timestamp with and without time zone...

[Andrew M. <an...@ob...>]

Bill recently checked in this change:

date: 2005/05/16 03:37:56;  author: ballie01;  state: Exp;  lines: +23 -4
15MAY2005 bga
    A change to the datetime parsing changed the default datetime object
    from localtime to UTC.  This update returns the default to localtime
    and adds a new variable, 'useUTCtimeValue' to control the default
    value of a datetime object.  Setting it to 1 will cause the datetime
    object to reference the UTC time, 0 will cause the datetime object to
    reference the client's local time zone.

The results are not idempotent - a DateTime saved to the database comes
back adjusted by the current timezone offset. Depending on the setting
of useUTCtimeValue, either PG_TIMESTAMP or PG_TIMESTAMPTZ is wrong. If
an application used only one of these types, useUTCtimeValue could be
set appropriate, but if the application uses both, it's in trouble.

I also note that there are other places where DateTimeFromString is used
where useUTCtimeValue is not honoured (handleArray).

Operating on timezones is like waltzing through a minefield - I'm not
sure what to suggest to fix this, but I think the PG_TIMESTAMP type
("without time zone") should make the round-trip to postgres and back
unmollested by default.

    Welcome to psql 7.4.7, the PostgreSQL interactive terminal.
    test=# create table x (y timestamp without time zone, z timestamp with time zone);
    CREATE TABLE
    test=# insert into x values (now(), now());
    INSERT 10353883 1
    test=# select * from x;
                 y              |               z               
    ----------------------------+-------------------------------
     2005-05-23 16:10:51.451112 | 2005-05-23 16:10:51.451112+10
    (1 row)

    1$ python2.4
    Python 2.4.1 (#2, May  5 2005, 11:32:06) 
    [GCC 3.3.5 (Debian 1:3.3.5-12)] on linux2
    >>> from pyPgSQL import PgSQL
    >>> db=PgSQL.connect(database="test")
    >>> c=db.cursor()
    >>> c.execute('select * from x')
    >>> c.fetchone()
    [<DateTime object for '2005-05-24 02:10:51.45' at b7e7f838>, <DateTime object for '2005-05-23 16:10:51.45' at b7e873d8>]

-- 
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/