How do I convert an interval into a number of hours with postgres?
DatetimePostgresqlIntervalsDatetime Problem Overview
Say I have an interval like
4 days 10:00:00
in postgres. How do I convert that to a number of hours (106 in this case?) Is there a function or should I bite the bullet and do something like
extract(days, my_interval) * 24 + extract(hours, my_interval)
Datetime Solutions
Solution 1 - Datetime
Probably the easiest way is:
SELECT EXTRACT(epoch FROM my_interval)/3600
Solution 2 - Datetime
If you want integer i.e. number of days:
SELECT (EXTRACT(epoch FROM (SELECT (NOW() - '2014-08-02 08:10:56')))/86400)::int
Solution 3 - Datetime
To get the number of days the easiest way would be:
SELECT EXTRACT(DAY FROM NOW() - '2014-08-02 08:10:56');
As far as I know it would return the same as:
SELECT (EXTRACT(epoch FROM (SELECT (NOW() - '2014-08-02 08:10:56')))/86400)::int;
Solution 4 - Datetime
select floor((date_part('epoch', order_time - '2016-09-05 00:00:00') / 3600)), count(*)
from od_a_week
group by floor((date_part('epoch', order_time - '2016-09-05 00:00:00') / 3600));
The ::int
conversion follows the principle of rounding.
If you want a different result such as rounding down, you can use the corresponding math function such as floor
.
Solution 5 - Datetime
If you convert table field:
-
Define the field so it contains seconds:
CREATE TABLE IF NOT EXISTS test ( ... field INTERVAL SECOND(0) );
-
Extract the value. Remember to cast to int other wise you can get an unpleasant surprise once the intervals are big:
EXTRACT(EPOCH FROM field)::int
Solution 6 - Datetime
I'm working with PostgreSQL 11, and I created a function to get the hours betweeen 2 differents timestamps
create function analysis.calcHours(datetime1 timestamp, datetime2 timestamp)
returns integer
language plpgsql as $$
declare
diff interval;
begin
diff = datetime2 - datetime1;
return (abs(extract(days from diff))*24 + abs(extract(hours from diff)))::integer;
end; $$;
Solution 7 - Datetime
select date 'now()' - date '1955-12-15';
Here is the simple query which calculates total no of days.