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

本文介绍: 从 23.4 开始 Li g htDB 支持 mysql 的 last _day 函数。目前Li g htDB 实现 last_day的与mysql 并不完成相同，会在示例中列举出不同点, 主要是以下几点：last_da y 用于获取指定日期所在月份的最后一天，下面是mysql中的介绍：

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

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

last_d ay 简介

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

Ta k es a date o r date time value and return s th e co rresp on di n g value f or th e las t day o f the month. Ret u rns 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不同点：

light db

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>