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.

Diarmuid

Postgres is very feature rich. It really is worth reading through the manual. – Skeie

Sorry I've never been shown that before. I'm definitely going to look at that from now on :) – Diarmuid

Answer 1

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

Gusto

And I was struggling with the age function all this time :/ – Finsen

Answer 2

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.

Speechmaker

Answer 3

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

Cellist

Answer 4

this gives you the time diff in seconds:

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

Lora

Answer 5

Im using Postgresql 14, and want to find difference between two fields: end_date and start_date.

Both fields come with the hour:minute:seconds components, but the type=date for both fields, so those components are zeroed out.

I was able to get the number of days (my goal) between end_date and start_date merely by subtracting start_date from end_date, with no formulas involved.

In my example,

start_date = 05/25/2021 00:00:00
end_date = 06/30/2021 00:00:00
end_date - start_date = 36

I don't know if you're using version 14, but that's how it functions for me.

Flory