Search code examples
sqlpostgresqldatediff

Calculating difference of dates In Postgresql

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.

Solution

  • 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