Time zone lost when returning timestamp with time zone from a function



  • I have this function:

    CREATE OR REPLACE FUNCTION laborable_day(dtDateTime TIMESTAMP WITH TIME ZONE) 
    RETURNS TIMESTAMP WITH TIME ZONE AS $$
    DECLARE
      _isHoliday BOOLEAN;
    BEGIN
      LOOP
        SELECT COUNT(*) > 0 INTO _isHoliday 
        FROM holidays 
        WHERE holiday = DATE(dtDateTime);
    
    IF _isHoliday THEN
      dtDateTime := dtDateTime + INTERVAL '1 DAY';
    ELSE
      EXIT;
    END IF;
    

    END LOOP;
    RETURN dtDateTime;
    END;
    $$ LANGUAGE plpgsql STRICT SECURITY DEFINER;

    If I run this command:

    select laborable_day('2022-01-01 18:53:11.14297-05'::TIMESTAMPTZ);
    

    I get:

    +------------------------------+
    | laborable_day                |
    |------------------------------|
    | 2022-01-02 23:53:11.14297+00 |
    +------------------------------+
    SELECT 1
    Time: 0.012s
    

    Why is the time zone info lost?



  • The data type timestamp with time zone (timestamptz) does not store any time zone information. That's a common misconception, inspired by the misleading name. (Blame the SQL standards committee!) You are not the first to fall for this:

    • https://stackoverflow.com/a/28876266/939860

    The time offset is just an input modifier / output decorator. Postgres always stores UTC time internally. Basics here:

    • https://stackoverflow.com/a/9576170/939860

    Consider this demo:

    test=> SET timezone = 'UTC';
    SET
    test=> SELECT timestamptz '2022-01-01 18:53:11.14297-05';
             timestamptz          
    ------------------------------
     2022-01-01 23:53:11.14297+00
    (1 row)
    

    test=> SET timezone = 'America/New_York';
    SET

    test=> SELECT timestamptz '2022-01-01 18:53:11.14297-05';
    timestamptz

    2022-01-01 18:53:11.14297-05

    test=> SELECT timestamptz '2022-03-21 18:53:11.14297-05';
    timestamptz

    2022-03-21 19:53:11.14297-04
    (1 row)

    Note also how DST changes for the last call. See:

    • https://dba.stackexchange.com/questions/160065/oddities-with-at-time-zone-and-utc-offsets/160083#160083

    This also goes to show why your current function is inherently unreliable. A plain cast from timestamptz to date assumes the current time zone setting of your session. A date is not clearly defined without also giving the timezone it applies to. If you are fine with the fuzzy definition, consider this improved (but still naive) function:

    CREATE OR REPLACE FUNCTION laborable_day(INOUT _dt timestamptz)
      LANGUAGE plpgsql STRICT AS  -- why SECURITY DEFINER ?
    $func$
    BEGIN
       LOOP
          IF EXISTS (
             SELECT FROM holidays
             WHERE  holiday = _dt::date  -- depends on current time zone! 
             ) THEN
             _dt := _dt + interval '1 day';
          ELSE
             EXIT;
          END IF;
      END LOOP;
    END
    $func$;
    

    I removed SECURITY DEFINER. Only use this when necessary, as it's potentially dangerous. Instead, grant SELECT for your holidays table to PUBLIC.

    Using an https://stackoverflow.com/a/8169928/939860 .

    About EXISTS:

    • https://stackoverflow.com/a/11892796/939860

    I also replaced your mixed-case identifiers. See:

    • https://stackoverflow.com/a/20880247/939860

    Deterministic function

    To get deterministic results, also define the time zone. Like

    CREATE OR REPLACE FUNCTION laborable_day(INOUT _dt timestamptz, _tz text DEFAULT 'UTC')  -- default UTC?
      LANGUAGE plpgsql STRICT AS  -- why SECURITY DEFINER ?
    $func$
    DECLARE
       _day date := (_dt AT TIME ZONE _tz)::date;
       _inc int := 0;
    BEGIN
       LOOP
          IF EXISTS (
             SELECT FROM holidays
             WHERE  holiday = _day + _inc
             ) THEN
             _inc := _inc + 1;
          ELSE
             EXIT;
          END IF;
      END LOOP;
    

    _dt := _dt + interval '1 day' * _inc;
    END
    $func$;

    I added 'UTC' as DEFAULT for the second parameter. Adapt to your needs. About parameter defaults:

    • https://stackoverflow.com/a/11604187/939860

    You can just https://stackoverflow.com/a/68742129/939860 .

    I'd suggest not to "overload" the function (create variants with different function parameters), that can get tricky.

    This way you can still call the function giving just a timestamptz:

    SELECT laborable_day('2022-01-01 18:53:11.14297-05');
    

    To get results for a given timezone, i.e. 'Europe/Vienna':

    SELECT laborable_day('2022-01-01 18:53:11.14297-05', 'Europe/Vienna');
    

    Use time zone names, not abbreviations or numerical offsets, to make it work properly with DST and other oddities.

    • https://dba.stackexchange.com/questions/160065/oddities-with-at-time-zone-and-utc-offsets/160083#160083

    Find available time zone names in https://www.postgresql.org/docs/current/view-pg-timezone-names.html .




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2