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' :
Show quoted text
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