CODEWITHSUNDEEP
postgresqlsum
A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38552

Calculate the sum of time column in PostgreSql

Can anyone suggest me, the easiest way to find summation of time field in POSTGRESQL. i just find a solution for MYSQL but i need the POSTGRESQL version.

MYSQL: https://stackoverflow.com/questions/3054943/calculate-sum-time-with-mysql

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(timespent))) FROM myTable;

Demo Data

id   time 
1   1:23:23
2   4:00:23
3   9:23:23

Desired Output

14:47:09

Upvotes: 2

Views: 18147

Answers (3)

Gabriel Perez
Gabriel Perez

Reputation: 1

If you need to calculate sum of some field, according another field, you can do this:

select
    keyfield,
    sum(time_col::interval) totaltime
    FROM myTable
    GROUP by keyfield

Output example: keyfield; totaltime "Gabriel"; "10:00:00" "John"; "36:00:00" "Joseph"; "180:00:00"

Data type of totaltime is interval.

Upvotes: 0

teoreda
teoreda

Reputation: 2580

I tried this solution on sql fieddle: link

Table creation:

CREATE TABLE time_table (
 id integer, time time
);

Insert data:

INSERT INTO time_table (id,time) VALUES
  (1,'1:23:23'),
  (2,'4:00:23'),
  (3,'9:23:23')

query the data:

SELECT
  sum(s.time)
FROM 
  time_table s;

Upvotes: 2

pozs
pozs

Reputation: 36274

What you want, is not possible. But you probably misunderstood the time type: it represents a precise time-point in a day. It doesn't make much sense, to add two (or more) times. f.ex. '14:00' + '14:00' = '28:00' (but there are no 28th hour in a day).

What you probably want, is interval (which represents time intervals; hours, minutes, or even years). sum() supports interval arguments.

If you use intervals, it's just that simple:

SELECT sum(interval_col) FROM my_table;

Although, if you stick to the time type (but you have no reason to do that), you can cast it to interval to calculate with it:

SELECT sum(time_col::interval) FROM my_table;

But again, the result will be interval, because time values cannot exceed the 24th hour in a day.

Note: PostgreSQL will even do the cast for you, so sum(time_col) should work too, but the result is interval in this case too.

Upvotes: 5

Related Questions