Thread: Inserting / selecting rows with TIMESTAMP

Inserting / selecting rows with TIMESTAMP

From

Claire De Longchamp

Date:

04 February 2003, 17:19:55

Hello All,

I am new to PostgreSQL. For testing purposes I am copying data from an Oracle database to a PostgreSQL database via Access.
When I encountered an insert problem in Access, I tried it directly in PostgreSQL and here is the result.

I am very confused. Can anyone give me a hint?

Thanks,
Claire

P.S. I have encountered the problem with the following dates:
1998-04-05 02:00:00, 2000-04-02 02:00:00 and 2002-04-07 02:00:00 (it's a bit fishy, isn't it)
______________________________________________________________________________
Table "hre_gaz" Column | Type | Modifiers --------+--------------------------------+----------- poste | smallint | not null gaz | smallint | not null temps | timestamp(0) without time zone | not null valeur | integer | qual | integer | type | character(1) | Primary key: hre_gaz_pkey Triggers: RI_ConstraintTrigger_462203, RI_ConstraintTrigger_462209 RsqaDb=# insert into hre_gaz (poste,gaz,temps,valeur,qual,type) RsqaDb-# values (3,1,'1998-04-05 02:00:00'::timestamp,6,1,'S') ; ERROR: Cannot insert a duplicate key into unique index hre_gaz_pkey RsqaDb=# select * from hre_gaz RsqaDb-# where temps between '1998-04-05 02:00:00' and '1998-04-05 02:59:59' RsqaDb-# ; poste | gaz | temps | valeur | qual | type -------+-----+-------+--------+------+------ (0 rows) RsqaDb=# select * from hre_gaz where temps RsqaDb-# between '1998-04-05 02:00:00'::timestamp and '1998-04-05 02:59:59'::timestamp ; poste | gaz | temps | valeur | qual | type -------+-----+---------------------+--------+------+------ 3 | 1 | 1998-04-05 01:00:00 | 54 | 1 | S 3 | 3 | 1998-04-05 01:00:00 | 10 | 4 | S 3 | 4 | 1998-04-05 01:00:00 | 4 | 1 | S 3 | 8 | 1998-04-05 01:00:00 | 39 | 12 | S 3 | 9 | 1998-04-05 01:00:00 | 35 | 4 | S 3 | 10 | 1998-04-05 01:00:00 | 6 | | S 28 | 1 | 1998-04-05 01:00:00 | 8 | 1 | S 28 | 4 | 1998-04-05 01:00:00 | 5 | 2 | S 28 | 5 | 1998-04-05 01:00:00 | 13 | 18 | S 28 | 8 | 1998-04-05 01:00:00 | 6 | 2 | S 28 | 9 | 1998-04-05 01:00:00 | 62 | 7 | S 28 | 10 | 1998-04-05 01:00:00 | 47 | | S 61 | 1 | 1998-04-05 01:00:00 | 5 | 2 | S 61 | 4 | 1998-04-05 01:00:00 | 12 | 4 | S 61 | 8 | 1998-04-05 01:00:00 | 15 | 4 | S 61 | 9 | 1998-04-05 01:00:00 | 47 | 5 | S 61 | 10 | 1998-04-05 01:00:00 | 47 | | S 99 | 6 | 1998-04-05 01:00:00 | 2 | 8 | S 99 | 8 | 1998-04-05 01:00:00 | 29 | 9 | S 99 | 9 | 1998-04-05 01:00:00 | 18 | 2 | S 99 | 10 | 1998-04-05 01:00:00 | 1 | | S (21 rows) ______________________________________________________________________________

Re: Inserting / selecting rows with TIMESTAMP

From

Tom Lane

Date:

04 February 2003, 21:09:48

Claire De Longchamp <cdl@abdmf.com> writes:
>                    Table "hre_gaz"
>  Column |              Type              | Modifiers
> --------+--------------------------------+-----------
>  poste  | smallint                       | not null
>  gaz    | smallint                       | not null
>  temps  | timestamp(0) without time zone | not null
>  valeur | integer                        |
>  qual   | integer                        |
>  type   | character(1)                   |
> Primary key: hre_gaz_pkey
> Triggers: RI_ConstraintTrigger_462203,
>           RI_ConstraintTrigger_462209

> RsqaDb=# insert into hre_gaz (poste,gaz,temps,valeur,qual,type)
> RsqaDb-# values (3,1,'1998-04-05 02:00:00'::timestamp,6,1,'S') ;
> ERROR:  Cannot insert a duplicate key into unique index hre_gaz_pkey

What PG version is this?

If it's 7.2, then I think you're getting burnt by the fact that
::timestamp means coercion to timestamp *with* time zone.  When that
value is then coerced to timestamp without time zone, you can get funny
behavior right around the times of daylight-savings transitions.
In particular, I don't think '1998-04-05 02:00:00' is actually a valid
time, at least under US DST rules --- it comes out as '03:00:00' for me.

Either leave off the explicit coercion, or coerce to "timestamp without
time zone", or update to 7.3 in which "timestamp" means "timestamp without
time zone".

            regards, tom lane

Re: Inserting / selecting rows with TIMESTAMP

From

Claire De Longchamp

Date:

05 February 2003, 16:39:34

Thank you very much Tom. Your remark did clarify the question.
___________________________________________________________________
Tom Lane a écrit:

Claire De Longchamp <cdl@abdmf.com> writes:

                   Table "hre_gaz"Column |              Type              | Modifiers
--------+--------------------------------+-----------poste  | smallint                       | not nullgaz    | smallint                       | not nulltemps  | timestamp(0) without time zone | not nullvaleur | integer                        |qual   | integer                        |type   | character(1)                   |
Primary key: hre_gaz_pkey
Triggers: RI_ConstraintTrigger_462203,         RI_ConstraintTrigger_462209

RsqaDb=# insert into hre_gaz (poste,gaz,temps,valeur,qual,type)
RsqaDb-# values (3,1,'1998-04-05 02:00:00'::timestamp,6,1,'S') ;
ERROR:  Cannot insert a duplicate key into unique index hre_gaz_pkey

What PG version is this?

If it's 7.2, then I think you're getting burnt by the fact that 
::timestamp means coercion to timestamp *with* time zone.  When that
value is then coerced to timestamp without time zone, you can get funny
behavior right around the times of daylight-savings transitions.
In particular, I don't think '1998-04-05 02:00:00' is actually a valid
time, at least under US DST rules --- it comes out as '03:00:00' for me.

Either leave off the explicit coercion, or coerce to "timestamp without
time zone", or update to 7.3 in which "timestamp" means "timestamp without
time zone".
		regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)