Generated column with data type timestamptz (timestamp with time zone)

In PostgreSQL, I want to make a computed column, where end_datetime = start_datetime + minute_duration adding an interval to a timestamp.

I keep getting error, how can I fix?

ERROR: generation expression is not immutable SQL state: 42P17

Tried two options below:

CREATE TABLE appt ( 
  appt_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  minute_duration INTEGER NOT NULL,
  start_datetime TIMESTAMPTZ NOT NULL,
  end_datetime TIMESTAMPTZ GENERATED ALWAYS AS (start_datetime + (minute_duration || ' minutes')::INTERVAL) STORED
 );


 CREATE TABLE appt ( 
  appt_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  minute_duration INTEGER NOT NULL,
  start_datetime TIMESTAMPTZ NOT NULL,
  end_datetime TIMESTAMPTZ GENERATED ALWAYS AS (start_datetime + make_interval(mins => minute_duration)) STORED
);

The only other option (I can think of) would be trigger, but trying to refrain trigger method for now. If trigger is the only answer, feel free to give trigger solution.

DST is the problem

The underlying issue is the moronic concept of daylight saving time (DST) - which should be removed from the surface of the earth and never be spoken of again.

Due to the existence of DST, operations on timestamptz have to take the TimeZone setting of the current session into account and cannot therefore be IMMUTABLE.

Simpler solution with type timestamp

The same would work with just timetstamp [without time zone]:

CREATE TABLE appt1 (
  appt_id         uuid PRIMARY KEY DEFAULT gen_random_uuid()
, minute_duration integer NOT NULL
, start_datetime  timestamp NOT NULL  -- timestamp!
, end_datetime    timestamp GENERATED ALWAYS AS (start_datetime + make_interval(mins => minute_duration)) STORED
);

Clean workaround for type timestamptz

If you know the time zone the operation should assume beforehand cast to timestamp (with a given time zone!) do your math, and cast back - thereby removing the dependence on session-local settings:

CREATE TABLE appt2 (
  appt_id         uuid PRIMARY KEY DEFAULT gen_random_uuid()
, minute_duration integer NOT NULL
, start_datetime  timestamptz NOT NULL
, end_datetime    timestamptz GENERATED ALWAYS AS (((start_datetime AT TIME ZONE 'UTC') + make_interval(mins => minute_duration)) AT TIME ZONE 'UTC') STORED
);

If you know that DST will never creep into your calculations, use the time zone 'UTC' which makes the casts extra cheap (binary identical).

fiddle

Note 1

Note how I cast back to timestamptz with the AT TIME ZONE using the same time zone. For practical reasons Postgres would allow to omit that:

...
, end_datetime    timestamptz GENERATED ALWAYS AS ((start_datetime AT TIME ZONE 'UTC') + make_interval(mins => minute_duration)) STORED
);

It only demands:

The generation expression can only use immutable functions [...]

That leaves a backdoor for non-immutable assignment casts. You would assign the returned timestamp to a timestamptz column, and that assignment cast depends on the timezone setting of the session.

Typically, you do not want that. Just because you can do it doesn't mean you should.

Note 2

Consider not adding a (redundant) generated column to begin with, and just use the cheap expression on the fly - unless you have some undisclosed requirement for a persisted column. You could encapsulate that in a VIEW for convenience.

Note 3

Don't do string concatenation + cast (minute_duration || ' minutes')::INTERVAL) which, on top of also not being immutable, is more costly.