Re: Understanding TIMESTAMP WITH TIME ZONE

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(dot)klaver(at)gmail(dot)com