PostgreSQL Computed Date Timestamp Column

Question

In PostgreSQL, I want to make a computed column, where end_datetime = start_datetime + minute_duration adding timestamps

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 would be trigger or computed on minute_duration, but trying to refrain from this method in follow up question PostgreSQL Meeting Table, Computed Column on TimeDuration or Trigger on EndDatetime

Answer 1

You could do something like this - but it involves "cheating".

First, you create a function as follows (all the code below is available on the fiddle here):

CREATE OR REPLACE FUNCTION Date_Add(tz1 TIMESTAMPTZ, mindur INT)
RETURNS TIMESTAMPTZ
AS 
$$ 
  SELECT $1 + ($2 || ' MINUTES')::INTERVAL
$$
LANGUAGE SQL IMMUTABLE;

There is a risk associated with this - you could end up with errors with edge cases where you go over day/month/year boundaries. If you always work in the same timezone, you should be fine! You have been warned! :-)

So now, we come to your table.

CREATE TABLE apt 
( 
  apt_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  mindur INTEGER NOT NULL,
  sd     TIMESTAMPTZ NOT NULL,
  ed     TIMESTAMPTZ GENERATED ALWAYS AS (Date_Add(sd, mindur)) STORED
);

Not sure why you want a UUID here - best to use UUID v7 (PG 18) - or you could use a ULID extension (similar to v7) and - see here, plus there are v7 extensions available for earlier versions of PostgreSQL.

And populate:

INSERT INTO apt (mindur, sd) VALUES 
(120,  '2025-08-08 14:00'::TIMESTAMPTZ),
(1440, '2025-08-08 15:00'::TIMESTAMPTZ);  -- 1440 = 24hr

and to check - SELECT * FROM apt;

Result:

mindur              sd                  ed
   120  2025-08-08 14:00:00+00  2025-08-08 16:00:00+00
  1440  2025-08-08 15:00:00+00  2025-08-09 15:00:00+00

Et voilà!

Answer 2

@PeterVandivier rightly points out that the immutability comes from TIMESTAMPTZ, but it also comes from the interval. In general, intervals are treated as mutable (it appears, even with a TIMESTAMP without time zone).

The problem comes from the fact that some intervals are time-zone dependent, in particular days: you get days of 23 and 25 hours around the Daylight Saving Time changes.

Here, you're working with minutes, so you can work around this by making everything immutable:

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 AT TIME ZONE 'UTC' + (minute_duration * '1 minute'::INTERVAL)) AT TIME ZONE 'UTC') STORED
); 

To explain this a bit more closely:

(start_datetime AT TIME ZONE 'UTC'
   + (minute_duration * '1 minute'::INTERVAL)
) AT TIME ZONE 'UTC'

• start_datetime AT TIME ZONE 'UTC' converts your TIMESTAMPTZ into its UTC TIMESTAMP equivalent in an immutable way.

• minute_duration * '1 minute'::INTERVAL multiplies the number of minutes (immutable) by a 1-minute interval (also immutable). This trick is necessary, because the (minute_duration || ' minutes')::INTERVAL doesn't seem to detect that the string will necessarily be a number and the word ' minutes', making it immutable (an arbitrary string as as '1 day 1 hour' would not necessarily be immutable).

• Convert it back to a TIMESTAMPTZ with AT TIME ZONE 'UTC'.

Note that the end result will not necessarily be in UTC, but in whatever time zone you've chosen with your session (as you would expect from any TIMESTAMPTZ column).

Indeed, PostgreSQL's TIMESTAMP WITH TIME ZONE doesn't actually store any time zone information: it just displays and makes calculations with the time zone you're currently using.

Answer 3

The volatility is coming from the start_datetime column. You must declare a timezone for your timestamp in some static way.

For example, the following expression is valid:

start_datetime at time zone 'utc' + make_interval(mins => minute_duration)

See also complete fiddle examples.

Further discussion on this thread.

You may also consider

1. declaring end_datetime explicitly and enforcing the relationship with a check constraint

  end_datetime   timestamptz not null
    check (
      end_datetime = (start_datetime + duration)
    ) 

2. making duration your computed column

  duration       interval
    generated always
    as (end_datetime - start_datetime)
    stored,

---

Sidebar: unfortunately, extraction of time zone data from timestamptz is not immutable (further reading) so you have to trust that a UTC timestamp is in fact present in the start_datetime column. IIRC timestamptz is always stored as UTC but I expect I may be missing possible failure cases for these examples.