I’m trying to change the format of time_discrep (below) into a fraction of days.
Code:
select time1, time2, (time1-time2) as time_discrep
from main_file
order by time1;
Screenshot of results:
I’ve tried dividing, etc, but it just formats it like 00:00:32 and things like that.
UPDATE: I found something that works, but I don’t know if it’s the best way:
Katie is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
A date minus a date give you the number of days between these two dates:
SELECT CAST((INTERVAL '29 days 02:41:44') + current_date AS date) - current_date;
This also works when you have a much larger number of days, like months or years.
The basic math is days + (hours / 24) + (minutes / (24 * 60)) + (seconds / (24 * 60 * 60)). We can write a little function to do the conversion from an interval to a numeric.
create function interval_numeric_days(i interval) returns numeric
language sql
immutable
returns null on null input
return
extract(days from $1) +
(extract(hour from $1) / 24) +
(extract(minute from $1) / (24 * 60)) +
(extract(second from $1) / (24 * 60 * 60))
select interval_numeric_days('29 days 02:41:44'::interval);
interval_numeric_days
-------------------------
29.11231481481481481481
select interval_numeric_days('2026-01-01 00:00:00'::timestamp - '2024-01-01 12:00:00');
interval_numeric_days
------------------------------
730.500000000000000000000000
1
Convert to seconds using extract(epoch from ...) then use arithmetic to get days as a decimal by dividing by the number of seconds in a day:
select
time1,
time2,
(extract(epoch from time1) - extract(epoch from time2))/86400 as time_discrep
from main_file
order by time1
1
- Get the days from the
intervalusingExtract(). - Get the fraction by extracting
epochfrom just the::timepart to turn hours and minutes to seconds in one move, then divide that by one day. - Combine these using a
+.
demo at db<>fiddle
select time1
,time2
,(extract(days from time1-time2)::numeric
+extract(epoch from (time1-time2)::time)/86400.)::numeric(7,2)
from main_file
order by time1;
| time1 | time2 | time_discrep |
|---|---|---|
| 2023-10-17 20:16:08 | 2023-10-13 23:38:53 | 3.86 |
| 2023-11-23 05:00:09 | 2023-10-03 13:25:48 | 50.65 |
| 2023-12-28 13:29:29 | 2023-12-06 19:32:01 | 21.75 |
| 2024-02-26 20:13:06 | 2024-01-11 14:31:16 | 46.24 |
| 2024-02-28 23:50:45 | 2023-12-31 19:50:10 | 59.17 |
| 2024-03-29 07:22:11 | 2024-03-05 22:51:15 | 23.35 |
The ::numeric(7,2) cast is just to make it look nice – skip that if you prefer higher precision.
Feels nice to have the code work similar to how you’d perform the operation mentally, but it can all be shortened to a single extract:
extract(epoch from time1-time2)/86400.
Which turns out is pretty much what @Bohemian had suggested already.
If you’re generating the interval by taking a difference between timestamps, it comes out justified. If you’re working with raw intervals, remember to use justify_interval() before extracting anything but epoch, otherwise:
select extract(days from '5 days 48 hours'::interval);
| 5 |
Even though the hours amount to 2 more days, they get ignored. Justifying fixes that:
select extract(days from justify_interval('5 days 48 hours'));
| 7 |