Doris–基础–13.2–SQl函数–日期函数
1、CONVERT_TZ(DATETIME dt, VARCHAR from_tz, VARCHAR to_tz)
转换datetime值dt，从 from_tz 转到 to_tz 时区，并返回的结果值。
如果参数无效该函数返回NULL。
MySQL [test_db]> select convert_tz('2019-08-01 13:21:03', 'Asia/Shanghai', 'America/Los_Angeles');+---------------------------------------------------------------------------+| convert_tz('2019-08-01 13:21:03', 'Asia/Shanghai', 'America/Los_Angeles') |+---------------------------------------------------------------------------+| 2019-07-31 22:21:03 |+---------------------------------------------------------------------------+1 row in set (0.01 sec)MySQL [test_db]> select convert_tz('2019-08-01 13:21:03', '+08:00', 'America/Los_Angeles');+--------------------------------------------------------------------+| convert_tz('2019-08-01 13:21:03', '+08:00', 'America/Los_Angeles') |+--------------------------------------------------------------------+| 2019-07-31 22:21:03|+--------------------------------------------------------------------+1 row in set (0.01 sec)2、CURDATE()
获取当前的日期，以DATE类型返回
MySQL [test_db]> SELECT CURDATE();+------------+| curdate()|+------------+| 2023-02-26 |+------------+1 row in set (0.00 sec)MySQL [test_db]> SELECT CURDATE() + 0;+----------------+| curdate() + 0|+----------------+| 20230226000000 |+----------------+1 row in set (0.00 sec)3、CURRENT_TIMESTAMP()
获得当前的时间，以Datetime类型返回
MySQL [test_db]> select current_timestamp();+---------------------+| current_timestamp() |+---------------------+| 2023-02-26 16:09:42 |+---------------------+1 row in set (0.00 sec)4、current_time()
获得当前的时间，以TIME类型返回
MySQL [test_db]> select current_time();+----------------+| current_time() |+----------------+| 16:09:58 |+----------------+1 row in set (0.00 sec)5、DATE_ADD(DATETIME date,INTERVAL expr type)
向日期添加指定的时间间隔。
date 参数是合法的日期表达式。
expr 参数是您希望添加的时间间隔。
type 参数可以是下列值：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
MySQL [test_db]> select date_add('2020-11-01 23:59:59', INTERVAL 2 DAY);+-------------------------------------------------+| date_add('2020-11-01 23:59:59', INTERVAL 2 DAY) |+-------------------------------------------------+| 2020-11-03 23:59:59 |+-------------------------------------------------+1 row in set (0.00 sec)6、DATE_FORMAT(DATETIME date, VARCHAR format)
将日期类型按照format的类型转化为字符串， 当前支持最大128字节的字符串，如果返回值长度超过128，则返回NULL
date 参数是合法的日期。
format 规定日期/时间的输出格式。
可以使用的格式有：%a | 缩写星期名%b | 缩写月名%c | 月，数值%D | 带有英文前缀的月中的天%d | 月的天，数值(00-31)%e | 月的天，数值(0-31)%f | 微秒%H | 小时 (00-23)%h | 小时 (01-12)%I | 小时 (01-12)%i | 分钟，数值(00-59)%j | 年的天 (001-366)%k | 小时 (0-23)%l | 小时 (1-12)%M | 月名%m | 月，数值(00-12)%p | AM 或 PM%r | 时间，12-小时(hh:mm:ss AM 或 PM)%S | 秒(00-59)%s | 秒(00-59)%T | 时间, 24-小时 (hh:mm:ss)%U | 周 (00-53) 星期日是一周的第一天%u | 周 (00-53) 星期一是一周的第一天%V | 周 (01-53) 星期日是一周的第一天，与 %X 使用%v | 周 (01-53) 星期一是一周的第一天，与 %x 使用%W | 星期名%w | 周的天 (0=星期日, 6=星期六)%X | 年，其中的星期日是周的第一天，4 位，与 %V 使用%x | 年，其中的星期一是周的第一天，4 位，与 %v 使用%Y | 年，4 位%y | 年，2 位%% | 用于表示 %MySQL [test_db]> select date_format('2020-10-04 22:23:00', '%W %M %Y');+------------------------------------------------+| date_format('2020-10-04 22:23:00', '%W %M %Y') |+------------------------------------------------+| 星期日 十月 2020 |+------------------------------------------------+1 row in set (0.00 sec)MySQL [test_db]> select date_format('2020-10-04 22:23:00', '%H:%i:%s');+------------------------------------------------+| date_format('2020-10-04 22:23:00', '%H:%i:%s') |+------------------------------------------------+| 22:23:00 |+------------------------------------------------+1 row in set (0.01 sec)MySQL [test_db]> select date_format('2020-10-04 22:23:00', '%D %y %a %d %m %b %j');+------------------------------------------------------------+| date_format('2020-10-04 22:23:00', '%D %y %a %d %m %b %j') |+------------------------------------------------------------+| 4th 20 Sun 04 10 Oct 278 |+------------------------------------------------------------+1 row in set (0.00 sec)MySQL [test_db]> select date_format('2020-10-04 22:23:00', '%H %k %I %r %T %S %w');+------------------------------------------------------------+| date_format('2020-10-04 22:23:00', '%H %k %I %r %T %S %w') |+------------------------------------------------------------+| 22 22 10 10:23:00 PM 22:23:00 00 0 |+------------------------------------------------------------+1 row in set (0.01 sec)MySQL [test_db]> select date_format('2020-01-01 00:00:00', '%X %V'); +---------------------------------------------+| date_format('2020-01-01 00:00:00', '%X %V') |+---------------------------------------------+| 2019 52 |+---------------------------------------------+1 row in set (0.01 sec)MySQL [test_db]> select date_format('2020-06-01', '%d');+------------------------------------------+| date_format('2020-06-01 00:00:00', '%d') |+------------------------------------------+| 01 |+------------------------------------------+1 row in set (0.00 sec)MySQL [test_db]> select date_format('2020-06-01', '%%%d');+--------------------------------------------+| date_format('2020-06-01 00:00:00', '%%%d') |+--------------------------------------------+| %01|+--------------------------------------------+1 row in set (0.00 sec)7、DATE_SUB(DATETIME date,INTERVAL expr type)
从日期减去指定的时间间隔
date 参数是合法的日期表达式。
expr 参数是您希望添加的时间间隔。
type 参数可以是下列值：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
MySQL [test_db]> select date_sub('2010-11-30 23:59:59', INTERVAL 2 DAY);+-------------------------------------------------+| date_sub('2010-11-30 23:59:59', INTERVAL 2 DAY) |+-------------------------------------------------+| 2010-11-28 23:59:59 |+-------------------------------------------------+1 row in set (0.01 sec)8、DATEDIFF(DATETIME expr1,DATETIME expr2)
计算expr1 – expr2，结果精确到天。
expr1 和 expr2 参数是合法的日期或日期/时间表达式。
注释：只有值的日期部分参与计算。
MySQL [test_db]> select datediff(CAST('2020-12-31 23:59:59' AS DATETIME), CAST('2020-12-30' AS DATETIME));+-----------------------------------------------------------------------------------+| datediff(CAST('2020-12-31 23:59:59' AS DATETIME), CAST('2020-12-30' AS DATETIME)) |+-----------------------------------------------------------------------------------+| 1 |+-----------------------------------------------------------------------------------+1 row in set (0.01 sec)MySQL [test_db]>MySQL [test_db]> select datediff(CAST('2020-11-30 23:59:59' AS DATETIME), CAST('2020-12-31' AS DATETIME));+-----------------------------------------------------------------------------------+| datediff(CAST('2020-11-30 23:59:59' AS DATETIME), CAST('2020-12-31' AS DATETIME)) |+-----------------------------------------------------------------------------------+| -31 |+-----------------------------------------------------------------------------------+1 row in set (0.00 sec)9、DAY(DATETIME date)
获得日期中的天信息，返回值范围从1-31。
参数为Date或者Datetime类型
MySQL [test_db]> select day('2020-01-31'); +----------------------------+| day('2020-01-31 00:00:00') |+----------------------------+| 31 |+----------------------------+1 row in set (0.00 sec) 10、DAYNAME(DATE)
返回日期对应的日期名字
参数为Date或者Datetime类型
MySQL [test_db]> select dayname('2020-02-03 00:00:00'); +--------------------------------+| dayname('2020-02-03 00:00:00') |+--------------------------------+| Monday |+--------------------------------+1 row in set (0.01 sec)11、DAYOFMONTH(DATETIME date)
获得日期中的天信息，返回值范围从1-31。
参数为Date或者Datetime类型
MySQL [test_db]> select dayofmonth('2020-01-31');+-----------------------------------+| dayofmonth('2020-01-31 00:00:00') |+-----------------------------------+|31 |+-----------------------------------+1 row in set (0.01 sec) 12、dayofweek(DATETIME date)
DAYOFWEEK函数返回日期的工作日索引值，即星期日为1，星期一为2，星期六为7
参数为Date或者Datetime类型或者可以cast为Date或者Datetime类型的数字
MySQL [test_db]> select dayofweek('2020-06-25');+----------------------------------+| dayofweek('2020-06-25 00:00:00') |+----------------------------------+|5 |+----------------------------------+1 row in set (0.01 sec) MySQL [test_db]> select dayofweek(cast(20190620 as date)); +-----------------------------------+| dayofweek(CAST(20190620 AS DATE)) |+-----------------------------------+| 5 |+-----------------------------------+1 row in set (0.01 sec)13、DAYOFYEAR(DATETIME date)
获得日期中对应当年中的哪一天。
参数为Date或者Datetime类型
MySQL [test_db]> select dayofyear('2020-02-03 00:00:00');+----------------------------------+| dayofyear('2020-02-03 00:00:00') |+----------------------------------+| 34 |+----------------------------------+1 row in set (0.00 sec)14、FROM_DAYS(INT N)
通过距离0000-01-01日的天数计算出哪一天
MySQL [test_db]> select from_days(730669);+-------------------+| from_days(730669) |+-------------------+| 2000-07-03|+-------------------+1 row in set (0.01 sec)15、FROM_UNIXTIME(INT unix_timestamp[, VARCHAR string_format])
将 unix 时间戳转化为对应的 time 格式，返回的格式由 string_format 指定
默认为 yyyy-MM-dd HH:mm:ss ,也支持date_format中的format格式
传入的是整形，返回的是字符串类型
目前 string_format 支持格式：
%Y：年。例：2014，1900%m：月。例：12，09%d：日。例：11，01%H：时。例：23，01，12%i：分。例：05，11%s：秒。例：59，01其余 string_format 格式是非法的，返回NULL
如果给定的时间戳小于 0 或大于 253402271999，则返回 NULL。即时间戳范围是：
1970-01-01 00:00:00 ~ 9999-12-31 23:59:59
MySQL [test_db]> select from_unixtime(1196440219);+---------------------------+| from_unixtime(1196440219) |+---------------------------+| 2007-12-01 00:30:19 |+---------------------------+1 row in set (0.00 sec)MySQL [test_db]> select from_unixtime(1196440219, 'yyyy-MM-dd HH:mm:ss');+------------------------------------------------+| from_unixtime(1196440219, '%Y-%m-%d %H:%i:%s') |+------------------------------------------------+| 2007-12-01 00:30:19|+------------------------------------------------+1 row in set (0.00 sec)MySQL [test_db]> select from_unixtime(1196440219, '%Y-%m-%d');+---------------------------------------+| from_unixtime(1196440219, '%Y-%m-%d') |+---------------------------------------+| 2007-12-01|+---------------------------------------+1 row in set (0.00 sec)MySQL [test_db]> select from_unixtime(1196440219, '%Y-%m-%d %H:%i:%s');+------------------------------------------------+| from_unixtime(1196440219, '%Y-%m-%d %H:%i:%s') |+------------------------------------------------+| 2007-12-01 00:30:19|+------------------------------------------------+1 row in set (0.00 sec)16、HOUR(DATETIME date)
获得日期中的小时的信息，返回值范围从0-23。
参数为Date或者Datetime类型
MySQL [test_db]> select hour('2018-12-31 23:59:59');+-----------------------------+| hour('2018-12-31 23:59:59') |+-----------------------------+|23 |+-----------------------------+1 row in set (0.00 sec)17、MINUTE(DATETIME date)
获得日期中的分钟的信息，返回值范围从0-59。
参数为Date或者Datetime类型
MySQL [test_db]> select minute('2018-12-31 23:59:59');+-------------------------------+| minute('2018-12-31 23:59:59') |+-------------------------------+|59 |+-------------------------------+1 row in set (0.00 sec)18、MONTH(DATETIME date)
返回时间类型中的月份信息，范围是1, 12
参数为Date或者Datetime类型
MySQL [test_db]> select month('1987-01-01');+------------------------------+| month('1987-01-01 00:00:00') |+------------------------------+|1 |+------------------------------+1 row in set (0.00 sec)19、MONTHNAME(DATE)
返回日期对应的月份名字
参数为Date或者Datetime类型
MySQL [test_db]> select monthname('2008-02-03 00:00:00');+----------------------------------+| monthname('2008-02-03 00:00:00') |+----------------------------------+| February |+----------------------------------+1 row in set (0.01 sec)20、NOW()
获得当前的时间，以Datetime类型返回
MySQL [test_db]> select now();+---------------------+| now() |+---------------------+| 2023-02-26 16:19:27 |+---------------------+1 row in set (0.00 sec)21、SECOND(DATETIME date)
获得日期中的秒的信息，返回值范围从0-59。
参数为Date或者Datetime类型
MySQL [test_db]> select second('2018-12-31 23:59:59');+-------------------------------+| second('2018-12-31 23:59:59') |+-------------------------------+|59 |+-------------------------------+1 row in set (0.00 sec)22、STR_TO_DATE(VARCHAR str, VARCHAR format)
通过format指定的方式将str转化为DATE类型，如果转化结果不对返回NULL
支持的format格式与date_format一致
MySQL [test_db]> select str_to_date('2020-12-21 12:34:56', '%Y-%m-%d %H:%i:%s');+---------------------------------------------------------+| str_to_date('2020-12-21 12:34:56', '%Y-%m-%d %H:%i:%s') |+---------------------------------------------------------+| 2020-12-21 12:34:56 |+---------------------------------------------------------+1 row in set (0.00 sec)MySQL [test_db]> select str_to_date('2020-12-21 12:34%3A56', '%Y-%m-%d %H:%i%%3A%s');+--------------------------------------------------------------+| str_to_date('2020-12-21 12:34%3A56', '%Y-%m-%d %H:%i%%3A%s') |+--------------------------------------------------------------+| 2020-12-21 12:34:56|+--------------------------------------------------------------+1 row in set (0.01 sec)MySQL [test_db]> select str_to_date('202042 Monday', '%X%V %W');+-----------------------------------------+| str_to_date('202042 Monday', '%X%V %W') |+-----------------------------------------+| 2020-10-19|+-----------------------------------------+1 row in set (0.00 sec)MySQL [test_db]> select str_to_date("2020-09-01", "%Y-%m-%d %H:%i:%s");+------------------------------------------------+| str_to_date('2020-09-01', '%Y-%m-%d %H:%i:%s') |+------------------------------------------------+| 2020-09-01 00:00:00|+------------------------------------------------+1 row in set (0.00 sec)23、TIMEDIFF(DATETIME expr1, DATETIME expr2)
TIMEDIFF返回两个DATETIME之间的差值
TIMEDIFF函数返回表示为时间值的expr1 – expr2的结果，返回值为TIME类型
MySQL [test_db]> SELECT TIMEDIFF(now(),utc_timestamp());+----------------------------------+| timediff(now(), utc_timestamp()) |+----------------------------------+| 08:00:00 |+----------------------------------+1 row in set (0.00 sec)MySQL [test_db]> SELECT TIMEDIFF('2020-07-11 16:59:30','2019-07-11 16:59:21');+--------------------------------------------------------+| timediff('2020-07-11 16:59:30', '2019-07-11 16:59:21') |+--------------------------------------------------------+| 8784:00:09 |+--------------------------------------------------------+1 row in set (0.00 sec)MySQL [test_db]> SELECT TIMEDIFF('2020-01-01 00:00:00', NULL);+---------------------------------------+| timediff('2020-01-01 00:00:00', NULL) |+---------------------------------------+| NULL|+---------------------------------------+1 row in set (0.01 sec)24、TIMESTAMPADD(unit, interval, DATETIME datetime_expr)
将整数表达式间隔添加到日期或日期时间表达式datetime_expr中。
interval的单位由unit参数给出，它应该是下列值之一:
SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, or YEAR。
MySQL [test_db]> SELECT TIMESTAMPADD(MINUTE,1,'2020-01-02');+------------------------------------------------+| timestampadd(MINUTE, 1, '2020-01-02 00:00:00') |+------------------------------------------------+| 2020-01-02 00:01:00|+------------------------------------------------+1 row in set (0.01 sec)MySQL [test_db]> SELECT TIMESTAMPADD(WEEK,1,'2020-01-02');+----------------------------------------------+| timestampadd(WEEK, 1, '2020-01-02 00:00:00') |+----------------------------------------------+| 2020-01-09 00:00:00|+----------------------------------------------+1 row in set (0.01 sec)25、TIMESTAMPDIFF(unit,DATETIME datetime_expr1, DATETIME datetime_expr2)
返回datetime_expr2−datetime_expr1，其中datetime_expr1和datetime_expr2是日期或日期时间表达式。
结果(整数)的单位由unit参数给出。interval的单位由unit参数给出，它应该是下列值之一:
SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, or YEAR
MySQL [test_db]> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');+--------------------------------------------------------------------+| timestampdiff(MONTH, '2003-02-01 00:00:00', '2003-05-01 00:00:00') |+--------------------------------------------------------------------+|3 |+--------------------------------------------------------------------+1 row in set (0.00 sec)MySQL [test_db]> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');+-------------------------------------------------------------------+| timestampdiff(YEAR, '2002-05-01 00:00:00', '2001-01-01 00:00:00') |+-------------------------------------------------------------------+|-1 |+-------------------------------------------------------------------+1 row in set (0.01 sec)MySQL [test_db]> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');+---------------------------------------------------------------------+| timestampdiff(MINUTE, '2003-02-01 00:00:00', '2003-05-01 12:05:55') |+---------------------------------------------------------------------+|128885 |+---------------------------------------------------------------------+1 row in set (0.01 sec)26、TO_DAYS(DATETIME date)
返回date距离0000-01-01的天数
参数为Date或者Datetime类型
MySQL [test_db]> select to_days('2007-10-07');+-----------------------+| to_days('2007-10-07') |+-----------------------+|733321 |+-----------------------+1 row in set (0.01 sec)27、UNIX_TIMESTAMP()
将 Date 或者 Datetime 类型转化为 unix 时间戳。
如果没有参数，则是将当前的时间转化为时间戳。
参数需要是 Date 或者 Datetime 类型。
对于在 1970-01-01 00:00:00 之前或 2038-01-19 03:14:07 之后的时间，该函数将返回 0。
Format 的格式请参阅 date_format 函数的格式说明。
该函数受时区影响。
MySQL [test_db]> select unix_timestamp();+------------------+| unix_timestamp() |+------------------+| 1677399817 |+------------------+1 row in set (0.00 sec)MySQL [test_db]> select unix_timestamp('2007-11-30 10:30:19');+---------------------------------------+| unix_timestamp('2007-11-30 10:30:19') |+---------------------------------------+|1196389819 |+---------------------------------------+1 row in set (0.01 sec)MySQL [test_db]> select unix_timestamp('2007-11-30 10:30-19', '%Y-%m-%d %H:%i-%s');+------------------------------------------------------------+| unix_timestamp('2007-11-30 10:30-19', '%Y-%m-%d %H:%i-%s') |+------------------------------------------------------------+| 1196389819 |+------------------------------------------------------------+1 row in set (0.00 sec)MySQL [test_db]> select unix_timestamp('2007-11-30 10:30%3A19', '%Y-%m-%d %H:%i%%3A%s');+-----------------------------------------------------------------+| unix_timestamp('2007-11-30 10:30%3A19', '%Y-%m-%d %H:%i%%3A%s') |+-----------------------------------------------------------------+|1196389819 |+-----------------------------------------------------------------+1 row in set (0.01 sec)MySQL [test_db]> select unix_timestamp('1969-01-01 00:00:00');+---------------------------------------+| unix_timestamp('1969-01-01 00:00:00') |+---------------------------------------+| 0 |+---------------------------------------+1 row in set (0.00 sec)28、UTC_TIMESTAMP()
返回当前UTC日期和时间在 “YYYY-MM-DD HH:MM:SS” 或
“YYYYMMDDHHMMSS”格式的一个值
根据该函数是否用在字符串或数字语境中
MySQL [test_db]> select utc_timestamp(),utc_timestamp() + 1;+---------------------+---------------------+| utc_timestamp() | utc_timestamp() + 1 |+---------------------+---------------------+| 2023-02-26 08:24:03 |20230226082404 |+---------------------+---------------------+1 row in set (0.00 sec)29、WEEKOFYEAR(DATETIME date)
获得一年中的第几周
参数为Date或者Datetime类型
MySQL [test_db]> select weekofyear('2023-02-20 00:00:00');+-----------------------------------+| weekofyear('2023-02-20 00:00:00') |+-----------------------------------+| 8 |+-----------------------------------+1 row in set (0.00 sec)30、YEAR(DATETIME date)
返回date类型的year部分，范围从1000-9999
参数为Date或者Datetime类型
MySQL [test_db]> select year('1987-01-01');+-----------------------------+| year('1987-01-01 00:00:00') |+-----------------------------+|1987 |+-----------------------------+1 row in set (0.00 sec)