Re: Timestamp with and without timezone conversion confusion.

>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.

As I mentioned in a separate reply, the best mental-model I've found for the ill-named "timestamp with time zone" is "point in time."

If you also need the location (or just the time zone) of an event I would recommend using two fields one of which is the event_timestamp as a timestamp with time zone (point in time) and the other is the event_timezone which is a text column with the full timezone name. You can get a full list of recognized time-zone names with "select * from pg_timezone_names".

I recommend storing the data as a timestamp with time zone and a full time-zone name to avoid data ambiguity during daylight saving changes. I.e. when the clock falls-back you will have 1:30 am twice if you are storing a timestamp without time zone. This *may* be disambiguated if you use an appropriate zone abbreviation like 1:30 EST vs 1:30 EDT but abbreviations lead to other problems in worldwide data including the problem that abbreviations may be reused leading to weirdness like needing to set the AUSTRALIAN_TIMEZONES parameter properly to avoid conflict with EST (Australian Eastern Standard Time) and EST (US Eastern Standard Time) among others - this will be even more "fun" if trying to select from a table that includes both Australian and United States data.

If you structure the data as recommended above you can simply get the local time as:

SELECT ..., event_timestamp at time zone event_timezone as event_local_time, ...

when you need the local time but you will still retain the exact point-in-time for use as needed.

Cheers,
Steve