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