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

If the information you have - possibly automatically transmitted - is the start datetime and duration in minutes, you should do the following (and thanks to Peter Vandivier for his excellent post on the issue):

CREATE TABLE t
(
  apt_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),  
  sdt TIMESTAMPTZ NOT NULL,
  duration_mins INTEGER NOT NULL,
  edt TIMESTAMPTZ NOT NULL
    GENERATED ALWAYS AS 
      (sdt AT TIME ZONE 'Europe/Dublin' + MAKE_INTERVAL(0, 0, 0, 0, 0, duration_mins))
        STORED
);

or

CREATE TABLE s
(
  apt_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),  
  sdt TIMESTAMPTZ NOT NULL,
  duration_mins INTEGER NOT NULL,
  edt TIMESTAMPTZ NOT NULL
    GENERATED ALWAYS AS 
      (sdt AT TIME ZONE 'Europe/Dublin' + (duration_mins * '1 MINUTE'::INTERVAL))
        STORED
);

Other posters have suggested other possible rearrangements - but if you have a start datetime and a given duration, then either of these is the way to go.

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 than 18 (which, at the time of writing, is still in beta).

According to Bruno (same thread):

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).

So, now for testing. I'm in 'Europe/Dublin', so I set the timezone.

SET TIMEZONE = 'Europe/Dublin'; 

populate:

INSERT INTO t VALUES
('2025-08-09 14:00'::TIMESTAMPTZ, 60),
('2025-08-09 14:00'::TIMESTAMPTZ, 1440);   -- <<--- 1440 = 24hrs

To check:

SELECT LEFT(apt_id::TEXT, 10), sdt, duration_mins, edt FROM t;

Result:

   left               sdt          duration_mins              edt
a94714b5-6  2025-08-09 14:00:00+01      60      2025-08-09 15:00:00+01
808c77ec-0  2025-08-09 14:00:00+01     1440     2025-08-10 14:00:00+01

Et voilà! Same results for the other table - see fiddle.

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.

---

To expand a little bit on why TIMESTAMPTZ makes this mutable, this is because it makes the calculations based on that timestamp dependent upon the time zone currently set for the transaction/session. Here is an example:

CREATE TABLE test1 (
   id INTEGER PRIMARY KEY,
   original_ts TIMESTAMPTZ,
   added_ts TIMESTAMPTZ
);

SET TIME ZONE 'Africa/Douala'; -- No Daylight Saving Time change
INSERT INTO test1(id, original_ts, added_ts)
    VALUES (1,
            '2025-03-29T15:00:00Z'::TIMESTAMPTZ,
            '2025-03-29T15:00:00Z'::TIMESTAMPTZ + '1 day'::INTERVAL);

SET TIME ZONE 'Europe/London'; -- Daylight Saving Time change
INSERT INTO test1(id, original_ts, added_ts)
    VALUES (2,
            '2025-03-29T15:00:00Z'::TIMESTAMPTZ,
            '2025-03-29T15:00:00Z'::TIMESTAMPTZ + '1 day'::INTERVAL);

SET TIME ZONE 'Europe/Paris';
SELECT * FROM test1;

In both cases, I'm adding "1 day" to 2025-03-29T15:00:00Z (15:00 at UTC). However,:

• the Africa/Douala time zone doesn't have DST change, so "+1 day" is always going to be "+24 hours",
• the Europe/London time zone changes from UTC to UTC+1 on the 2025-03-30 at 1AM, so "+1 day" means "+23 hours" on that day.

As a result, you get this:

id	original_ts	added_ts
1	2025-03-29 16:00:00+01	2025-03-30 17:00:00+02
2	2025-03-29 16:00:00+01	2025-03-30 16:00:00+02

This is where the mutability comes from: in general, operations on TIMESTAMP WITH TIME ZONE vary depending on what you have set for the session/transaction.

Coming back to the fact that the time zone doesn't really affect storage, but only display and operations, if I change the current time zone, I'll get the same instants in time presented with different time zones:

SET TIME ZONE 'Europe/Bucharest';
SELECT * FROM test1;

id	original_ts	added_ts
1	2025-03-29 17:00:00+02	2025-03-30 18:00:00+03
2	2025-03-29 17:00:00+02	2025-03-30 17:00:00+03

If I now repeat the same experiment with plain TIMESTAMP (without time zone), I get this (which does not vary based on the current time zone setting):

CREATE TABLE test2 (
   id INTEGER PRIMARY KEY,
   original_ts TIMESTAMP,
   added_ts TIMESTAMP
);

SET TIME ZONE 'Africa/Douala';
INSERT INTO test2(id, original_ts, added_ts)
    VALUES (1,
            '2025-03-29T15:00:00'::TIMESTAMP,
            '2025-03-29T15:00:00'::TIMESTAMP + '1 day'::INTERVAL);

SET TIME ZONE 'Europe/London';
INSERT INTO test2(id, original_ts, added_ts)
    VALUES (2,
            '2025-03-29T15:00:00'::TIMESTAMP,
            '2025-03-29T15:00:00'::TIMESTAMP + '1 day'::INTERVAL);

SET TIME ZONE 'Europe/Bucharest';
SELECT * FROM test2;

id	original_ts	added_ts
1	2025-03-29 15:00:00	2025-03-30 15:00:00
2	2025-03-29 15:00:00	2025-03-30 15:00:00

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.