0

I have a table and I want to calculate the difference (in time) between two columns of my table.

My columns are: scheduled_arrival_time(timestamptz), scheduled_departure_time(timestamptz) and I want to get the difference of them as "scheduled_duration" (scheduled_duration = scheduled_arrival_time - scheduled_departure_time)

I tried this:

scheduled_departure_time TIMESTAMPTZ NOT NULL,
    scheduled_arrival_time TIMESTAMPTZ NOT NULL,
    scheduled_duration numeric(4,2) NOT NULL
                generated always as 
                              ( extract(epoch from (scheduled_arrival_time - scheduled_departure_time))/3600 )
                              stored

but I got the error when I tried to insert data:

ERROR: cannot insert a non-DEFAULT value into column "scheduled_duration" DETAIL: Column "scheduled_duration" is a generated column. SQL state: 428C9

enolic
  • 19
  • 4
  • 1
    Apparently some code tries to provide an explicit value for the generated column. The (partial) DDL you have shown can not cause this error (btw: why a `numeric` column? An `interval` would probably be easier to deal with) –  Jun 16 '22 at 13:16
  • Hmm, something with EXTRACT(EPOCH). I have to look for correct answer. – FrankBlack78 Jun 16 '22 at 13:34
  • @a_horse_with_no_name I didn't know about Interval data type. It looks much closer to what I want. Thank you! – enolic Jun 16 '22 at 14:06
  • 1
    Be aware that if you stick with `numeric(4,2)` you may get a `numeric field overflow` error depending on the granularity of the `scheduled_departure_time` and `scheduled_arrival_time`. – Adrian Klaver Jun 16 '22 at 16:14

1 Answers1

0

You can do this with for excample:

SELECT
    EXTRACT(EPOCH FROM '2022-06-16 15:00:00.00000'::TIMESTAMP - '2022-06-15 15:00:00.00000'::TIMESTAMP)

You get the difference in seconds.

FrankBlack78
  • 152
  • 11