Re: Inserting / selecting rows with TIMESTAMP - Mailing list pgsql-novice

From Claire De Longchamp
Subject Re: Inserting / selecting rows with TIMESTAMP
Date
Msg-id 3E415AE1.5020404@abdmf.com
Whole thread Raw
In response to Inserting / selecting rows with TIMESTAMP  (Claire De Longchamp <cdl@abdmf.com>)
List pgsql-novice
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)

 

pgsql-novice by date:

Previous
From: Dinesh Visweswaraiah
Date:
Subject: Re: SQL Intersect like problem
Next
From: "Scott Morrison"
Date:
Subject: How can I speed up with query?