0

I have two Timestamp columns in a PostgreSQL table (e.g, 2019-07-12 07:59:59). I need to subtract two columns and save the (HH:MM:SS) value under new name?

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
Vivek
  • 1
  • 3
    check this link: https://stackoverflow.com/questions/14020919/find-difference-between-timestamps-in-seconds-in-postgresql – zip Jan 27 '20 at 18:17
  • I don't want to change the format into seconds.... Final subtracted value should be like hh:mm:ss? – Vivek Jan 27 '20 at 18:41
  • You combine it with a conversion of second into HH:mm:ss – zip Jan 27 '20 at 18:43

2 Answers2

2

There is no need to compute the epoch. Subtraction of timestamps gives an interval which directly converted:

with timestamps (tsa, tsb) as 
     (values ('2020-01-15T06:15:00'::timestamp, '2020-01-15T18:15:00'::timestamp)) 
select to_char(tsb-tsa,'hh24:mi:ss') from timestamps;
Belayer
  • 13,578
  • 2
  • 11
  • 22
0

You combine the two: First get the difference in seconds and convert seconds in hhmmss:

SELECT TO_CHAR((EXTRACT(EPOCH 
               FROM (timestamp_B - timestamp_A)) || ' second')::interval, 'HH24:MI:SS')
zip
  • 3,938
  • 2
  • 11
  • 19