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

by

The question:

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 Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

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:

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

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:

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 INOUT parameter for simplicity.

About EXISTS:

I also replaced your mixed-case identifiers. See:

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:

You can just add integer to a date.

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.

Find available time zone names in pg_catalog.pg_timezone_names.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment