Re: Timestamp with and without timezone conversion confusion.

Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> · Wed, 02 Oct 2013 10:54:51 -0700



    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