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

How to get the difference between two timestamps in a readable format in PostgreSQL

I have a table in PostgreSQL with two columns, created_on and closed_on, which contain timestamps in Unix milliseconds. I want to get the difference between these two timestamps in a readable format, such as "1 year, 9 days, 1 hour, 6 minutes, 1 second".

My initial table :

id created_on closed_on
1 1654850834025 1687168395586

I have tried the following query:

SELECT 
    (closed_on - created_on) * interval '1 millisecond' as interval1,
    justify_interval(32317561561 * interval '1 millisecond') as interval2,
    to_timestamp(closed_on / 1000)::timestamp - to_timestamp(created_on / 1000)::timestamp as interval3,
    to_timestamp(created_on / 1000)::timestamp as created_on,
    to_timestamp(closed_on / 1000)::timestamp as closed_on
FROM test

This query returns the following results:

interval1 interval2 interval3 created_on closed_on
{"hours":8977,"minutes":6,"seconds":1,"milliseconds":561} {"years":1,"days":14,"hours":1,"minutes":6,"seconds":1,"milliseconds":561} {"days":374,"hours":1,"minutes":6,"seconds":1} 2022-06-10T08:47:14.000Z 2023-06-19T09:53:15.000Z

However, the results are not as expected:

  • interval1 only shows the hours, not the days, months, etc.
  • interval2 incorrectly shows 14 days, when there should be 9 days in a year.
  • interval3 shows the correct number of days, but it does not show the
    years or months.

Expected result:

1 year, 9 days, 1 hour, 6 minutes, 1 second

enter image description here

Fiddle : https://www.db-fiddle.com/f/xnXPVmtBEBDih83ZutBMZ4/0

Tags:

2

Answers


  1. 0

    According to https://www.postgresql.org/docs/current/functions-datetime.html, justify_interval counts with 30-day months and timestamp subtraction counts with 24-hour days. Without a calendar and concrete start/end timestamps, that’s the best you can do.

    It seems you are looking for the age function instead:

    age ( timestamp, timestamp ) → interval

    Subtract arguments, producing a “symbolic” result that uses years and
    months, rather than just days

    SELECT age(to_timestamp(closed_on / 1000), to_timestamp(created_on / 1000))
FROM test

    (online demo)

    Login or Signup to reply.
  2. 0

    Why would you ever divide the epoch value by 1000? That is entirely different timestamp.

    Also using justify_interval() you are saying to treat months as 30 days each, then it is 1 year and 14 days.

    CREATE TABLE test (
    id serial PRIMARY KEY,
    created_on BIGINT NOT NULL,
    closed_on BIGINT NOT NULL
);

INSERT INTO test (id, created_on, closed_on)
 VALUES (1, 1654850834025/1000, 1687168395586/1000);

select to_timestamp(created_on) created_on, 
       to_timestamp(closed_on) cloased_on,
       age( to_timestamp(closed_on) , to_timestamp(created_on)) "interval"
  from test;
    created_on cloased_on interval
    2022-06-10 09:47:14+01 2023-06-19 10:53:15+01 1 year 9 days 01:06:01

    DBfiddle demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search