Thread: Inserting timestamp values

Hi!

Does my test result comply with the statements in section
"8.5 Date/Time Types"? My shell set local time to UTC+8
hours. I was in the impression that the query

insert into test values ('2003-2-1'::timestamp);

equals to

insert into test values ('2003-2-1'::timestamp without time
zone);

but my test results seem to show the other way.

db1=# show time zone;
 TimeZone
----------
 unknown
(1 row)

db1=# \d test
              Table "public.test"
 Column |           Type           | Modifiers
--------+--------------------------+-----------
 f1     | timestamp with time zone |

db1=# insert into test values ('2003-1-1'::timestamp);
INSERT 46230 1
db1=# select * from test;
           f1
------------------------
 2003-01-01 00:00:00+08
(1 row)

db1=# set time zone 9;
SET
db1=# insert into test values ('2003-2-1'::timestamp);
INSERT 46231 1
db1=# select * from test;
           f1
------------------------
 2003-01-01 01:00:00+09
 2003-02-01 00:00:00+09
(2 rows)

db1=# set time zone 0;
SET
db1=# select * from test;
           f1
------------------------
 2002-12-31 16:00:00+00
 2003-01-31 15:00:00+00
(2 rows)

Regards,

CN

"cnliou" <cnliou@so-net.net.tw> writes:
> Does my test result comply with the statements in section
> "8.5 Date/Time Types"?

I think so.  You are inserting into a timestamp-with-time-zone column,
so even though you mark the literal constant as timestamp without time
zone, it's going to be converted to timestamp with time zone.

            regards, tom lane

Tom Lane kirjutas T, 02.12.2003 kell 18:40:
> "cnliou" <cnliou@so-net.net.tw> writes:
> > Does my test result comply with the statements in section
> > "8.5 Date/Time Types"?
>
> I think so.  You are inserting into a timestamp-with-time-zone column,
> so even though you mark the literal constant as timestamp without time
> zone, it's going to be converted to timestamp with time zone.

Maybe he is wondering about it getting converted to +8 time zone even
when the time zone is 'unknown' :

> but my test results seem to show the other way.
>
> db1=# show time zone;
>  TimeZone
> ----------
>  unknown
> (1 row)
>
> db1=# \d test
>               Table "public.test"
>  Column |           Type           | Modifiers
> --------+--------------------------+-----------
>  f1     | timestamp with time zone |
>
> db1=# insert into test values ('2003-1-1'::timestamp);
> INSERT 46230 1
> db1=# select * from test;
>            f1
> ------------------------
>  2003-01-01 00:00:00+08