Re: Timestamp with and without timezone conversion confusion.

On 10/02/2013 01:49 AM, Tim Uckun 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.
>
>
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