postgresqltimetimestamp

Insert time fields in timerange

I have a PostgreSQL table with these fields:

Then I added a field calde as a "timerange" (tsrange).

I need to update the table and set F1 and F2 to a tsrange, but I get:

update Table1 set timerange=[F1,F2]
ERROR:  syntax error at or near "["
Solution

  • First of all, it is not clear if you want a range of times (that is, something like opening hours from 8 a.m. to 6 p.m.) or a range of timestamps (something like a game that started on Jan 11 2025 at 8 p.m. and ended at 10 p.m. that same day).

    If it is a range of timestamps you need, the answer is pretty simple:

    UPDATE table1
SET timerange = tsrange(f1, f2);

    If you need a range of times, the task is more complicated, because PostgreSQL doesn't have such a type built in. But it is not too hard to create a time range type:

    CREATE FUNCTION trange_subdiff(time, time) RETURNS double precision
IMMUTABLE PARALLEL SAFE
RETURN EXTRACT (epoch FROM $2 - $1);

CREATE TYPE trange AS RANGE (
   SUBTYPE = time,
   SUBTYPE_DIFF = trange_subdiff
);

    When you create this range type, PostgreSQL automatically creates a function trange(time, time) that you can use to construct time ranges:

    UPDATE table1
SET timerange = trange(f1, f2);