pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedInserting timestamp values
3+ messages / 3 participants
[nested] [flat]
* Inserting timestamp values
@ 2003-12-02 16:24 cnliou <cnliou@so-net.net.tw>
0 siblings, 1 reply; 3+ messages in thread
From: cnliou @ 2003-12-02 16:24 UTC (permalink / raw)
To: pgsql-docs
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Inserting timestamp values
@ 2003-12-02 16:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: cnliou <cnliou@so-net.net.tw>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2003-12-02 16:40 UTC (permalink / raw)
To: cnliou@so-net.net.tw; +Cc: pgsql-docs
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Inserting timestamp values
@ 2003-12-02 20:32 Hannu Krosing <hannu@tm.ee>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Hannu Krosing @ 2003-12-02 20:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: cnliou@so-net.net.tw; pgsql-docs
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-12-02 20:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-02 16:24 Inserting timestamp values cnliou <cnliou@so-net.net.tw>
2003-12-02 16:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-02 20:32 ` Hannu Krosing <hannu@tm.ee>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox