当前位置：首页 > article >正文

LightDB - 支持 last_day 函数[mysql兼容]

article 2025/2/25 19:14:22

从 23.4 开始 LightDB 支持 mysql 的 last_day 函数。
目前LightDB 实现last_day的与mysql 并不完成相同，会在示例中列举出不同点, 主要是以下几点：

对于错误日期mysql返回null，lightdb 是直接报错
对于一些特殊日期，lightdb 不支持
LightDB 只支持date 和text 类型（datetime 目前不支持）

last_day 简介

last_day 用于获取指定日期所在月份的最后一天，下面是mysql中的介绍：

Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid or NULL.

示例

lightdb@test_m=# select last_day('2000-02-05') as f1, last_day('2002-12-31') as f2,lightdb@test_m-# last_day('2003-04-01') as f3,
lightdb@test_m-# last_day('2001-01-01 01:01:01') as f4, last_day(NULL) as f5,
lightdb@test_m-# last_day('2001-02-12') as f6;
     f1     |     f2     |     f3     |     f4     | f5 |     f6     
------------+------------+------------+------------+----+------------
 2000-02-29 | 2002-12-31 | 2003-04-30 | 2001-01-31 |    | 2001-02-28
(1 row)

lightdb@test_m=# select last_day(cast('20220101' as date));
  last_day  
------------
 2022-01-31
(1 row)

报错及与mysql不同点：

lightdb

lightdb@test_m=# select last_day(cast('20220501' as datetime));
ERROR:  function last_day(timestamp without time zone) does not exist
LINE 1: select last_day(cast('20220501' as datetime));
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
lightdb@test_m=# select last_day (cast('2003-02-29' as date));
ERROR:  date/time field value out of range: "2003-02-29"
LINE 1: select last_day (cast('2003-02-29' as date));
                              ^
lightdb@test_m=# select last_day('2005-00-00');
ERROR:  date/time field value out of range: "2005-00-00"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  SQL function "last_day" statement 1
lightdb@test_m=# select last_day('2005-01-00'); -- 2005-01-31
ERROR:  date/time field value out of range: "2005-01-00"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  SQL function "last_day" statement 1
lightdb@test_m=# select last_day(0501);
ERROR:  invalid input syntax for type date: "501"
CONTEXT:  SQL function "last_day" statement 1
lightdb@test_m=# select last_day(20220501.1); 
ERROR:  invalid input syntax for type date: "20220501.1"
CONTEXT:  SQL function "last_day" statement 1
lightdb@test_m=#

mysql

mysql> select last_day(cast('20220501' as datetime));
+----------------------------------------+
| last_day(cast('20220501' as datetime)) |
+----------------------------------------+
| 2022-05-31                             |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select last_day (cast('2003-02-29' as date));
+---------------------------------------+
| last_day (cast('2003-02-29' as date)) |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2003-02-29' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql>  select last_day('2005-00-00');
+------------------------+
| last_day('2005-00-00') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select last_day('2005-01-00'); 
+------------------------+
| last_day('2005-01-00') |
+------------------------+
| 2005-01-31             |
+------------------------+
1 row in set (0.00 sec)

mysql> select last_day(0501);
+----------------+
| last_day(0501) |
+----------------+
| 2000-05-31     |
+----------------+
1 row in set (0.01 sec)

mysql> select last_day(20220501.1);
+----------------------+
| last_day(20220501.1) |
+----------------------+
| 2022-05-31           |
+----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect date value: '20220501.1' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql>