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

From Claire De Longchamp
Subject Inserting / selecting rows with TIMESTAMP
Date
Msg-id 3E4012EA.3000103@abdmf.com
Whole thread Raw
Responses Re: Inserting / selecting rows with TIMESTAMP
List pgsql-novice
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)

______________________________________________________________________________

pgsql-novice by date:

Previous
From: Jon V
Date:
Subject: Re: Cygwin postmaster.pid solution?
Next
From: Petre Scheie
Date:
Subject: Re: PL/Perl on HPUX