Question 408419 in PostgreSQL Question posted in
The official documentation can be found here.

How to format time interval in PostgreSQL from "29 days 02:41:44" to a decimal number of days?

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:
enter image description here

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:
enter image description here

Tags:

4

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
  4. 0
    1. Get the days from the interval using Extract().
    2. Get the fraction by extracting epoch from just the ::time part to turn hours and minutes to seconds in one move, then divide that by one day.
    3. 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search