ANSI 92日期差异在MySQL中不起作用

I'm, trying to calculate the number of days between two dates using ANSI SQL standard. But I'm missing something as this statement returns NULL in MySQL.

我正在尝试使用ANSI SQL标准计算两个日期之间的天数。但我错过了一些东西,因为这个语句在MySQL中返回NULL。

SELECT EXTRACT(DAY FROM DATE('2009-01-25') - DATE('2009-01-01')) AS day_diff;

SELECT EXTRACT(日期('2009-01-25') - DATE('2009-01-01'))AS day_diff;

I'm aware of the MySQL DATEDIFF function, but I'm curious why this code isn't working.

我知道MySQL DATEDIFF函数,但我很好奇为什么这段代码不起作用。

What am I missing?

我错过了什么?

1 个解决方案

#1

Is this what you meant to do?

这是你的意思吗?

mysql> SELECT EXTRACT(DAY FROM DATE('2009-01-25')) - 
         EXTRACT(DAY FROM DATE('2009-01-01')) AS day_diff;
+----------+
| day_diff |
+----------+
|       24 |
+----------+
1 row in set (0.00 sec)

UPDATE:

If you want this to work for dates in different months (or even different years), then you can use the MySQL DATEDIFF() function.

如果您希望这适用于不同月份(甚至不同年份)的日期,则可以使用MySQL DATEDIFF()函数。

Examples:

mysql> select datediff('2009-04-25','2009-01-01');
+-------------------------------------+
| datediff('2009-04-25','2009-01-01') |
+-------------------------------------+
|                                 114 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff('2010-04-25','2009-01-01');
+-------------------------------------+
| datediff('2010-04-25','2009-01-01') |
+-------------------------------------+
|                                 479 |
+-------------------------------------+
1 row in set (0.00 sec)

#1