[DOCS] Inserting timestamp values

2003-12-02 Thread cnliou 
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

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [DOCS] Inserting timestamp values

2003-12-02 Thread Tom Lane 
"cnliou" <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [DOCS] Inserting timestamp values

2003-12-02 Thread Hannu Krosing 
Tom Lane kirjutas T, 02.12.2003 kell 18:40:
> "cnliou" <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly