从 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>
