0

I have two timestamps and I would like to have a result with the difference between them. I found a similar question asked here but I have noticed that:

select 
 to_char(column1::timestamp - column2::timestamp, 'HH:MS:SS')
from
 table

Gives me an incorrect return if these timestamps cross multiple days. I know that I can use EPOCH to work out the number of hours/days/minutes/seconds etc but my use case requires the result as a timestamp (or a string...anything not an interval!).

In the case of multiple days I would like to continue counting the hours, even if it should go past 24. This would allow results like:

36:55:01
Sorvah
  • 333
  • 1
  • 12

1 Answers1

2

I'd use the built-in date_part function (as previously described in an older thread: How to convert an interval like "1 day 01:30:00" into "25:30:00"?) but finally cast the result to the type you desire:

SELECT
   from_date,
   to_date,
   to_date - from_date as date_diff_interval,
   (date_part('epoch', to_date - from_date) * INTERVAL '1 second')::text as date_diff_text
from (
   (select
    '2018-01-01 04:03:06'::timestamp as from_date,
    '2018-01-02 16:58:07'::timestamp as to_date)
) as dates;

This results in the following:

enter image description here

I'm currently unaware of any way to convert this interval into a timestamp and also not sure whether there is a use for it. You're still dealing with an interval and you'd need a point of reference in time to transform that interval into an actual timestamp.

le_affan
  • 314
  • 2
  • 9
  • That seems to produce exactly what I'm looking for when I run it on my own table. I'm not familiar with PGSQL at all and part of my problem was that I don't really understand what the 'interval' type is beyond the fact it returns an object I don't want to iterate on in javascript. Thanks for the answer, that'll take me a long way for my use case – Sorvah Feb 15 '18 at 11:06