Sum of declare time values in postgresql

Question

script

DO $$ 
DECLARE
   a time := '05:00:00';
   b time := '10:00:00';
BEGIN 
   RAISE NOTICE '%', a;
   PERFORM pg_sleep(5);
   RAISE NOTICE '%', b;
   RAISE NOTICE '%', a + b::time;
END $$;

Just i want to add a and b, there result will show 17:00:00 this like. When i'm writing this "RAISE NOTICE '%', a + b::time;" then raise error in the following image

Thanks.

Kaushik Nayak · Accepted Answer · 2019-03-31 10:38:23Z

2

time datatype represents a precise time in a day. You can't add two times just as you cannot add two dates. Also, the hour part cannot exceed 24

You should rather define it as an interval.

knayak=# DO $$
knayak$# DECLARE
knayak$#    a interval := '05:00:00';
knayak$#    b interval := '10:00:00';
knayak$# BEGIN
knayak$#    RAISE NOTICE '%', a;
knayak$#    PERFORM pg_sleep(5);
knayak$#    RAISE NOTICE '%', b;
knayak$#    RAISE NOTICE '%', a + b;
knayak$# END $$;
NOTICE:  05:00:00
NOTICE:  10:00:00
NOTICE:  15:00:00
DO

answered Mar 31, 2019 at 10:38

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

3 Comments

Prosenjit Over a year ago

It's simply working. But into a,b variable I have data that is "time with time zone", so in this case how can I explicit time to the interval? Thanks @Kaushik Nayak

Kaushik Nayak Over a year ago

@Prosenjit : You may have clarified that in the question if that was the case. That calls for a separate question.This answers your original question.

Prosenjit Over a year ago

Yes. I need another question about this.

Collectives™ on Stack Overflow

Sum of declare time values in postgresql

script

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

script

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related