Calculating difference of dates In Postgresql

Question

I'm trying to find out the time between certain fields in my tables. However cause I'm using Postgresql :(( I can't use the DATEDIFF function. I can't find any clear guides/ tutorials on the net which shows how to do a similar thing in Postgres so I need help doing the same thing but in Postgres

I'm assuming this query would work if I was using a RDBMS that supported the DATEDIFF function so basically my question is how can I change this so it works using features provided by Postgresql?

SELECT Question.ID, 
Question.Status, COUNT (qUpdate.ID)  AS NumberofUpdates,
DATEDIFF (Question.LoggedTime,MIN(qUpdate.UpdateTime)) AS TimeBeforeFirstUpdate,
DATEDIFF(Question.LoggedTime, MAX(qUpdate.UpdateTime)) AS TimeBeforeLastUpdate
FROM qUpdate
LEFT JOIN Question ON qUpdate.qID=Question.ID
WHERE Question.Status = 'closed' AND qUpdate.Update NOT NULL
GROUP BY Question.Status, Question.ID, Question.LoggedTime;

If you need more info or any clarification I'll responsd ASAP.

Postgres is very feature rich. It really is worth reading through the manual. — Glenn, Apr 08 '12 at 19:56
Sorry I've never been shown that before. I'm definitely going to look at that from now on :) — Jimmy, Apr 08 '12 at 19:59

score 12 · Accepted Answer · 2014-09-12T06:02:01.863

12

You don't need a "datediff" function.

Just subtract the two dates:

Question.LoggedTime - MIN(qUpdate.UpdateTime)

In case you don't know, but all that is documented online:
http://www.postgresql.org/docs/current/static/functions-datetime.html

edited Sep 12 '14 at 06:02

answered Apr 08 '12 at 19:47

1

And I was struggling with the age function all this time :/ – itsols Nov 10 '13 at 03:43

score 4 · Answer 2 · answered Apr 08 '12 at 19:54

You can use the age(<date1>, <date2>) function (instead of DATEDIFF).

This should work -

SELECT Question.ID, 
Question.Status, COUNT (qUpdate.ID)  AS NumberofUpdates,
age(Question.LoggedTime,MIN(qUpdate.UpdateTime)) AS TimeBeforeFirstUpdate,
age(Question.LoggedTime, MAX(qUpdate.UpdateTime)) AS TimeBeforeLastUpdate
FROM qUpdate
LEFT JOIN Question ON qUpdate.qID=Question.ID
WHERE Question.Status = 'closed' AND qUpdate.Update NOT NULL
GROUP BY Question.Status, Question.ID, Question.LoggedTime;

Note, if psql gives you this error - ERROR: date/time field value out of range, then you would need to choose an appropriate datestyle.

score 3 · Answer 3 · answered Jan 23 '14 at 13:22

3

SELECT extract(year from age('2014-01-23', '1985-08-27'));
-- returns 28 years, my current age.

answered Jan 23 '14 at 13:22

Paulo Henrique

182
1
6

score 0 · Answer 4 · answered Oct 12 '21 at 21:56

0

this gives you the time diff in seconds:

select extract(epoch from to_date::timestamp - from_date::timestamp)

answered Oct 12 '21 at 21:56

Afshin Amiri

3,438
1
20
21

Calculating difference of dates In Postgresql

4 Answers4

Linked