电子商务网站建设与管理思考与练习/小程序开发工具

STR_TO_DATE函数用于将字符串转变为日期/时间值，与DATE_FORMAT函数的作用是相反的。这个函数是Mysql的日期时间函数，对此，LightDB做了兼容，详细的文档可参考Mysql官网文档str_to_date。

函数说明：

-- srt:要格式化为日期的字符串(输入字符串) 
-- format:要使用的格式字符串
-- 返回值可以是DATETIME,DATE,TIME类型。返回何种类型由参数决定
STR_TO_DATE(str,format)

其中format格式化字符串含义说明如下：

Specifier	Description
%a	Abbreviated weekday name (Sun…Sat)
%b	Abbreviated month name (Jan…Dec)
%c	Month, numeric (0…12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00…31)
%e	Day of the month, numeric (0…31)
%f	Microseconds (000000…999999)
%H	Hour (00…23)
%h	Hour (01…12)
%I	Hour (01…12)
%i	Minutes, numeric (00…59)
%j	Day of year (001…366)
%k	Hour (0…23)
%l	Hour (1…12)
%M	Month name (January…December)
%m	Month, numeric (00…12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00…59)
%s	Seconds (00…59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00…53), where Sunday is the first day of the week; WEEK() mode 0
%u	Week (00…53), where Monday is the first day of the week; WEEK() mode 1
%V	Week (01…53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v	Week (01…53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W	Weekday name (Sunday…Saturday)
%w	Day of the week (0=Sunday…6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal % character
%x	x, for any “x” not listed above

根据str以及format输出日期/时间类型，其中如果含有日期以及时间，则返回DATETIME类型，如果仅含有日期则返回DATE类型，如果仅含有时间则返回TIME类型。示例如下：

• 包含日期以及时间：

-- 需要在mysql模式下
postgres@mysql=# show lightdb_dblevel_syntax_compatible_type ;lightdb_dblevel_syntax_compatible_type 
----------------------------------------MySql
(1 row)postgres@mysql=# select str_to_date('2022-05-26 11:30:59','%Y-%m-%d %H:%i:%s');str_to_date     
---------------------2022-05-26 11:30:59
(1 row)postgres@mysql=# SELECT STR_TO_DATE('Monday 7th November 2022 13:45:30','%W %D %M %Y %T');str_to_date     
---------------------2022-11-07 13:45:30
(1 row)postgres@mysql=# SELECT STR_TO_DATE('Nov 05 2022 02:30 PM','%b %d %Y %h:%i %p');str_to_date     
---------------------2022-11-05 14:30:00
(1 row)

• 仅含日期：

postgres@mysql=# select str_to_date('2022-05-26','%Y-%m-%d');str_to_date 
-------------2022-05-26
(1 row)postgres@mysql=# select str_to_date('August,5,2022', '%M,%e,%Y');str_to_date 
-------------2022-08-05
(1 row)postgres@mysql=# SELECT str_to_date('04/30/2004', '%m/%d/%Y');str_to_date 
-------------2004-04-30
(1 row)

• 仅含时间

postgres@mysql=# select str_to_date('09:00:59', '%h:%i:%s');str_to_date 
-------------09:00:59
(1 row)postgres@mysql=# select str_to_date('10,40,50', '%h,%i,%s');str_to_date 
-------------10:40:50
(1 row)postgres@mysql=# SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');str_to_date 
-------------09:30:17
(1 row)

另外，在Mysql中支持sql_mode，在no_zero_date下，不允许出现日期为zero，对此，在LightDB中，支持lightdb_sql_mode参数，在LightDB中，可设置

postgres@mysql=# show lightdb_sql_mode ;lightdb_sql_mode 
------------------(1 row)
-- 设置 lightdb sql mode 为no_zero_date
postgres@mysql=# set lightdb_sql_mode = 'no_zero_date';
SET
postgres@mysql=# show lightdb_sql_mode ;lightdb_sql_mode 
------------------no_zero_date
(1 row)

在lightdb sql mode为no_zero_date时，以下示例，返回NULL：

-- 非法日期
postgres@mysql=# SELECT str_to_date('9','%d');str_to_date 
-------------(1 row)
-- 非法日期
postgres@mysql=# SELECT str_to_date('1999','%Y%d');str_to_date 
-------------(1 row)postgres@mysql=# SELECT STR_TO_DATE('2022-12-11 11:12:26','%Y-%m');str_to_date 
-------------(1 row)

在LightDB中，如果lightdb_sql_mode没有设置no_zero_date时，返回的结果与mysql不同，因为LightDB不会返回一个类似1999-0-0这样不存在的非法日期，会默认返回一个合法日期。

set lightdb_sql_mode to '';
show lightdb_sql_mode;lightdb_sql_mode 
------------------(1 row)SELECT str_to_date('9','%m');   str_to_date  
---------------0001-09-01 BC
(1 row)SELECT str_to_date('1999','%Y%d');str_to_date 
-------------1999-01-01
(1 row)SELECT str_to_date('1999','%Y');    -- pg return 1999-01-01, mysql return 1999-00-00str_to_date 
-------------1999-01-01
(1 row)

在LightDB中，不支持str_to_date函数应用绑定变量，从用户使用的角度也不推荐这么用。同时，LightDB的错误处理更加准确严格，示例如下：

-- return error in lightdb, return null in mysql
set lightdb_sql_mode to 'no_zero_date';
show lightdb_sql_mode;lightdb_sql_mode 
------------------no_zero_date
(1 row)SELECT str_to_date('13','%m');
ERROR:  date/time field value out of range: "13"
SELECT str_to_date('32','%d');
ERROR:  date/time field value out of range: "32"
SELECT STR_TO_DATE('61','%s');
ERROR:  date/time field value out of range: "61"
SELECT STR_TO_DATE('25','%H');
ERROR:  date/time field value out of range: "25"
SELECT STR_TO_DATE('61','%i');
ERROR:  date/time field value out of range: "61"
set lightdb_sql_mode to '';
show lightdb_sql_mode;lightdb_sql_mode 
------------------(1 row)SELECT str_to_date('13','%m');
ERROR:  date/time field value out of range: "13"
SELECT str_to_date('32','%d');
ERROR:  date/time field value out of range: "32"
SELECT STR_TO_DATE('61','%s');
ERROR:  date/time field value out of range: "61"
SELECT STR_TO_DATE('25','%H');
ERROR:  date/time field value out of range: "25"
SELECT STR_TO_DATE('61','%i');
ERROR:  date/time field value out of range: "61"
-- test return null in any sql_mode
SELECT str_to_date(null ,'%Y-%m-%d');str_to_date 
-------------(1 row)