Search code examples
sqlpostgresqlintervals

PostgreSQL query to breakdown interval into weeks, days, hours, minutes, seconds

I am pretty inexperienced in PostgreSQL and am trying to get a row returning an interval from the current datetime to a given datetime as weeks, days, hours, minutes, and seconds broken down into separate columns.

The closest I've gotten other than a very convoluted solution is this:

SELECT

    (CURRENT_TIMESTAMP - '2021-01-01 00:00:00')::text AS str,
    
    SUBSTRING((CURRENT_TIMESTAMP - '2021-01-01 00:00:00')::text FROM  1 FOR 2)::NUMERIC AS days,
    SUBSTRING((CURRENT_TIMESTAMP - '2021-01-01 00:00:00')::text FROM  9 FOR 2)::NUMERIC AS hours,
    SUBSTRING((CURRENT_TIMESTAMP - '2021-01-01 00:00:00')::text FROM 12 FOR 2)::NUMERIC AS minutes,
    SUBSTRING((CURRENT_TIMESTAMP - '2021-01-01 00:00:00')::text FROM 15 FOR 2)::NUMERIC AS seconds

This gets me close: enter image description here

My questions are:

  1. This implementation relies on the number of characters in the X days substring always being the same, which won't be true, so how do I make it more flexible?
  2. Can I save the result of (CURRENT_TIMESTAMP - '2021-01-01 00:00:00')::text to a simple variable so that I don't have to repeat it?
  3. How can I add a 'weeks' column and get 5 weeks, 5 days, ... instead of 40 days?

I've looked at https://stackoverflow.com/a/56776697/2909854, but I don't want months.

If I could just save the output of each result to a variable, I could do something relatively clean like this: https://stackoverflow.com/a/21323783/2909854

Any help is appreciated. Thanks!

Solution

  • You can use extract()

    SELECT  (CURRENT_TIMESTAMP - '2021-01-01 00:00:00')::text AS str,
        extract(day from CURRENT_TIMESTAMP - '2021-01-01 00:00:00')as days,
        extract(hour from CURRENT_TIMESTAMP - '2021-01-01 00:00:00')as hours,
        extract(minute from CURRENT_TIMESTAMP - '2021-01-01 00:00:00')as minutes,
        extract(second from CURRENT_TIMESTAMP - '2021-01-01 00:00:00')as seconds;

    To avoid repeating the expression you can use a common table expression:

    with input (duration) as (
  values (CURRENT_TIMESTAMP - '2021-01-01 00:00:00')
)
select duration::text as str,
       extract(day from duration) as days, 
       extract(hour from duration) as hours,
       extract(minute from duration) as minutes,
       extract(second from duration) as seconds
from input;

    You can't get "weeks" from an interval, but you can use justify_interval() to convert the days to months