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
– Glenn, Commented Apr 8, 2012 at 19:56
Sorry I've never been shown that before. I'm definitely going to look at that from now on :) — Jimmy
– Jimmy, Commented Apr 8, 2012 at 19:59

score 12 · Accepted Answer · 2014-09-12 06:02:01Z

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, 2014 at 6:02

answered Apr 8, 2012 at 19:47

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

itsols Over a year ago

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

Pushpak Dagade · Accepted Answer · 2012-04-08 19:54:10Z

5

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.

answered Apr 8, 2012 at 19:54

Pushpak Dagade

6,4907 gold badges31 silver badges43 bronze badges

Comments

Paulo Henrique · Accepted Answer · 2014-01-23 13:22:51Z

4

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

answered Jan 23, 2014 at 13:22

Paulo Henrique

1921 silver badge6 bronze badges

Comments

Afshin Amiri · Accepted Answer · 2021-10-12 21:56:39Z

0

this gives you the time diff in seconds:

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

answered Oct 12, 2021 at 21:56

Afshin Amiri

3,6231 gold badge22 silver badges22 bronze badges

Comments

Daniel T · Accepted Answer · 2024-07-02 22:42:03Z

0

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.

answered Jul 2, 2024 at 22:42

Daniel T

237 bronze badges

Collectives™ on Stack Overflow

Calculating difference of dates In Postgresql

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related