SQL 计算 Postgresql 中的日期差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10065894/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating difference of dates In Postgresql
提问by Jimmy
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
我试图找出表格中某些字段之间的时间。但是因为我使用的是 Postgresql :((我不能使用 DATEDIFF 函数。我在网上找不到任何明确的指南/教程来展示如何在 Postgres 中做类似的事情，所以我需要帮助做同样的事情但在 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?
如果我使用支持 DATEDIFF 函数的 RDBMS，我假设这个查询会起作用，所以基本上我的问题是如何更改它以便它使用 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.
如果您需要更多信息或任何说明，我会尽快回复。
回答by a_horse_with_no_name
You don't need a "datediff" function.
您不需要“datediff”功能。
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
如果您不知道，但所有这些都在线记录：http:
//www.postgresql.org/docs/current/static/functions-datetime.html
回答by Pushpak Dagade
You can use the age(<date1>, <date2>)function (instead of DATEDIFF).
您可以使用该age(<date1>, <date2>)函数（而不是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.
注意，如果 psql 给你这个错误 - ERROR: date/time field value out of range，那么你需要选择一个合适的datestyle.
回答by Paulo Henrique Foxer
SELECT extract(year from age('2014-01-23', '1985-08-27'));
-- returns 28 years, my current age.
