4

Calculate the time between these two timestamps in PostgreSQL,
create_time='2017-11-02 05:51:13' and update_time='2017-11-02 07:36:18'
and display it on HH:MM:SS Format. it should display like this 01:45:04

Ivan Aracki
  • 4,861
  • 11
  • 59
  • 73
  • 1
    Possible duplicate of [Find difference between timestamps in seconds in PostgreSQL](https://stackoverflow.com/questions/14020919/find-difference-between-timestamps-in-seconds-in-postgresql) – Ivan Aracki Nov 02 '17 at 11:36
  • What is the desired result if the timestamps are on different days? – Laurenz Albe Nov 02 '17 at 11:43
  • @IvanAracki I don't think this is an exact duplicate of that question, because the OP wants the time portion of the timestamp, not just seconds (or minutes, etc.). – Tim Biegeleisen Nov 02 '17 at 11:44
  • What if the difference is bigger than one day? What do you want to see then? –  Nov 02 '17 at 11:59

1 Answers1

2

You can try just subtracting the two timestamps, then using to_char to extract out the time portion:

select
    SELECT to_char('2017-11-02 07:36:18'::timestamp -
                   '2017-11-02 05:51:10'::timestamp, 'HH:MI:SS');

Output:

to_char
01:45:08

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360