Calculating difference of dates In Postgresql

sql postgresql datediff
Jimmy picture Jimmy · Apr 8, 2012 · Viewed 25.2k times · Source

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.

Answer

a_horse_with_no_name picture a_horse_with_no_name · Apr 8, 2012

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

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More