'sql (or postgres) how to set the date as single year and month?

I am compiling data of multiple years but I only need time not date and year. How can I set the year and date data into a single year and a single date so that I can use the time information as accumulative data? 2020-01-01 + time value.

Thanks!!!!

postgresqldatetime


Solution 1:[1]

The information you provided is a little light. Still with certain assumptions:

create table ts_sum (ts_fld timestamptz);

insert into ts_sum values ('2020-04-14 08:15:32'), ('2021-09-27 18:45:01'), ('2022-01-09 20:21:05');

select sum(to_char(ts_fld, 'HH24:MI:SS')::interval) from ts_sum;
   sum    
----------
 47:21:38

Needs to be tested with your data. The procedure is extract the time portion out of the timestamp using to_char then cast that to an interval and then sum the time intervals.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Adrian Klaver

Related Questions