CODEWITHSUNDEEP
postgresqlselect
sealabr
sealabr

Reputation: 1672

PostgreSQL difference between two dates and return in hours and minutes

Hello I want to return in my PostgreSQL the difference between two dates: 

START: 2016-06-01  00:00:00
END: 2016-06-06  08:35:33

Expected return value: 128:35:33, formatted like format [h]:mm:ss;@ in Excel. Hours must be added up if there is more than 24 hours of difference.

Here's my SQL:

SELECT EXTRACT(EPOCH FROM dt_termino::timestamp - dt_inicio::timestamp)/3600 FROM crm.task_interacao WHERE id_task_tarefa = 1

UPDATE!!!

hello now i'm facing another problema I have a table like this:

my table in database like this
start;end
2013-06-01 09:29:33;2016-06-07 14:08:19
2016-06-07 14:22:09;2016-06-07 14:22:43
2016-06-07 14:22:51; null

i need to sum values ....i'm trying as you said (1st awnser).. I cant use function because i'm using inside a php code

SELECT SUM(COALESCE(end::timestamp, now()::timestamp) - start::timestamp) FROM crm.task_interacao WHERE id_task_tarefa = 1

but is returning

1102 days 26:07:54.864879

why 26 hours??? I was supose be te at maximum 24...

no problem now to return (Days HH:MM:SS) and not miliseconds

Upvotes: 2

Views: 6947

Answers (1)

klin
klin

Reputation: 121889

You can simply subtract timestamps to get interval:

select '2016-06-06 08:35:33'::timestamp- '2016-06-01 00:00:00' result

     result      
-----------------
 5 days 08:35:33
(1 row)

There is no standard function to convert the result to the format you need but you can write one:

create or replace function interval_without_days(interval)
returns interval language sql as $$
    select $1- date_part('day', $1)* '1d'::interval+ date_part('day', $1)* '24h'::interval;
$$;

select interval_without_days('2016-06-06 08:35:33'::timestamp- '2016-06-01 00:00:00');

 interval_without_days 
-----------------------
 128:35:33
(1 row)

Question #2. Use the functions date_trunc(text, interval) and justify_hours(interval):

select date_trunc('sec', justify_hours('1102 days 26:07:54.864879'));

     date_trunc     
--------------------
 1103 days 02:07:54
(1 row)

Upvotes: 2

Related Questions