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
