On 01/20/2013 04:28 PM, Robert James wrote:
I'm confused. If I make sure to use UTC, isn't timestamp without time
zone identical, then? If not, what is the difference?
When you tag a date/time using WITH TIME ZONE you are telling Postgres
you care about time zones for that field:
http://www.postgresql.org/docs/9.2/interactive/datatype-datetime.html
"All timezone-aware dates and times are stored internally in UTC. They
are converted to local time in the zone specified by the TimeZone
configuration parameter before being displayed to the client"
So:
test=> \d timestamp_test
Table "public.timestamp_test"
Column | Type | Modifiers
---------+-----------------------------+-----------
id | integer | not null
txt_fld | text |
ts_fld | timestamp with time zone |
ts_fld2 | timestamp(0) with time zone |
ts_fld3 | timestamp without time zone |
test=> insert into timestamp_test(id, ts_fld, ts_fld3) values(20,
now(), now());
INSERT 0 1
test=> SELECT * from timestamp_test ;
id | txt_fld | ts_fld | ts_fld2 |
ts_fld3
----+---------+-------------------------------+---------+----------------------------
20 | | 2013-01-20 16:43:02.060805-08 | | 2013-01-20
16:43:02.060805
Note how in the time zone aware field you get an offset.
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
