Postgres Pro
Downloads
Home   >   mailing lists

Re: Timestamp with and without timezone conversion confusion. - Mailing list pgsql-general

From Tim Uckun
Subject Re: Timestamp with and without timezone conversion confusion.
Date
Msg-id CAGuHJrMaLmVrj9Qkkt5ATujg=HCXhmmJgerZBsTsQ5Vxwt2gTg@mail.gmail.com
Whole thread Raw
In response to Re: Timestamp with and without timezone conversion confusion.  (Tony Theodore <tony.theodore@gmail.com>)
List pgsql-general
Tree view
That's interesting article but it tells me that I can't really use the timestamp with time zone data type.   I really need to store the time zone information along with the datetime and do not want to automatically convert the timestamp to the connection time zone.

If one row has a timestamp in UTC and another has a timestamp in EST I want the user (all users) to view the timestamps "as is" with the offset so they can see how it may differ from each other and their own time zone.

I guess I have to store the time zones separately in another field.


On Wed, Oct 2, 2013 at 10:05 PM, Tony Theodore <tony.theodore@gmail.com> wrote:

On 02/10/2013, at 6:49 PM, Tim Uckun <timuckun@gmail.com> wrote:

>The reason for that is that in PostgreSQL there is no time zone
information stored along with a "timestamp with time zone",
it is stored in UTC.

That seems unintuitive. What is the difference between timestamp without time zone and timestamp with time zone? I was expecting to have the time zone stored in the field. For example one row might be in UTC  but the other row might be in my local time.

Maybe the question I need to ask is "how can I store the time zone along with the timestamp"   

>That is because AT TIME ZONE returns a "timestamp without time zone"

Also seems counterintutive but I guess I can aways convert it. I am just not getting the right offset when I convert. That's what's puzzling.


Here's a handy blog post from Josh Berkus about timestamps:


Cheers,

Tony


pgsql-general by date:

Previous
From: Tony Theodore
Date:
Subject: Re: Timestamp with and without timezone conversion confusion.
Next
From: Albe Laurenz
Date:
Subject: Re: Timestamp with and without timezone conversion confusion.