两个日期之间的年份差异 [英] Difference in years between two dates

问题描述

我在postgresql中有一个表，该表的列数据类型 TIMESTAMP 名为 birthdate ，是否可以编写一条返回以下内容的sql语句：与表中存储的生日和当前日期之间的年数差异？怎么做？我的服务器是Postgresql

I have a table in postgresql which has a column datatype TIMESTAMP named birthdate, is it possible to write a sql statement that would return the difference in years from the birthdate stored in the table and the current date? How would this be done? My server is Postgresql

这是我尝试过的方法，但是在第二次选择时却给了我语法错误

This is what I have tried but it is giving me a syntax error at the second select

select DATEDIFF(yy, NOW(), select birthdate from match where match_id = '550856d8560a64ed180416d1556f5435f4bb054c68930040')

推荐答案

这里有很多错误。

select DATEDIFF
       ^^^^
       PostgreSQL doesn't have a datediff function.

regress-> \df datediff
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)

我认为您想要-运算符， extract 函数， justify_interval 和 to_char 函数。另外，写 current_timestamp 而不是 now（），这是标准拼写。

I think you want the - operator, the extract function, justify_interval and the to_char function. Also, write current_timestamp instead of now(), it's the standard spelling.

也这样：

(yy, NOW(), select birthdate ...)
            ^^^

是语法错误，因为您没有将子查询包装在（括号），应为：

is a syntax error because you didn't wrap the subquery in (parentheses), it should be:

(yy, NOW(), (select birthdate  ...))

但在这种情况下，不需要子查询，因为您可以将其展平为外部查询，当子查询固定但没有其他条件时，将为您提供此查询：

but in this case no subquery is necessary because you can just flatten it into the outer query, which gives you this when the subquery is fixed but nothing else is:

select DATEDIFF(yy, NOW(), birthdate)
from match where match_id = '550856d8560a64ed180416d1556f5435f4bb054c68930040';

并减去日期：

regress=> SELECT extract(year FROM justify_interval(current_timestamp - DATE '2008-02-01'));
 date_part 
-----------
         7
(1 row)

给出类似的结果：

SELECT extract(year FROM justify_interval(current_timestamp - birthdate))
from match where match_id = '550856d8560a64ed180416d1556f5435f4bb054c68930040';

这篇关于两个日期之间的年份差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！