Re: Timestamp with and without timezone conversion confusion.

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(dot)theodore(at)gmail(dot)com>wrote:

>
> On 02/10/2013, at 6:49 PM, Tim Uckun <timuckun(at)gmail(dot)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:
>
> http://it.toolbox.com/blogs/database-soup/zone-of-misunderstanding-48608
>
> Cheers,
>
> Tony
>
>