postgres: how to add 2 'time without timezone' columns?

Question

I'm trying to add 2 'time without timezone' columns in postgres. What is the best way to achieve or correct this?

SELECT time_of_day + offset_time
FROM event_time;

"Error: operator is not unique: time without time zone + time without time zone. HINT: Could not choose a best candidate operator. You might need to add explicit type casts. SQL state: 42725".

A time is a "point in time". Adding "8 in the morning" to "10 in the evening" doesn't really make sense. — user330315
– user330315, Commented May 7, 2021 at 18:58
In other words: change the data type of offset_time to interval. — Laurenz Albe
– Laurenz Albe, Commented May 8, 2021 at 3:38

Adrian Klaver · Accepted Answer · 2021-05-07 18:41:28Z

3

What is happening:

select '11:34'::time + '1:00'::time;
ERROR:  operator is not unique: time without time zone + time without time zone
LINE 1: select '11:34'::time + '1:00'::time;

Per Gordon Linoffs comment, what you need to do:

select '11:34'::time + '1:00'::interval;
 ?column? 
----------
 12:34:00

Per docs Date/Time Operators you can subtract times, but you can't add them. You can only add an interval to a time.

answered May 7, 2021 at 18:41

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres: how to add 2 'time without timezone' columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related