LightDB - datediff 函数增强[mysql兼容]

LightDB在 23.4 版本对原先支持的mysql的datediff函数进行了增强,由原先只支持如下函数匹配:

DATEDIFF(expr1 timestamp,expr2 timestamp) RETURN integer
DATEDIFF(expr1 text,expr2 text) RETURN integer

扩展到支持如下函数匹配:

DATEDIFF(expr1 timestamp,expr2 timestamp) RETURN integer
DATEDIFF(expr1 text,expr2 text) RETURN integer
DATEDIFF(expr1 timestamptz,expr2 timestamptz) RETURN integer
DATEDIFF(expr1 timestamptz,expr2 timestamp) RETURN integer
DATEDIFF(expr1 timestamp,expr2 timestamptz) RETURN integer
DATEDIFF(expr1 date, expr2 date) RETURN integer

datediff 简介

datediff 用于计算两个日期的差值,下面是mysql中对其的介绍:

DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

注意点

  • 在LightDB 中不支持time 类型, mysql支持(在mysql中time类型可以大于24小时,因此差值可以不为0)
  • LightDB 对于错误格式的日期会报错,mysql 会截断,并warning

示例

lightdb:

lightdb@test_m=# SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
 datediff 
----------
        1
(1 row)

lightdb@test_m=# SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
 datediff 
----------
      -31
(1 row)

lightdb@test_m=# SELECT DATEDIFF(cast('2007-12-31 23:59:59' as datetime),cast('2007-12-30' as datetime));
 datediff 
----------
        1
(1 row)

lightdb@test_m=# SELECT DATEDIFF(cast('2007-12-31 23:59:59' as date),cast('2007-12-30' as date));
 datediff 
----------
        1
(1 row)

lightdb@test_m=# SELECT DATEDIFF(cast('10:00:00' as time),cast('11:00:00' as time));
ERROR:  function datediff(time without time zone, time without time zone) does not exist
LINE 1: SELECT DATEDIFF(cast('10:00:00' as time),cast('11:00:00' as ...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
lightdb@test_m=# 
lightdb@test_m=# SELECT DATEDIFF(20121212,20121215);
 datediff 
----------
       -3
(1 row)

lightdb@test_m=# SELECT DATEDIFF(20121212.5,20121215.1);
ERROR:  invalid input syntax for type date: "20121212.5"
CONTEXT:  SQL function "datediff" statement 1
lightdb@test_m=# 

mysql:

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
+----------------------------------------------+
| DATEDIFF('2010-11-30 23:59:59','2010-12-31') |
+----------------------------------------------+
|                                          -31 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(cast('2007-12-31 23:59:59' as datetime),cast('2007-12-30' as datetime));
+----------------------------------------------------------------------------------+
| DATEDIFF(cast('2007-12-31 23:59:59' as datetime),cast('2007-12-30' as datetime)) |
+----------------------------------------------------------------------------------+
|                                                                                1 |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(cast('2007-12-31 23:59:59' as date),cast('2007-12-30' as date));
+--------------------------------------------------------------------------+
| DATEDIFF(cast('2007-12-31 23:59:59' as date),cast('2007-12-30' as date)) |
+--------------------------------------------------------------------------+
|                                                                        1 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(cast('10:00:00' as time),cast('11:00:00' as time));
+-------------------------------------------------------------+
| DATEDIFF(cast('10:00:00' as time),cast('11:00:00' as time)) |
+-------------------------------------------------------------+
|                                                           0 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(cast('100:00:00' as time),cast('11:00:00' as time));
+--------------------------------------------------------------+
| DATEDIFF(cast('100:00:00' as time),cast('11:00:00' as time)) |
+--------------------------------------------------------------+
|                                                            4 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(20121212,20121215);
+-----------------------------+
| DATEDIFF(20121212,20121215) |
+-----------------------------+
|                          -3 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(20121212.5,20121215.1);
+---------------------------------+
| DATEDIFF(20121212.5,20121215.1) |
+---------------------------------+
|                              -3 |
+---------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect date value: '20121212.5' |
| Warning | 1292 | Truncated incorrect date value: '20121215.1' |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)

mysql> 


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
DATEDIFF函数MySQL中的一个日期函数,用于计算两个日期之间的天数差。它的语法是DATEDIFF(date1, date2),其中date1和date2是要比较的两个日期。\[2\]\[3\] 例如,如果我们要计算从2017年8月8日到2017年8月17日之间的天数差,可以使用以下语句: SELECT DATEDIFF('2017-08-17','2017-08-08'); 这将返回结果9,表示两个日期之间相差9天。\[2\] 需要注意的是,DATEDIFF函数的结果可以是负数,表示第一个日期在第二个日期之后。例如,如果我们将日期顺序颠倒,计算从2017年8月17日到2017年8月8日之间的天数差,可以使用以下语句: SELECT DATEDIFF('2017-08-08','2017-08-17'); 这将返回结果-9,表示第一个日期在第二个日期之后,相差9天。\[3\] #### 引用[.reference_title] - *1* [MySQL Datediff函数](https://blog.csdn.net/weixin_45659364/article/details/115438315)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* *3* [MySQL datediff()函数](https://blog.csdn.net/moakun/article/details/82290269)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

紫无之紫

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值