Date with timezone format

From: "Emmanuel Guyot" <emmguyot(at)wanadoo(dot)fr>
To: pgsql-admin(at)postgresql(dot)org
Subject: Date with timezone format
Date: 2001-10-28 09:36:56
Message-ID: 9rgjkt$2mf8$1@news.tht.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I've notice the following behaviour, and would like some comments about the
way to interpret it.

I have the following table :
Table "pointage"
Attribute | Type | Modifier
-----------------+--------------------------+------------------------
cd_collab | numeric(3,0) | not null
dt_debut | timestamp with time zone | not null
dt_fin | timestamp with time zone |
cd_typ_pointage | numeric(2,0) | not null
comm | text |
dt_creat | timestamp with time zone | not null
dt_modif | timestamp with time zone | not null default now()
Index: pk_pointage

and the following configuration :

NOTICE: DateStyle is SQL with European conventions
NOTICE: Time zone is unknown

When I use (in psql) :
insert into POINTAGE (CD_COLLAB,DT_DEBUT,CD_TYP_POINTAGE,DT_CREAT,DT_MODIF)
values ( 1,'28/10/2001 09:26 GMT+01:00',1,'28/10/2001
09:26:16GMT+01:00','28/10/2001 09:26:16 GMT+01:00');
or
insert into POINTAGE (CD_COLLAB,DT_DEBUT,CD_TYP_POINTAGE,DT_CREAT,DT_MODIF)
values ( 1,'28/10/2001 09:26 GMT+01:00',1,'28/10/2001
09:26:16GMT+01:00','28/10/2001 09:26:16 GMT+01:00');

a select then returns :
cd_collab | dt_debut | dt_fin | cd_typ_pointage | comm |
dt_creat | dt_modif
-----------+----------------------------+--------+-----------------+------+-
---------------------------+----------------------------
1 | 28/10/2001 10:26:00.00 GMT | | 1 | |
28/10/2001 10:26:16.00 GMT | 28/10/2001 10:26:16.00 GMT

but when I use :
insert into POINTAGE (CD_COLLAB,DT_DEBUT,CD_TYP_POINTAGE,DT_CREAT,DT_MODIF)
values ( 1,'28/10/2001 09:26+01:00',1,'28/10/2001
09:26:16+01:00','28/10/2001 09:26:16+01:00');

a select returns the following results which is right :
cd_collab | dt_debut | dt_fin | cd_typ_pointage | comm |
dt_creat | dt_modif
-----------+----------------------------+--------+-----------------+------+-
---------------------------+----------------------------
1 | 28/10/2001 08:26:00.00 GMT | | 1 | |
28/1
0/2001 08:26:16.00 GMT | 28/10/2001 08:26:16.00 GMT

So it seems that the former syntax is not right and is interpreted as 9:26
with GMT TimeZone, One hour is then added to the date. Is this right ??

If it is, why, with SQL datestyle, is it a bad syntax ?

Emmanuel Guyot

Browse pgsql-admin by date

  From Date Subject
Next Message Marko Kreen 2001-10-28 09:50:42 Re: UNICODE
Previous Message Jean-Michel POURE 2001-10-28 08:22:24 UNICODE