时间和日期函数
更新时间：2022-09-13
PALO 支持的时间类型 包括 DATE 和 DATETIME 两种类型。
- DATE 类型格式为："2020-10-10"
- DATETIME 类型格式为："2020-10-10 11:10:06"
PALO 支持的日期和时间函数如下：
1.add_months
2.adddate
3.convert_tz
4.curdate,current_date
5.current_timestamp
6.curtime,current_time
7.date_add
8.date_sub
9.date_format
10.datediff
11.day,dayofmonth
12.dayname
13.dayofweek
14.dayofyear
15.days_add
16.days_diff
17.days_sub
18.extract
19.from_days
20.from_unixtime
21.unix_timestamp
22.utc_timestamp
23.hour
24.hours_add
25.hours_diff
26.hours_sub
27.localtime,localtimestamp
28.microseconds_add
29.microseconds_sub
30.minute
31.minutes_add
32.minutes_diff
33.minutes_sub
34.month
35.monthname
36.months_add
37.months_diff
38.months_sub
39.now
40.second
41.seconds_add
42.seconds_diff
43.seconds_sub
44.subdate
45.str_to_date
46.time_round
47.timediff
48.timestampadd
49.timestampdiff
50.to_date
51.to_days
52.weeks_add
53.weekofyear
54.weeks_diff
55.weeks_sub
56.quarter
57.year
58.year_floor
59.years_add
60.years_diff
61.years_sub
62.makedate
63.week
64.yearweek
ADD_MONTHS
Description
SQL
1add_months(timestamp date, int months)
2
3add_months(timestamp date, bigint months)- 功能：返回指定date加上months个月的新date。和months_add()相同
- 返回类型：timestamp类型
Example
如果这个月的这一日并不存在于目标月中，那么结果将是那个月的最后一天；如果参数中的months是负数，则是求先前的月。
SQL
1 mysql> select now(), add_months(now(), 2);
2 +---------------------+---------------------+
3 | now() | add_months(now(), 2)|
4 +---------------------+---------------------+
5 | 2016-05-31 10:47:00 | 2016-07-31 10:47:00 |
6 +---------------------+---------------------+
7 1 row in set (0.01 sec)
8
9 mysql> select now(), add_months(now(), 1);
10 +---------------------+---------------------+
11 | now() | add_months(now(), 1)|
12 +---------------------+---------------------+
13 | 2016-05-31 10:47:14 | 2016-06-30 10:47:14 |
14 +---------------------+---------------------+
15 1 row in set (0.01 sec)
16
17 mysql> select now(), add_months(now(), -1);
18 +---------------------+----------------------+
19 | now() | add_months(now(), -1)|
20 +---------------------+----------------------+
21 | 2016-05-31 10:47:31 | 2016-04-30 10:47:31 |
22 +---------------------+----------------------+
23 1 row in set (0.01 sec)Keywords
Plain Text
1add_monthsADDDATE
Description
SQL
1adddate(timestamp startdate, int days)
2
3adddate(timestamp startdate, bigint days)- 功能：给startdate加上指定的天数
- 返回类型：timestamp类型
Example
SQL
1 mysql> select adddate(date_column, 10) from big_table limit 1;
2 +-------------------------------+
3 | adddate(date_column, 10) |
4 +-------------------------------+
5 | 2014-01-11 00:00:00 |
6 +-------------------------------+Keywords
Plain Text
1adddateCONVERT_TZ
Description
SQL
1convert_tz(timestamp date, string from, string to)- 功能：转化指定时间的时区
- 返回类型：timestamp类型
Example
SQL
1mysql> select convert_tz('2020-12-20 12:00:00','+00:00','+10:00');
2+-------------------------------------------------------+
3| convert_tz('2020-12-20 12:00:00', '+00:00', '+10:00') |
4+-------------------------------------------------------+
5| 2020-12-20 22:00:00 |
6+-------------------------------------------------------+
71 row in set (0.09 sec)Keywords
Plain Text
1convert_tz CURDATE,CURRENT_DATE
Description
SQL
1curdate()
2current_date()- 功能：获取当前的日期
- 返回类型：timestamp类型
Example
SQL
1mysql> select curdate();
2+------------+
3| curdate() |
4+------------+
5| 2020-12-25 |
6+------------+
71 row in set (0.03 sec)
8
9mysql> select current_date();
10+----------------+
11| current_date() |
12+----------------+
13| 2020-12-25 |
14+----------------+
151 row in set (0.02 sec)Keywords
Plain Text
1curdate,current_dateCURRENT_TIMESTAMP
Description
SQL
1current_timestamp()- 功能：和now()函数功能相同，获取当前的时间
- 返回类型：timestamp类型
Example
SQL
1mysql> select current_timestamp();
2+---------------------+
3| current_timestamp() |
4+---------------------+
5| 2020-12-25 14:13:10 |
6+---------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1CURRENT_TIMESTAMPCURTIME,CURRENT_TIME
Description
SQL
1curtime()
2current_time()- 功能：获取当前的时间，不包含日期字段
- 返回类型：timestamp类型
Example
SQL
1mysql> select curtime();
2+-----------+
3| curtime() |
4+-----------+
5| 14:24:07 |
6+-----------+
71 row in set (0.02 sec)
8
9mysql> select current_time();
10+----------------+
11| current_time() |
12+----------------+
13| 14:24:22 |
14+----------------+
151 row in set (0.08 sec)Keywords
Plain Text
1curtime,current_timeDATE_ADD
Description
SQL
1date_add(timestamp startdate, int days)- 功能：给TIMESTAMP值加上指定的天数。第一个参数可以是字符串，如果字符串符合TIMESTAMP数据类型的格式，该字符串会自动转成TIMESTAMP类型。第二个参数是时间间隔。
- 返回类型：timestamp类型
Example
SQL
1mysql> select date_add('2020-12-25',20);
2+-------------------------------------+
3| date_add('2020-12-25 00:00:00', 20) |
4+-------------------------------------+
5| 2021-01-14 00:00:00 |
6+-------------------------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1date_add
2 DATE_SUB
Description
SQL
1date_sub(timestamp startdate, int days)- 功能：给TIMESTAMP值减去指定的天数。第一个参数可以是字符串，如果字符串符合TIMESTAMP数据类型的格式，该字符串会自动转成成TIMESTAMP类型。第二个参数是时间间隔。
- 返回类型：timestamp类型
Example
SQL
1mysql> select date_sub('2020-12-25',20);
2+-------------------------------------+
3| date_sub('2020-12-25 00:00:00', 20) |
4+-------------------------------------+
5| 2020-12-05 00:00:00 |
6+-------------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1date_subDATE_FORMAT
Description
SQL
1date_format(timestamp day, string fmt)- 功能：将日期类型按照format的类型转化为字符串，当前支持最大128字节的字符串，如果返回长度超过128，则返回NULL。
- 返回类型：string类型
-
format的含义如下：
Plain Text1%a Abbreviated weekday name (Sun..Sat) 2%b Abbreviated month name (Jan..Dec) 3%c Month, numeric (0..12) 4%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) 5%d Day of the month, numeric (00..31) 6%e Day of the month, numeric (0..31) 7%f Microseconds (000000..999999) 8%H Hour (00..23) 9%h Hour (01..12) 10%I Hour (01..12) 11%i Minutes, numeric (00..59) 12%j Day of year (001..366) 13%k Hour (0..23) 14%l Hour (1..12) 15%M Month name (January..December) 16%m Month, numeric (00..12) 17%p AM or PM 18%r Time, 12-hour (hh:mm:ss followed by AM or PM) 19%S Seconds (00..59) 20%s Seconds (00..59) 21%T Time, 24-hour (hh:mm:ss) 22%U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0 23%u Week (00..53), where Monday is the first day of the week; WEEK() mode 1 24%V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X 25%v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x 26%W Weekday name (Sunday..Saturday) 27%w Day of the week (0=Sunday..6=Saturday) 28%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V 29%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v 30%Y Year, numeric, four digits 31%y Year, numeric (two digits) 32%% A literal “%” character 33%x x, for any “x” not listed above
Example
SQL
1mysql> select date_format('2009-10-04 22:23:00', '%W %M %Y');
2+------------------------------------------------+
3| date_format('2009-10-04 22:23:00', '%W %M %Y') |
4+------------------------------------------------+
5| Sunday October 2009 |
6+------------------------------------------------+
71 row in set (0.01 sec)
8
9mysql> select date_format('2007-10-04 22:23:00', '%H:%i:%s');
10+------------------------------------------------+
11| date_format('2007-10-04 22:23:00', '%H:%i:%s') |
12+------------------------------------------------+
13| 22:23:00 |
14+------------------------------------------------+
151 row in set (0.01 sec)
16
17mysql> select date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
18+------------------------------------------------------------+
19| date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
20+------------------------------------------------------------+
21| 4th 00 Thu 04 10 Oct 277 |
22+------------------------------------------------------------+
231 row in set (0.03 sec)
24
25mysql> select date_format('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
26+------------------------------------------------------------+
27| date_format('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
28+------------------------------------------------------------+
29| 22 22 10 10:23:00 PM 22:23:00 00 6 |
30+------------------------------------------------------------+
311 row in set (0.00 sec)Keywords
Plain Text
1date_formatDATEDIFF
Description
SQL
1datediff(string enddate, string startdate)- 功能：返回两个日期的天数差值
- 返回类型：int类型
Example
SQL
1mysql> select datediff('2020-12-25','2019-11-20');
2+--------------------------------------------------------+
3| datediff('2020-12-25 00:00:00', '2019-11-20 00:00:00') |
4+--------------------------------------------------------+
5| 401 |
6+--------------------------------------------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1datediffDAY,DAYOFMONTH
Description
SQL
1day(string date)
2dayofmonth(string date)- 功能：返回日期中的天字段
- 返回类型：int类型
Example
SQL
1mysql> select day('2020-12-25');
2+----------------------------+
3| day('2020-12-25 00:00:00') |
4+----------------------------+
5| 25 |
6+----------------------------+
71 row in set (0.02 sec)
8
9mysql> select dayofmonth('2020-12-25');
10+-----------------------------------+
11| dayofmonth('2020-12-25 00:00:00') |
12+-----------------------------------+
13| 25 |
14+-----------------------------------+
151 row in set (0.07 sec)Keywords
Plain Text
1day,dayofmonthDAYNAME
Description
SQL
1dayname(timestamp date)- 功能：返回指定日期是星期几（英文）
- 返回类型：字符串类型
Example
SQL
1mysql> select dayname('2020-12-25');
2+--------------------------------+
3| dayname('2020-12-25 00:00:00') |
4+--------------------------------+
5| Friday |
6+--------------------------------+
71 row in set (0.04 sec)Keywords
Plain Text
1dayname DAYOFWEEK
Description
SQL
1dayofweek(timestamp date)-
功能：返回指定日期为星期几（数字）
说明: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.
- 返回类型：int类型
Example
SQL
1mysql> select dayofweek('2020-12-25');
2+----------------------------------+
3| dayofweek('2020-12-25 00:00:00') |
4+----------------------------------+
5| 6 |
6+----------------------------------+
71 row in set (0.07 sec)Keywords
Plain Text
1dayofweekDAYOFYEAR
Description
SQL
1dayofyear(timestamp date)- 功能：返回指定日期是当年的第几天
- 返回类型：int类型
Example
SQL
1mysql> select dayofyear('2020-12-25');
2+----------------------------------+
3| dayofyear('2020-12-25 00:00:00') |
4+----------------------------------+
5| 360 |
6+----------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1dayofyear
2 DAYS_ADD
Description
SQL
1days_add(timestamp startdate, int days)
2
3days_add(timestamp startdate, bigint days)- 功能：给startdate加上指定的天数，和date_add函数相似，差别在于本函数的参数是TIMESTAMP类型而不是string类型。
- 返回类型：timestamp类型
Example
SQL
1mysql> select days_add('2020-12-25',10);
2+-------------------------------------+
3| days_add('2020-12-25 00:00:00', 10) |
4+-------------------------------------+
5| 2021-01-04 00:00:00 |
6+-------------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1days_addDAYS_DIFF
Description
SQL
1days_diff(timestamp enddate, timestamp startdate)- 功能：开始时间到结束时间相差几天
- 返回类型：int类型
Example
SQL
1mysql> select days_diff('2020-12-25','2020-10-1');
2+---------------------------------------------------------+
3| days_diff('2020-12-25 00:00:00', '2020-10-01 00:00:00') |
4+---------------------------------------------------------+
5| 85 |
6+---------------------------------------------------------+
71 row in set (0.06 sec)Keywords
Plain Text
1days_diffDAYS_SUB
Description
SQL
1days_sub(timestamp startdate, int days)
2
3days_sub(timestamp startdate, bigint days)- 功能：给startdate减去指定的天数，和date_sub函数相似，差别在于本函数的参数是TIMESTAMP类型而不是string类型。
- 返回类型：timestamp类型
Example
SQL
1mysql> select days_sub('2020-12-25',20);
2+-------------------------------------+
3| days_sub('2020-12-25 00:00:00', 20) |
4+-------------------------------------+
5| 2020-12-05 00:00:00 |
6+-------------------------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1days_subEXTRACT
Description
SQL
1extract(unit FROM timestamp)- 功能：提取timestamp某个指定单位的值。单位可以为year, month, day, hour, minute或者second
- 返回类型：int类型
Example
SQL
1mysql> select now() as right_now,
2 -> extract(year from now()) as this_year,
3 -> extract(month from now()) as this_month;
4+---------------------+-----------+------------+
5| right_now | this_year | this_month |
6+---------------------+-----------+------------+
7| 2020-12-25 18:27:13 | 2020 | 12 |
8+---------------------+-----------+------------+
91 row in set (0.02 sec)
10
11mysql> select now() as right_now,
12 -> extract(day from now()) as this_day,
13 -> extract(hour from now()) as this_hour;
14+---------------------+----------+-----------+
15| right_now | this_day | this_hour |
16+---------------------+----------+-----------+
17| 2020-12-25 18:28:26 | 25 | 18 |
18+---------------------+----------+-----------+
191 row in set (0.02 sec)Keywords
Plain Text
1extractFROM_DAYS
Description
SQL
1from_days(int days)- 功能：返回从0000-00-00开始向后指定天数的日期
- 返回类型：timestamp
Example
SQL
1mysql> select from_days(10000);
2+------------------+
3| from_days(10000) |
4+------------------+
5| 0027-05-19 |
6+------------------+
71 row in set (0.09 sec)Keywords
Plain Text
1from_daysFROM_UNIXTIME
Description
SQL
1from_unixtime(bigint unixtime,[ string format])- 功能：将unix时间（自1970年1月1日起经过的秒数）转换成相应格式的日期类型
- 返回类型：字符串类型
- 使用说明：当前日期格式是大小写敏感的，用户尤其要区分小写m（表达分钟）和大写M（表达月份）。日期字符串的完整型式是"yyyy-MM-dd HH:mm:ss.SSSSSS"，也可以只包含其中部分字段。
Example
SQL
1 mysql> select from_unixtime(100000);
2 +-----------------------+
3 | from_unixtime(100000) |
4 +-----------------------+
5 | 1970-01-02 11:46:40 |
6 +-----------------------+
7 1 row in set (0.01 sec)
8
9 mysql> select from_unixtime(100000, 'yyyy-MM-dd');
10 +-------------------------------------+
11 | from_unixtime(100000, 'yyyy-MM-dd') |
12 +-------------------------------------+
13 | 1970-01-02 |
14 +-------------------------------------+
15 1 row in set (0.00 sec)
16
17
18 mysql> select from_unixtime(1392394861, 'yyyy-MM-dd');
19 +-----------------------------------------+
20 | from_unixtime(1392394861, 'yyyy-MM-dd') |
21 +-----------------------------------------+
22 | 2014-02-15 |
23 +-----------------------------------------+
24 1 row in set (0.00 sec)unix_timestamp()和from_unixtime()经常结合使用，将时间戳类型转换成指定格式的字符串。
SQL
1mysql> select from_unixtime(unix_timestamp(now()), 'yyyy-MM-dd');
2+--------------------------------------------------+
3| from_unixtime(unix_timestamp(now()), '%Y-%m-%d') |
4+--------------------------------------------------+
5| 2020-12-25 |
6+--------------------------------------------------+
71 row in set (0.02 sec)
8
9 mysql> select from_unixtime(unix_timestamp(now()), '%Y %D %M');
10+--------------------------------------------------+
11| from_unixtime(unix_timestamp(now()), '%Y %D %M') |
12+--------------------------------------------------+
13| 2020 25th December |
14+--------------------------------------------------+
151 row in set (0.03 sec)UNIX_TIMESTAMP
Description
SQL
1unix_timestamp()
2
3unix_timestamp(string datetime)
4
5unix_timestamp(string datetime, string format)
6
7unix_timestamp(timestamp datetime)- 功能：返回当前时间的时间戳（相对1970年1月1日的秒数）或者从一个指定的日期和时间转换成时间戳。返回的时间戳是相对于格林尼治时区的时间戳。
- 返回类型：bigint类型
Example
SQL
1mysql> select unix_timestamp();
2+------------------+
3| unix_timestamp() |
4+------------------+
5| 1608896139 |
6+------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1unix_timestampUTC_TIMESTAMP
Description
SQL
1utc_timestamp()- 功能：返回当前格林尼治时区的时间
- 返回类型：timestamp
Example
SQL
1mysql> select utc_timestamp();
2+---------------------+
3| utc_timestamp() |
4+---------------------+
5| 2020-12-25 11:39:30 |
6+---------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1utc_timestampHOUR
Description
SQL
1hour(string date)- 功能：返回字符串所表达日期的小时字段
- 返回类型：int类型
Example
SQL
1mysql> select hour('2020-12-25 23:46');
2+--------------------------+
3| hour('2020-12-25 23:46') |
4+--------------------------+
5| 23 |
6+--------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1hourHOURS_ADD
Description
SQL
1hours_add(timestamp date, int hours)
2
3hours_add(timestamp date, bigint hours)- 功能：返回指定的日期加上若干小时后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select hours_add('2020-12-25 18:00', 24);
2+-----------------------------------+
3| hours_add('2020-12-25 18:00', 24) |
4+-----------------------------------+
5| 2020-12-26 18:00:00 |
6+-----------------------------------+
71 row in set (0.06 sec)
8
9mysql> select hours_add(now(), 24);
10+----------------------+
11| hours_add(now(), 24) |
12+----------------------+
13| 2020-12-26 19:45:40 |
14+----------------------+
151 row in set (0.02 sec)Keywords
Plain Text
1hours_addHOURS_DIFF
Description
SQL
1hours_diff(timestamp enddate, timestamp startdate)- 功能：开始时间到结束时间相差几个小时
- 返回类型：int类型
Example
SQL
1mysql> select hours_diff('2020-12-26 08:00','2020-12-25 20:00');
2+----------------------------------------------------+
3| hours_diff('2020-12-26 08:00', '2020-12-25 20:00') |
4+----------------------------------------------------+
5| 12 |
6+----------------------------------------------------+
71 row in set (0.04 sec)Keywords
Plain Text
1hours_diffHOURS_SUB
Description
SQL
1hours_sub(timestamp date, int hours)
2
3hours_sub(timestamp date, bigint hours)- 功能：返回指定的日期减去若干小时后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select hours_sub('2020-12-25 20:00',12);
2+-----------------------------------+
3| hours_sub('2020-12-25 20:00', 12) |
4+-----------------------------------+
5| 2020-12-25 08:00:00 |
6+-----------------------------------+
71 row in set (0.05 sec)Keywords
Plain Text
1hours_subLOCALTIME,LOCALTIMESTAMP
Description
SQL
1localtime ()
2
3localtimestamp()- 功能：和now()函数功能相同，获取当前的时间
- 返回类型：timestamp类型
Example
SQL
1mysql> select localtime();
2+---------------------+
3| localtime() |
4+---------------------+
5| 2020-12-25 19:52:56 |
6+---------------------+
71 row in set (0.04 sec)
8
9mysql> select localtimestamp();
10+---------------------+
11| localtimestamp() |
12+---------------------+
13| 2020-12-25 19:53:10 |
14+---------------------+
151 row in set (0.02 sec)Keywords
Plain Text
1localtime,localtimestampMICROSECONDS_ADD
Description
SQL
1microseconds_add(timestamp date, int microseconds)
2
3microseconds_add(timestamp date, bigint microseconds)- 功能：返回指定的日期加上若干微秒后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select microseconds_add('2020-12-25',1000000);
2+--------------------------------------------------+
3| microseconds_add('2020-12-25 00:00:00', 1000000) |
4+--------------------------------------------------+
5| 2020-12-25 00:00:01 |
6+--------------------------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1microseconds_addMICROSECONDS_SUB
Description
SQL
1microseconds_sub(timestamp date, int microseconds)
2
3microseconds_sub(timestamp date, bigint microseconds)- 功能：返回指定的日期减去若干微秒后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select microseconds_sub('2020-12-25',1000000);
2+--------------------------------------------------+
3| microseconds_sub('2020-12-25 00:00:00', 1000000) |
4+--------------------------------------------------+
5| 2020-12-24 23:59:59 |
6+--------------------------------------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1microseconds_subMINUTE
Description
SQL
1minute(string date)- 功能：返回字符串所表达日期的分钟字段
- 返回类型：int类型
Example
SQL
1mysql> select minute('2020-12-25 20:25:35');
2+-------------------------------+
3| minute('2020-12-25 20:25:35') |
4+-------------------------------+
5| 25 |
6+-------------------------------+
71 row in set (0.04 sec)Keywords
Plain Text
1minuteMINUTES_ADD
Description
SQL
1minutes_add(timestamp date, int minutes)
2
3minutes_add(timestamp date, bigint minutes)- 功能：返回指定的日期加上若干分钟后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select minutes_add('2020-12-25 20:00:00',25);
2+----------------------------------------+
3| minutes_add('2020-12-25 20:00:00', 25) |
4+----------------------------------------+
5| 2020-12-25 20:25:00 |
6+----------------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1minutes_addMINUTES_DIFF
Description
SQL
1minutes_diff(timestamp enddate, timestamp startdate)- 功能：开始时间到结束时间相差几分钟
- 返回类型：int类型
Example
SQL
1mysql> select minutes_diff('2020-12-25 22:00:00','2020-12-25 21:00:00');
2+------------------------------------------------------------+
3| minutes_diff('2020-12-25 22:00:00', '2020-12-25 21:00:00') |
4+------------------------------------------------------------+
5| 60 |
6+------------------------------------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1minutes_diffMINUTES_SUB
Description
SQL
1minutes_sub(timestamp date, int minutes)
2
3minutes_sub(timestamp date, bigint minutes)- 功能：返回指定的日期减去若干分钟后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select minutes_sub('2020-12-25 20:00:00',25);
2+----------------------------------------+
3| minutes_sub('2020-12-25 20:00:00', 25) |
4+----------------------------------------+
5| 2020-12-25 19:35:00 |
6+----------------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1minutes_subMONTH
Description
SQL
1month(string date)- 功能：返回字符串所表达的日期的月份字段（数字）
- 返回类型：int类型
Example
SQL
1mysql> select month('2020-12-25');
2+------------------------------+
3| month('2020-12-25 00:00:00') |
4+------------------------------+
5| 12 |
6+------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1monthMONTHNAME
Description
SQL
1monthname(string date)- 功能：返回字符串所表达的日期的月份字段（英文）
- 返回类型：字符串类型
Example
SQL
1mysql> select monthname('2020-12-25');
2+----------------------------------+
3| monthname('2020-12-25 00:00:00') |
4+----------------------------------+
5| December |
6+----------------------------------+
71 row in set (0.02 sec)Keywords
MONTHS_ADD
Description
SQL
1months_add(timestamp date, int months)
2
3months_add(timestamp date, bigint months)- 功能：返回指定的日期加上若干月份后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select months_add('2020-12-25',2);
2+--------------------------------------+
3| months_add('2020-12-25 00:00:00', 2) |
4+--------------------------------------+
5| 2021-02-25 00:00:00 |
6+--------------------------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1months_addMONTHS_DIFF
Description
SQL
1months_diff(timestamp enddate, timestamp startdate)- 功能：开始时间到结束时间相差几个月
- 返回类型：int类型
Example
SQL
1mysql> select months_diff('2020-12-25','2020-05-25');
2+-----------------------------------------------------------+
3| months_diff('2020-12-25 00:00:00', '2020-05-25 00:00:00') |
4+-----------------------------------------------------------+
5| 7 |
6+-----------------------------------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1months_diffMONTHS_SUB
Description
SQL
1months_sub(timestamp date, int months)
2
3months_sub(timestamp date, bigint months)- 功能：返回指定的日期减去若干月份后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select months_sub('2020-12-25',2);
2+--------------------------------------+
3| months_sub('2020-12-25 00:00:00', 2) |
4+--------------------------------------+
5| 2020-10-25 00:00:00 |
6+--------------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1months_subNOW
Description
SQL
1now()- 功能：返回当前的日期和时间（东八区的时区）
- 返回类型：timestamp
Example
SQL
1mysql> select now();
2+---------------------+
3| now() |
4+---------------------+
5| 2020-12-25 20:36:03 |
6+---------------------+
71 row in set (0.20 sec)Keywords
Plain Text
1nowSECOND
Description
SQL
1second(string date)- 功能：返回字符串所表达的日期的秒字段
- 返回类型：int 类型
Example
SQL
1mysql> select now() as right_now,
2 -> second(now());
3+---------------------+---------------+
4| right_now | second(now()) |
5+---------------------+---------------+
6| 2020-12-25 20:44:25 | 25 |
7+---------------------+---------------+
81 row in set (0.03 sec)Keywords
Plain Text
1secondSECONDS_ADD
Description
SQL
1seconds_add(timestamp date, int seconds)
2
3seconds_add(timestamp date, bigint seconds)- 功能：返回指定的日期加上若干秒后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select now() as right_now,
2 -> seconds_add(now(),20);
3+---------------------+------------------------+
4| right_now | seconds_add(now(), 20) |
5+---------------------+------------------------+
6| 2020-12-25 20:45:07 | 2020-12-25 20:45:27 |
7+---------------------+------------------------+
81 row in set (0.02 sec)Keywords
Plain Text
1seconds_addSECONDS_DIFF
Description
SQL
1seconds_diff(timestamp enddate, timestamp startdate)- 功能：开始时间到结束时间相差几秒钟
- 返回类型：int类型
Example
SQL
1mysql> select seconds_diff('2020-12-25 08:00:00','2020-12-25 07:00:00');
2+------------------------------------------------------------+
3| seconds_diff('2020-12-25 08:00:00', '2020-12-25 07:00:00') |
4+------------------------------------------------------------+
5| 3600 |
6+------------------------------------------------------------+
71 row in set (0.05 sec)Keywords
Plain Text
1seconds_diffSECONDS_SUB
Description
SQL
1seconds_sub(timestamp date, int seconds)
2
3seconds_sub(timestamp date, bigint seconds)- 功能：返回指定的日期减去若干秒后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select now() as right_now,
2 -> seconds_sub(now(),20);
3+---------------------+------------------------+
4| right_now | seconds_sub(now(), 20) |
5+---------------------+------------------------+
6| 2020-12-25 20:46:10 | 2020-12-25 20:45:50 |
7+---------------------+------------------------+
81 row in set (0.03 sec)Keywords
Plain Text
1seconds_subSUBDATE
Description
SQL
1subdate(timestamp startdate, int days)
2
3subdate(timestamp startdate, bigint days)- 功能：从startdate的时间减去若干天后的时间。和date_sub()函数相似，但是本函数的第一个参数是确切的TIMESTAMP，而非可以转成TIMESTAMP类型的字符串。
- 返回类型：timestamp
Example
SQL
1mysql> select subdate('2020-12-25',10);
2+------------------------------------+
3| subdate('2020-12-25 00:00:00', 10) |
4+------------------------------------+
5| 2020-12-15 00:00:00 |
6+------------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1subdateSTR_TO_DATE
Description
SQL
1str_to_date(string str, string format)- 功能：通过format指定的方式将str转化为timestamp类型，如果转化结果不对返回NULL。支持的format格式与date_format一致。
- 返回类型：timestamp
Example
SQL
1mysql> select str_to_date('20201225 1130','%Y%m%d %h%i');
2+---------------------------------------------+
3| str_to_date('20201225 1130', '%Y%m%d %h%i') |
4+---------------------------------------------+
5| 2020-12-25 11:30:00 |
6+---------------------------------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1str_to_dateTIME_ROUND
Description
SQL
1DATETIME TIME_ROUND(DATETIME expr)
2DATETIME TIME_ROUND(DATETIME expr, INT period)
3DATETIME TIME_ROUND(DATETIME expr, DATETIME origin)
4DATETIME TIME_ROUND(DATETIME expr, INT period, DATETIME origin)- 功能：函数名 TIME_ROUND 由两部分组成，每部分由以下可选值组成 TIME: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR ROUND: FLOOR, CEIL 返回 expr 的上/下界。 period 指定每个周期有多少个 TIME 单位组成，默认为 1。 origin 指定周期的开始时间，默认为 1970-01-01T00:00:00，WEEK 的默认开始时间为 1970-01-04T00:00:00，即周日。可以比 expr 大。 请尽量选择常见 period，如 3 MONTH，90 MINUTE 等，如设置了非常用 period，请同时指定 origin。
- 返回类型：string类型
Example
SQL
1MySQL> SELECT YEAR_FLOOR('20200202000000');
2+------------------------------+
3| year_floor('20200202000000') |
4+------------------------------+
5| 2020-01-01 00:00:00 |
6+------------------------------+
7
8
9MySQL> SELECT MONTH_CEIL(CAST('2020-02-02 13:09:20' AS DATETIME), 3); --quarter
10+--------------------------------------------------------+
11| month_ceil(CAST('2020-02-02 13:09:20' AS DATETIME), 3) |
12+--------------------------------------------------------+
13| 2020-04-01 00:00:00 |
14+--------------------------------------------------------+
15
16
17MySQL> SELECT WEEK_CEIL('2020-02-02 13:09:20', '2020-01-06'); --monday
18+---------------------------------------------------------+
19| week_ceil('2020-02-02 13:09:20', '2020-01-06 00:00:00') |
20+---------------------------------------------------------+
21| 2020-02-03 00:00:00 |
22+---------------------------------------------------------+
23
24
25MySQL> SELECT MONTH_CEIL(CAST('2020-02-02 13:09:20' AS DATETIME), 3, CAST('1970-01-09 00:00:00' AS DATETIME)); --next rent day
26+-------------------------------------------------------------------------------------------------+
27| month_ceil(CAST('2020-02-02 13:09:20' AS DATETIME), 3, CAST('1970-01-09 00:00:00' AS DATETIME)) |
28+-------------------------------------------------------------------------------------------------+
29| 2020-04-09 00:00:00 |
30+-------------------------------------------------------------------------------------------------+Keywords
Plain Text
1TIME_ROUNDTIMEDIFF
Description
SQL
1timediff(string enddate, string startdate)- 功能：返回两个时间的时间戳差值
- 返回类型：int类型
Example
SQL
1mysql> select timediff('20201225','20201224');
2+--------------------------------------------------------+
3| timediff('2020-12-25 00:00:00', '2020-12-24 00:00:00') |
4+--------------------------------------------------------+
5| 24:00:00 |
6+--------------------------------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1timediffTIMESTAMPADD
Description
SQL
1TIMESTAMPADD(unit, interval, DATETIME datetime_expr)- 功能：将整数表达式间隔添加到日期或日期时间表达式datetime_expr中。 unit可选值：SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR
- 返回类型：string类型
Example
SQL
1mysql> select timestampadd(DAY, 1, '2021-07-02 12:31:44');
2+---------------------------------------------+
3| timestampadd(DAY, 1, '2021-07-02 12:31:44') |
4+---------------------------------------------+
5| 2021-07-03 12:31:44 |
6+---------------------------------------------+Keywords
Plain Text
1timestampaddTIMESTAMPDIFF
Description
SQL
1TIMESTAMPDIFF(unit,DATETIME datetime_expr1, DATETIME datetime_expr2)- 功能：返回datetime_expr2−datetime_expr1，其中datetime_expr1和datetime_expr2是日期或日期时间表达式。 结果(整数)的单位由unit参数给出。 unit可选值:SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR
- 返回类型：int类型
Example
SQL
1mysql> select timestampdiff(SECOND, '2021-07-01 12:31:44', '2021-07-02 12:31:44');
2+---------------------------------------------------------------------+
3| timestampdiff(SECOND, '2021-07-01 12:31:44', '2021-07-02 12:31:44') |
4+---------------------------------------------------------------------+
5| 86400 |
6+---------------------------------------------------------------------+Keywords
Plain Text
1timestampdiffTO_DATE
Description
SQL
1to_date(timestamp)- 功能：返回timestamp的date域
- 返回类型：string类型
Example
SQL
1mysql> select now() as right_now,
2 -> concat('The date today is ',to_date(now()),'.') as date_announcement;
3+---------------------+-------------------------------+
4| right_now | date_announcement |
5+---------------------+-------------------------------+
6| 2020-12-25 21:08:02 | The date today is 2020-12-25. |
7+---------------------+-------------------------------+
81 row in set (0.03 sec)Keywords
Plain Text
1to_dateTO_DAYS
Description
SQL
1to_days(timestamp date)- 功能：返回从0000-00-00开始到指定日期的天数
- 返回类型：int类型
Example
SQL
1mysql> select to_days('0000-12-25');
2+-----------------------+
3| to_days('0000-12-25') |
4+-----------------------+
5| 359 |
6+-----------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1to_daysWEEK_ADD
Description
SQL
1weeks_add(timestamp date, int weeks)
2
3weeks_add(timestamp date, bigint weeks)- 功能：返回指定的日期加上若干周后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select weeks_add('2020-12-25',3);
2+-------------------------------------+
3| weeks_add('2020-12-25 00:00:00', 3) |
4+-------------------------------------+
5| 2021-01-15 00:00:00 |
6+-------------------------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1weeks_addWEEKOFYEAR
Description
SQL
1weekofyear(timestamp date)- 功能：获得一年中的第几周
- 返回类型：int
Example
SQL
1mysql> select weekofyear('2020-12-25');
2+-----------------------------------+
3| weekofyear('2020-12-25 00:00:00') |
4+-----------------------------------+
5| 52 |
6+-----------------------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1weekofyearWEEK_DIFF
Description
SQL
1weeks_diff(timestamp enddate, timestamp startdate)- 功能：开始时间到结束时间相差几个星期
- 返回类型：int类型
Example
SQL
1mysql> select weeks_diff('2020-12-25','2020-10-25');
2+----------------------------------------------------------+
3| weeks_diff('2020-12-25 00:00:00', '2020-10-25 00:00:00') |
4+----------------------------------------------------------+
5| 8 |
6+----------------------------------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1weeks_diffWEEK_SUD
Description
SQL
1weeks_sub(timestamp date, int weeks)
2
3weeks_sub(timestamp date, bigint weeks)- 功能：返回指定的日期减去若干周后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select weeks_sub('2020-12-25',3);
2+-------------------------------------+
3| weeks_sub('2020-12-25 00:00:00', 3) |
4+-------------------------------------+
5| 2020-12-04 00:00:00 |
6+-------------------------------------+
71 row in set (0.28 sec)Keywords
Plain Text
1weeks_subQUARTER
Description
SQL
1quarter(timestamp date)- 功能：返回指定的日期所属季度
- 返回类型：int
Example
SQL
1mysql> select quarter('2020-12-25');
2+--------------------------------+
3| quarter('2020-12-25 00:00:00') |
4+--------------------------------+
5| 4 |
6+--------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1quarterYEAR
Description
SQL
1year(string date)- 功能：返回字符串所表达的日期的年字段
- 返回类型：int类型
Example
SQL
1mysql> select year('2020-12-25');
2+-----------------------------+
3| year('2020-12-25 00:00:00') |
4+-----------------------------+
5| 2020 |
6+-----------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1yearYEAR_FLOOR
Description
SQL
1year_floor(timestamp date)- 功能：指定时间向下舍入，保留字段级别到年
- 返回类型：timestamp
Example
SQL
1mysql> select year_floor('2020-12-25');
2+-----------------------------------+
3| year_floor('2020-12-25 00:00:00') |
4+-----------------------------------+
5| 2020-01-01 00:00:00 |
6+-----------------------------------+
71 row in set (0.04 sec)Keywords
Plain Text
1year_floorYEARS_ADD
Description
SQL
1years_add(timestamp date, int years)
2
3years_add(timestamp date, bigint years)- 功能：返回指定的日期加上若干年后的时间
- 返回类型：timestamp
Example
SQL
1mysql> select years_add('2020-12-25',10);
2+--------------------------------------+
3| years_add('2020-12-25 00:00:00', 10) |
4+--------------------------------------+
5| 2030-12-25 00:00:00 |
6+--------------------------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1years_addYEAR_DIFF
Description
SQL
1years_diff(timestamp enddate, timestamp startdate)- 功能：开始时间到结束时间相差几年
- 返回类型：int类型
Example
SQL
1mysql> select years_diff('2020-12-25','2011-04-30');
2+----------------------------------------------------------+
3| years_diff('2020-12-25 00:00:00', '2011-04-30 00:00:00') |
4+----------------------------------------------------------+
5| 9 |
6+----------------------------------------------------------+
71 row in set (0.02 sec)Keywords
Plain Text
1years_diffYEAR_SUB
Description
SQL
1years_sub(timestamp date, int years)
2
3years_sub(timestamp date, bigint years)- 功能：返回指定的日期减去若干年的时间
- 返回类型：timestamp
Example
SQL
1mysql> select years_sub('2020-12-25',10);
2+--------------------------------------+
3| years_sub('2020-12-25 00:00:00', 10) |
4+--------------------------------------+
5| 2010-12-25 00:00:00 |
6+--------------------------------------+
71 row in set (0.03 sec)Keywords
Plain Text
1years_subMAKEDATE
Description
SQL
1DATE MAKEDATE(INT year, INT dayofyear)- 功能：返回指定年份和 dayofyear 构建的日期。dayofyear 必须大于0，否则结果为空。
- 返回类型：date
Example
SQL
1mysql> select makedate(2021,1), makedate(2021,100), makedate(2021,400);
2+-------------------+---------------------+---------------------+
3| makedate(2021, 1) | makedate(2021, 100) | makedate(2021, 400) |
4+-------------------+---------------------+---------------------+
5| 2021-01-01 | 2021-04-10 | 2022-02-04 |
6+-------------------+---------------------+---------------------+Keywords
Plain Text
1makedateWEEK
Description
INT YEARWEEK(DATE date)
INT YEARWEEK(DATE date, INT mode)
*功能：返回指定日期的星期数。mode的值默认为0。
参数mode的作用参见下面的表格：
| Mode | 星期的第一天 | 星期数的范围 | 第一个星期的定义 |
|---|---|---|---|
| 0 | 星期日 | 0-53 | 这一年中的第一个星期日所在的星期 |
| 1 | 星期一 | 0-53 | 这一年的日期所占的天数大于等于4天的第一个星期 |
| 2 | 星期日 | 1-53 | 这一年中的第一个星期日所在的星期 |
| 3 | 星期一 | 1-53 | 这一年的日期所占的天数大于等于4天的第一个星期 |
| 4 | 星期日 | 0-53 | 这一年的日期所占的天数大于等于4天的第一个星期 |
| 5 | 星期一 | 0-53 | 这一年中的第一个星期一所在的星期 |
| 6 | 星期日 | 1-53 | 这一年的日期所占的天数大于等于4天的第一个星期 |
| 7 | 星期一 | 1-53 | 这一年中的第一个星期一所在的星期 |
参数为Date或者Datetime类型
Example
SQL
1mysql> select yearweek('2021-1-1');
2+----------------------+
3| yearweek('2021-1-1') |
4+----------------------+
5| 202052 |
6+----------------------+
7
8mysql> select yearweek('2020-7-1');
9+----------------------+
10| yearweek('2020-7-1') |
11+----------------------+
12| 202026 |
13+----------------------+
14
15mysql> select yearweek('2024-12-30',1);
16+------------------------------------+
17| yearweek('2024-12-30 00:00:00', 1) |
18+------------------------------------+
19| 202501 |
20+------------------------------------+keywords
Plain Text
1weekYEARWEEK
Description
SQL
1INT YEARWEEK(DATE date)
2INT YEARWEEK(DATE date, INT mode)- 功能：返回指定日期的年份和星期数。mode的值默认为0。
当日期所在的星期属于上一年时，返回的是上一年的年份和星期数；
当日期所在的星期属于下一年时，返回的是下一年的年份，星期数为1。
参数mode的作用参见下面的表格：
| Mode | 星期的第一天 | 星期数的范围 | 第一个星期的定义 |
|---|---|---|---|
| 0 | 星期日 | 1-53 | 这一年中的第一个星期日所在的星期 |
| 1 | 星期一 | 1-53 | 这一年的日期所占的天数大于等于4天的第一个星期 |
| 2 | 星期日 | 1-53 | 这一年中的第一个星期日所在的星期 |
| 3 | 星期一 | 1-53 | 这一年的日期所占的天数大于等于4天的第一个星期 |
| 4 | 星期日 | 1-53 | 这一年的日期所占的天数大于等于4天的第一个星期 |
| 5 | 星期一 | 1-53 | 这一年中的第一个星期一所在的星期 |
| 6 | 星期日 | 1-53 | 这一年的日期所占的天数大于等于4天的第一个星期 |
| 7 | 星期一 | 1-53 | 这一年中的第一个星期一所在的星期 |
参数为Date或者Datetime类型
Example
SQL
1mysql> select yearweek('2021-1-1');
2+----------------------+
3| yearweek('2021-1-1') |
4+----------------------+
5| 202052 |
6+----------------------+
7
8mysql> select yearweek('2020-7-1');
9+----------------------+
10| yearweek('2020-7-1') |
11+----------------------+
12| 202026 |
13+----------------------+
14
15mysql> select yearweek('2024-12-30',1);
16+------------------------------------+
17| yearweek('2024-12-30 00:00:00', 1) |
18+------------------------------------+
19| 202501 |
20+------------------------------------+Keywords
Plain Text
1yearweek