Re: Date & Time with time zone

On Sun, Apr 09, 2006 at 12:07:50PM -0800, Wei Wei wrote:
> I try to understand how the D&T information is stored/presented in PG. In the application, the data is reported as
>
> Sun Apr 09 12:40:52 PDT 2006 - Pacific Standard Time
>
> But, in the DB, it is stated as
>
> 2006-04-09 14:40:53.093-07
>
> It doesn't seen right to me. Both are on the same box and the date column is with time zone.

Read the docs for the exact details but the gist of it is that
PostgreSQL always displays dates to you in your own timezone (or
whatever is configured with "set timezone" anyway).

timestamp with timezone represents an instant in time, which may appear
as different actual values depending on which timezone you're talking
about.

timestamp without timezone is a timestamp that appears the same to
everyone, all the time. The uses for this are not entirely clear, given
you can't represent the overlaps or gaps created by daylight savings
shifts. However, sometimes this is what you want.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.