178

I have a table in PostgreSQL 8.3 with 2 timestamp columns. I would like to get the difference between these timestamps in seconds. Could you please help me how to get this done?

TableA
(
  timestamp_A timestamp,
  timestamp_B timestamp
)

I need to get something like (timestamo_B - timestamp_A) in seconds (not just the difference between seconds, it should include hours, minutes etc).

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
Arun
  • 2,217
  • 3
  • 17
  • 18

2 Answers2

353

Try: 

SELECT EXTRACT(EPOCH FROM (timestamp_B - timestamp_A))
FROM TableA

Details here: EXTRACT.

altermativ
  • 690
  • 1
  • 6
  • 20
Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
  • 3
    Thanks a lot for the answer. It worked !!! In the above query, we are missing a closing parenthesis . But I have figured it out. Thanks a lot for your quick reply. – Arun Dec 26 '12 at 10:38
43
select age(timestamp_A, timestamp_B)

Answering to Igor's comment:

select age('2013-02-28 11:01:28'::timestamp, '2011-12-31 11:00'::timestamp);
              age              
-------------------------------
 1 year 1 mon 28 days 00:01:28
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • It wont do the job. It will `Subtract arguments, producing a "symbolic" result that uses years and months`. It wont give the difference in seconds. – Ihor Romanchenko Dec 24 '12 at 11:55
  • @Igor Updated with the results including seconds. The OP wants not only seconds but also minutes, hours, etc – Clodoaldo Neto Dec 24 '12 at 12:39
  • 7
    If I understood him correctly, he wants `to get the difference between these timestamps in seconds`. And `it should include hours, minutes etc` means it must be the full difference like `10:25:30 - 10:15:25 = 605 seconds`. My guess - he used `EXTRACT(SECONDS FROM ...)` and got `10:25:30 - 10:15:25 = 5 seconds` – Ihor Romanchenko Dec 24 '12 at 12:52
  • 1
    @Igor It is not very clear but now that you say it I think you are probably right. – Clodoaldo Neto Dec 24 '12 at 12:54
  • @Clodoaldo: I need the output as mentioned by Igor. I need the full difference in seconds. – Arun Dec 26 '12 at 10:33
  • What if I only need a month difference from this query? – Ahmed Nov 17 '19 at 21:34
  • well butter my biscuit – aschyiel Apr 07 '21 at 01:34