全部产品

日期和时间函数

更新时间:2019-05-31 17:54:47

  • ADDDATE/DATE_ADD:返回添加指定时间后的日期。
  • ADDTIME:返回添加指定时间后的时间。
  • CONVERT_TZ:转换时区,从from_tz转到to_tz给出的时区,并返回结果值。
  • CURDATE:返回当前日期。
  • CURTIME:返回当前时间。
  • DATE:返回日期或日期时间表达式中的日期。
  • DATE_FORMAT:按照format指定的格式,将日期时间格式化成字符串。
  • SUBDATE/DATE_SUB:返回date减去指定INTERVAL间隔后的日期。
  • DATEDIFF:返回expr1减去expr2后的天数。
  • DAY/DAYOFMONTH:返回date中的日,取值范围[1,31]。
  • DAYNAME:返回日期对应的工作日的名称,例如星期一为Monday。
  • DAYOFWEEK:返回日期对应的工作日索引值。
  • DAYOFYEAR:返回指定日期是当年的哪一天。
  • EXTRACT:返回日期或时间的单独部分,例如年、月、日、小时、分钟等。
  • FROM_DAYS:根据指定的天数N,返回对应的DATE值。
  • FROM_UNIXTIME:返回unixtime时间戳。
  • HOUR:返回时间中的小时。
  • LAST_DAY:返回日期或者日期时间中对应月份的最后一天。
  • LOCALTIME/LOCALTIMESTAMP/NOW:返回当前时间戳。
  • MAKEDATE:按照参数year和dayofyear,返回一个日期。
  • MAKETIME:按照参数hour、minute和second,返回一个时间。
  • MINUTE:返回时间中的分钟。
  • MONTH:返回日期中的月份。
  • MONTHNAME:返回日期中月份的全名。
  • PERIOD_ADD:将日期格式的参数P增加N个月。
  • PERIOD_DIFF:返回P1和P2之间相差的月数。
  • QUARTER:返回日期在一年中的季度。
  • SEC_TO_TIME:将seconds转换为时间。
  • SECOND:返回时间中的秒。
  • STR_TO_DATE:按照指定日期或时间显示格式,将字符串转换为日期或日期时间类型。
  • SUBTIME:返回expr1减去expr2后的时间。
  • SYSDATE:获取系统时间。
  • TIME:以字符串形式返回expr中的时间。
  • TIME_FORMAT:按照format指定的格式,以字符串形式显示时间。
  • TIME_TO_SEC:返回time转换为秒的结果。
  • TIMEDIFF:返回expr1减去expr2后的时间。
  • TIMESTAMP:返回expr表示的日期或日期时间。
  • TIMESTAMPADD:将interval添加到日期或日期时间表达式datetime_expr中。
  • TIMESTAMPDIFF:返回日期或日期时间表达式datetime_expr1减去datetime_expr2后的结果。
  • TO_DAYS:根据给定日期date,返回自0年开始的天数。
  • TO_SECONDS:根据给定的expr,返回自0年开始的秒数。
  • UNIX_TIMESTAMP:返回自’1970-01-01 00:00:00’ UTC以来秒数的Unix时间戳。
  • UTC_DATE:返回UTC日期。
  • UTC_TIME:返回UTC时间。
  • UTC_TIMESTAMP:返回UTC时间戳。
  • UTC_DATE:返回UTC日期。
  • WEEK:返回日期对应的周数。
  • WEEKDAY:返回日期对应的工作日。
  • WEEKOFYEAR:返回日期对应的日历周。
  • YEAR:返回日期中的年份。
  • YEARWEEK:返回日期的年份和星期。

ADDDATE/DATE_ADD

  1. ADDDATE(date,INTERVAL expr unit)
  2. ADDDATE(expr,days)
  • 命令说明:返回添加指定时间后的日期。

    • unit可取值为:secondminutehourdaymonthyearminute_secondhour_secondhour_minuteday_secondday_minuteday_houryear_monthunit默认值为day
    • daysexpr:系统将返回expr加上days之后的结果。
  • 参数类型:

    1. adddate(date, INTERVAL expr unit)
    2. adddate(timestamp, INTERVAL expr unit)
    3. adddate(datetime, INTERVAL expr unit)
    4. adddate(varchar, INTERVAL expr unit)
    5. adddate(date, varchar)
    6. adddate(date, bigint)
    7. adddate(datetime, bigint)
    8. adddate(datetime, varchar)
    9. adddate(timestamp, varchar)
    10. adddate(timestamp, bigint)
    11. adddate(varchar, bigint)
    12. adddate(varchar, varchar)
  • 返回值类型:DATE。

  • 示例:

    1. select adddate(date '2001-1-22',interval '3' day);
    2. +----------------------------------------------+
    3. | adddate(DATE '2001-1-22', INTERVAL '3' DAY) |
    4. +----------------------------------------------+
    5. | 2001-01-25 |
    1. select adddate(timestamp '2001-1-22',interval '3' day);
    2. +---------------------------------------------------+
    3. | adddate(TIMESTAMP '2001-1-22', INTERVAL '3' DAY) |
    4. +---------------------------------------------------+
    5. | 2001-01-25 00:00:00 |
    1. select adddate(datetime '2001-1-22',interval '3' day);
    2. +--------------------------------------------------+
    3. | adddate(DATETIME '2001-1-22', INTERVAL '3' DAY) |
    4. +--------------------------------------------------+
    5. | 2001-01-25 00:00:00 |
    1. select adddate('2001-1-22',interval '3' day);
    2. +-----------------------------------------+
    3. | adddate('2001-1-22', INTERVAL '3' DAY) |
    4. +-----------------------------------------+
    5. | 2001-01-25 |
    1. select adddate(datetime '2001-1-22',interval '3' second);
    2. +-----------------------------------------------------+
    3. | adddate(DATETIME '2001-1-22', INTERVAL '3' SECOND) |
    4. +-----------------------------------------------------+
    5. | 2001-01-22 00:00:03 |
    1. select adddate(datetime '2001-1-22',interval '3' minute);
    2. +-----------------------------------------------------+
    3. | adddate(DATETIME '2001-1-22', INTERVAL '3' MINUTE) |
    4. +-----------------------------------------------------+
    5. | 2001-01-22 00:03:00 |
    1. select adddate(datetime '2001-1-22',interval '3' hour);
    2. +---------------------------------------------------+
    3. | adddate(DATETIME '2001-1-22', INTERVAL '3' HOUR) |
    4. +---------------------------------------------------+
    5. | 2001-01-22 03:00:00 |
    1. select adddate(datetime '2001-1-22',interval '3' month);
    2. +----------------------------------------------------+
    3. | adddate(DATETIME '2001-1-22', INTERVAL '3' MONTH) |
    4. +----------------------------------------------------+
    5. | 2001-04-22 00:00:00 |
    1. select adddate(datetime '2001-1-22',interval '3' year);
    2. +---------------------------------------------------+
    3. | adddate(DATETIME '2001-1-22', INTERVAL '3' YEAR) |
    4. +---------------------------------------------------+
    5. | 2004-01-22 00:00:00 |
    1. select adddate(datetime '2001-1-22',interval '3' minute_second) as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-22 00:03:00 |
    1. select adddate(datetime '2001-1-22',interval '3' hour_second) as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-22 03:00:00 |
    1. select adddate(datetime '2001-1-22',interval '3' hour_minute) as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-22 03:00:00 |
    1. select adddate(datetime '2001-1-22',interval '3' day_second) as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-25 00:00:00 |
    1. adddate(datetime '2001-1-22',interval '3' day_minute) as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-25 00:00:00 |
    1. select adddate(datetime '2001-1-22',interval '3' day_hour) as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-25 00:00:00 |
    1. select adddate(datetime '2001-1-22 12:32:1',interval '4' year_month) as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2005-01-22 12:32:01 |
    1. select adddate('2001-1-22','3');
    2. +---------------------------+
    3. | adddate('2001-1-22', '3') |
    4. +---------------------------+
    5. | 2001-01-25 |
    1. select adddate('2001-1-22',3);
    2. +-------------------------+
    3. | adddate('2001-1-22', 3) |
    4. +-------------------------+
    5. | 2001-01-25 |
    1. select adddate(datetime '2001-1-22 12:12:32',3);
    2. +-------------------------------------------+
    3. | adddate(DATETIME '2001-1-22 12:12:32', 3) |
    4. +-------------------------------------------+
    5. | 2001-01-25 12:12:32 |
    1. select adddate(datetime '2001-1-22 12:12:32','3');
    2. +---------------------------------------------+
    3. | adddate(DATETIME '2001-1-22 12:12:32', '3') |
    4. +---------------------------------------------+
    5. | 2001-01-25 12:12:32 |
    1. select adddate(timestamp '2001-1-22 12:12:32','3');
    2. +----------------------------------------------+
    3. | adddate(TIMESTAMP '2001-1-22 12:12:32', '3') |
    4. +----------------------------------------------+
    5. | 2001-01-25 12:12:32 |
    1. select adddate(timestamp '2001-1-22 12:12:32',3);
    2. +--------------------------------------------+
    3. | adddate(TIMESTAMP '2001-1-22 12:12:32', 3) |
    4. +--------------------------------------------+
    5. | 2001-01-25 12:12:32 |
    1. select adddate('2001-1-22 12:12:32',3);
    2. +----------------------------------+
    3. | adddate('2001-1-22 12:12:32', 3) |
    4. +----------------------------------+
    5. | 2001-01-25 12:12:32 |
    1. select adddate('2001-1-22 12:12:32','3');
    2. +------------------------------------+
    3. | adddate('2001-1-22 12:12:32', '3') |
    4. +------------------------------------+
    5. | 2001-01-25 12:12:32 |

ADDTIME

  1. ADDTIME(expr1,expr2)
  • 命令说明:返回添加指定时间后的时间,即返回expr1增加expr2后的结果。

  • 参数类型:

    1. addtime(date,varchar)
    2. addtime(time,varchar)
    3. addtime(datetime,varchar)
    4. addtime(timestamp,varchar)
    5. addtime(varchar,varchar)
  • 返回值类型:VARCHAR。

  • 示例:

    1. select addtime(date '1998-01-01','01:01:01');
    2. +----------------------------------------+
    3. | addtime(DATE '1998-01-01', '01:01:01') |
    4. +----------------------------------------+
    5. | 1998-01-01 01:01:01 |
    1. select addtime(time '00:00:00','01:01:01');
    2. +--------------------------------------+
    3. | addtime(TIME '00:00:00', '01:01:01') |
    4. +--------------------------------------+
    5. | 01:01:01 |
    1. select addtime(datetime '2001-1-22 00:00:00','01:01:01');
    2. +----------------------------------------------------+
    3. | addtime(DATETIME '2001-1-22 00:00:00', '01:01:01') |
    4. +----------------------------------------------------+
    5. | 2001-01-22 01:01:01 |
    1. select addtime(timestamp '2001-1-22 00:00:00','01:01:01');
    2. +-----------------------------------------------------+
    3. | addtime(TIMESTAMP '2001-1-22 00:00:00', '01:01:01') |
    4. +-----------------------------------------------------+
    5. | 2001-01-22 01:01:01 |
    1. select addtime('2001-1-22 00:00:00','01:01:01');
    2. +-------------------------------------------+
    3. | addtime('2001-1-22 00:00:00', '01:01:01') |
    4. +-------------------------------------------+
    5. | 2001-01-22 01:01:01 |

CONVERT_TZ

  1. CONVERT_TZ(dt,from_tz,to_tz)
  • 命令说明:转换dt,从from_tz转到to_tz给出的时区,并返回结果。

  • 参数类型:

    1. convert_tz(varchar, varchar, varchar)
  • 返回值类型:DATETIME。

  • 示例:

    1. select convert_tz('2004-01-01 12:00:00','+00:00','+10:00');
    2. +-------------------------------------------------------+
    3. | convert_tz('2004-01-01 12:00:00', '+00:00', '+10:00') |
    4. +-------------------------------------------------------+
    5. | 2004-01-01 22:00:00 |
    1. select convert_tz('2004-01-01 12:00:00','GMT','MET');
    2. +-------------------------------------------------+
    3. | convert_tz('2004-01-01 12:00:00', 'GMT', 'MET') |
    4. +-------------------------------------------------+
    5. | 2004-01-01 13:00:00 |

CURDATE

  1. CURDATE()
  • 命令说明:返回当前日期。

  • 返回值类型:DATE。

  • 示例:

    1. select curdate;
    2. +------------+
    3. | curdate() |
    4. +------------+
    5. | 2019-05-25 |

CURTIME

  1. CURTIME()
  • 命令说明:返回当前时间。

  • 返回值类型:TIME。

  • 示例:

    1. select curtime();
    2. +--------------+
    3. | curtime() |
    4. +--------------+
    5. | 14:39:22.109 |

DATE

  1. DATE(expr)
  • 命令说明:返回日期或日期时间表达式中的日期。

  • 参数类型:

    1. date(timestamp)
    2. date(datetime)
    3. date(varchar)
  • 返回值类型:DATE。

  • 示例:

    1. select date(timestamp '2003-12-31 01:02:03');
    2. +---------------------------------------+
    3. | date(TIMESTAMP '2003-12-31 01:02:03') |
    4. +---------------------------------------+
    5. | 2003-12-31 |
    1. select date(datetime '2003-12-31 01:02:03');
    2. +--------------------------------------+
    3. | date(DATETIME '2003-12-31 01:02:03') |
    4. +--------------------------------------+
    5. | 2003-12-31 |
    1. select date('2003-12-31 01:02:03');
    2. +-----------------------------+
    3. | date('2003-12-31 01:02:03') |
    4. +-----------------------------+
    5. | 2003-12-31 |

DATE_FORMAT

  1. DATE_FORMAT(date,format)
  • 命令说明:按照format指定的格式,将日期时间格式化成字符串。format格式如下所示。

    符号 说明
    %a 工作日缩写名称(Sun.. Sat)
    %b 缩写的月份名称(Jan.. Dec)
    %c 月,数字(0.. 12)
    %d 每月的某一天,数字(00.. 31)
    %e 每月的某一天,数字(0.. 31)
    %f 微秒(000000… 999999)
    %H 小时(00.. 23)
    %h 小时(01.. 12)
    %I 小时(01.. 12)
    %i 分钟,数字(00.. 59)
    %j 一年中的一天(001.. 366)
    %k 小时(0.. 23)
    %l 小时(1.. 12)
    %M 月份名称(January.. December)
    %m 月,数字(00.. 12)
    %p AM或PM
    %r 时间,12小时(hh:mm:ss其次是AM或PM)
    %S 秒(00… 59)
    %s 秒(00… 59)
    %T 时间,24小时(hh:mm:ss)
    %v 周(01.. 53),周一是一周的第一天; WEEK()模式3; 用于%x
    %W 工作日名称(Sunday.. Saturday)
    %x 一周的年份,星期一是一周的第一天,数字,四位数; 用于%v
    %Y 年份,数字,四位数
    %y 年份,数字,两位数
    %% 文字%字符
    %x x,对于上面未列出的任何x
  • 参数类型:

    1. date_format(timestamp, varchar)
    2. date_format(varchar, varchar)
    3. date_format(datetime, varchar)
    4. date_format(date, varchar)
  • 返回值类型:VARCHAR。

  • 示例:

    1. select date_format(timestamp '2019-05-27 13:23:00', '%W %M %Y')as result;
    2. +-----------------+
    3. | result |
    4. +-----------------+
    5. | Monday May 2019 |
    1. select date_format('2019-05-27 13:23:00', '%W %M %Y')as result;
    2. +-----------------+
    3. | result |
    4. +-----------------+
    5. | Monday May 2019 |
    1. select date_format(datetime '2019-05-27 13:23:00', '%W %M %Y')as result;
    2. +-----------------+
    3. | result |
    4. +-----------------+
    5. | Monday May 2019 |
    1. select date_format(date '2019-05-27', '%W %M %Y')as result;
    2. +-----------------+
    3. | result |
    4. +-----------------+
    5. | Monday May 2019 |

SUBDATE/DATE_SUB

  1. DATE_SUB(date,INTERVAL expr unit)
  • 命令说明:返回date减去指定INTERVAL间隔后的日期。

    unit可取值为:secondminutehourdaymonthyearminute_secondhour_secondhour_minuteday_secondday_minuteday_houryear_monthunit默认值为day

  • 参数类型:

    1. subdate(date, INTERVAL expr unit)
    2. subdate(timestamp, INTERVAL expr unit)
    3. subdate(datetime, INTERVAL expr unit)
    4. subdate(varchar, INTERVAL expr unit)
    5. subdate(date, bigint)
    6. subdate(date, varchar)
    7. subdate(datetime, bigint)
    8. subdate(datetime, varchar)
    9. subdate(timestamp, bigint)
    10. subdate(timestamp, varchar)
    11. subdate(varchar, bigint)
    12. subdate(varchar, varchar)
  • 返回值类型:DATE。

  • 示例:

    1. select date_sub(date '2001-1-22',interval '3' day);
    2. +-----------------------------------------------+
    3. | date_sub(DATE '2001-1-22', INTERVAL '3' DAY) |
    4. +-----------------------------------------------+
    5. | 2001-01-19 |
    1. select date_sub(timestamp '2001-1-22 00:00:00',interval '3' day)as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-19 00:00:00 |
    1. select date_sub(datetime '2001-1-22 00:00:00',interval '3' day)as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-19 00:00:00 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' day);
    2. +---------------------------------------------------+
    3. | date_sub('2001-1-22 00:00:00', INTERVAL '3' DAY) |
    4. +---------------------------------------------------+
    5. | 2001-01-19 00:00:00 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' second);
    2. +------------------------------------------------------+
    3. | date_sub('2001-1-22 00:00:00', INTERVAL '3' SECOND) |
    4. +------------------------------------------------------+
    5. | 2001-01-21 23:59:57 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' minute);
    2. +------------------------------------------------------+
    3. | date_sub('2001-1-22 00:00:00', INTERVAL '3' MINUTE) |
    4. +------------------------------------------------------+
    5. | 2001-01-21 23:57:00 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' hour);
    2. +----------------------------------------------------+
    3. | date_sub('2001-1-22 00:00:00', INTERVAL '3' HOUR) |
    4. +----------------------------------------------------+
    5. | 2001-01-21 21:00:00 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' month);
    2. +-----------------------------------------------------+
    3. | date_sub('2001-1-22 00:00:00', INTERVAL '3' MONTH) |
    4. +-----------------------------------------------------+
    5. | 2000-10-22 00:00:00 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' year);
    2. +----------------------------------------------------+
    3. | date_sub('2001-1-22 00:00:00', INTERVAL '3' YEAR) |
    4. +----------------------------------------------------+
    5. | 1998-01-22 00:00:00 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' minute_second)as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-21 23:57:00 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' hour_second)as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-21 21:00:00 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' hour_minute)as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-21 21:00:00 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' day_second)as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-19 00:00:00 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' day_minute)as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-19 00:00:00 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' day_hour)as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2001-01-19 00:00:00 |
    1. select date_sub('2001-1-22 00:00:00',interval '3' year_month)as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 1998-01-22 00:00:00 |
    1. select date_sub(date '2001-1-22 00:00:00',3);
    2. +----------------------------------------+
    3. | date_sub(DATE '2001-1-22 00:00:00', 3) |
    4. +----------------------------------------+
    5. | 2001-01-19 |
    1. select date_sub(date '2001-1-22 00:00:00','3');
    2. +------------------------------------------+
    3. | date_sub(DATE '2001-1-22 00:00:00', '3') |
    4. +------------------------------------------+
    5. | 2001-01-19 |
    1. select date_sub(datetime '2001-1-22 00:00:00',3);
    2. +--------------------------------------------+
    3. | date_sub(DATETIME '2001-1-22 00:00:00', 3) |
    4. +--------------------------------------------+
    5. | 2001-01-19 00:00:00 |
    1. select date_sub(datetime '2001-1-22 00:00:00','3');
    2. +----------------------------------------------+
    3. | date_sub(DATETIME '2001-1-22 00:00:00', '3') |
    4. +----------------------------------------------+
    5. | 2001-01-19 00:00:00 |
    1. select date_sub(timestamp '2001-1-22 00:00:00',3);
    2. +---------------------------------------------+
    3. | date_sub(TIMESTAMP '2001-1-22 00:00:00', 3) |
    4. +---------------------------------------------+
    5. | 2001-01-19 00:00:00 |
    1. select date_sub(timestamp '2001-1-22 00:00:00','3');
    2. +-----------------------------------------------+
    3. | date_sub(TIMESTAMP '2001-1-22 00:00:00', '3') |
    4. +-----------------------------------------------+
    5. | 2001-01-19 00:00:00 |
    1. select date_sub('2001-1-22 00:00:00',3);
    2. +-----------------------------------+
    3. | date_sub('2001-1-22 00:00:00', 3) |
    4. +-----------------------------------+
    5. | 2001-01-19 00:00:00 |
    1. select date_sub('2001-1-22 00:00:00','3');
    2. +-------------------------------------+
    3. | date_sub('2001-1-22 00:00:00', '3') |
    4. +-------------------------------------+
    5. | 2001-01-19 00:00:00 |

DATEDIFF

  1. DATEDIFF(expr1,expr2)
  • 命令说明:返回expr1减去expr2后的天数。

  • 参数类型:

    1. datediff(varchar, varchar)
    2. datediff(datetime, varchar)
    3. datediff(varchar, datetime)
    4. datediff(datetime, datetime)
    5. datediff(varchar, timestamp)
    6. datediff(timestamp, timestamp)
    7. datediff(timestamp, varchar)
    8. datediff(date, date)
    9. datediff(date, varchar)
    10. datediff(varchar, date)
  • 返回值类型:BIGINT。

  • 示例:

    1. select datediff('2007-12-31 23:59:59','2007-12-30');
    2. +-----------------------------------------------+
    3. | datediff('2007-12-31 23:59:59', '2007-12-30') |
    4. +-----------------------------------------------+
    5. | 1 |
    1. select datediff(datetime '2007-12-31 23:59:59','2007-12-30')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | 1 |
    1. select datediff('2007-12-31 23:59:59',datetime '2007-12-30')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | 1 |
    1. select datediff(datetime '2007-12-31 23:59:59',datetime '2007-12-30')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | 1 |
    1. select datediff('2007-12-31 23:59:59',timestamp '2007-12-30')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | 1 |
    1. select datediff(timestamp '2007-12-31 23:59:59',timestamp '2007-12-30')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | 1 |
    1. select datediff(timestamp '2007-12-31 23:59:59','2007-12-30')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | 1 |
    1. select datediff(date '2007-12-31 23:59:59',date '2007-12-30')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | 1 |
    1. select datediff(date '2007-12-31 23:59:59','2007-12-30')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | 1 |
    1. select datediff('2008-12-31',date '2007-12-30');
    2. +-------------------------------------------+
    3. | datediff('2008-12-31', DATE '2007-12-30') |
    4. +-------------------------------------------+
    5. | 367 |

DAY/DAYOFMONTH

  1. DAY(date)
  2. DAYOFMONTH(date)
  • 命令说明:返回date中的日,取值范围[1,31]

  • 参数类型:

    1. dayofmonth(timestamp)
    2. dayofmonth(datetime)
    3. dayofmonth(date)
    4. dayofmonth(time)
    5. dayofmonth(varchar)
  • 返回值类型:BIGINT。

  • 示例:

    1. select dayofmonth(timestamp '2007-02-03 12:23:09');
    2. +---------------------------------------------+
    3. | dayofmonth(TIMESTAMP '2007-02-03 12:23:09') |
    4. +---------------------------------------------+
    5. | 3 |
    1. select dayofmonth(date '2007-02-03');
    2. +-------------------------------+
    3. | dayofmonth(DATE '2007-02-03') |
    4. +-------------------------------+
    5. | 3 |
    1. select dayofmonth(time '17:01:10');
    2. +-----------------------------+
    3. | dayofmonth(TIME '17:01:10') |
    4. +-----------------------------+
    5. | 30 |
    1. select day('2007-02-03');
    2. +-------------------+
    3. | day('2007-02-03') |
    4. +-------------------+
    5. | 3 |
    1. select dayofmonth(datetime '2007-02-03 00:00:00');
    2. +--------------------------------------------+
    3. | dayofmonth(DATETIME '2007-02-03 00:00:00') |
    4. +--------------------------------------------+
    5. | 3 |

DAYNAME

  1. DAYNAME(date)
  • 命令说明:返回日期对应的工作日的名称,例如星期一为Monday

  • 参数类型:

    1. dayname(timestamp)
    2. dayname(datetime)
    3. dayname(date)
    4. dayname(varchar)
  • 返回值类型:VARCHAR。

  • 示例:

    1. select dayname(timestamp '2007-02-03 00:00:00');
    2. +------------------------------------------+
    3. | dayname(TIMESTAMP '2007-02-03 00:00:00') |
    4. +------------------------------------------+
    5. | Saturday |
    1. select dayname(datetime '2007-02-03 00:00:00');
    2. +-----------------------------------------+
    3. | dayname(DATETIME '2007-02-03 00:00:00') |
    4. +-----------------------------------------+
    5. | Saturday |
    1. select dayname(date '2007-02-04');
    2. +----------------------------+
    3. | dayname(DATE '2007-02-04') |
    4. +----------------------------+
    5. | Sunday |
    1. select dayname('2007-02-03');
    2. +-----------------------+
    3. | dayname('2007-02-03') |
    4. +-----------------------+
    5. | Saturday |

DAYOFWEEK

  1. DAYOFWEEK(date)
  • 命令说明:返回日期对应的工作日索引值,即星期日为1,星期一为2,星期六为7

  • 参数类型:

    1. dayofweek(timestamp)
    2. dayofweek(datetime)
    3. dayofweek(date)
    4. dayofweek(varchar)
  • 返回值类型:BIGINT。

  • 示例:

    1. select dayofweek(timestamp '2007-02-03 00:00:00');
    2. +--------------------------------------------+
    3. | dayofweek(TIMESTAMP '2007-02-03 00:00:00') |
    4. +--------------------------------------------+
    5. | 7 |
    1. select dayofweek(datetime '2007-02-03 00:00:00');
    2. +-------------------------------------------+
    3. | dayofweek(DATETIME '2007-02-03 00:00:00') |
    4. +-------------------------------------------+
    5. | 7 |
    1. select dayofweek(date '2007-02-03');
    2. +------------------------------+
    3. | dayofweek(DATE '2007-02-03') |
    4. +------------------------------+
    5. | 7 |
    1. select dayofweek('2007-02-03');
    2. +-------------------------+
    3. | dayofweek('2007-02-03') |
    4. +-------------------------+
    5. | 7 |

    DAYOFYEAR

  1. DAYOFYEAR(date)
  • 命令说明:返回指定日期是当年的哪一天,返回值范围为[1,366]

  • 参数类型:

    1. dayofyear(timestamp)
    2. dayofyear(datetime)
    3. dayofyear(date)
    4. dayofyear(varchar)
  • 返回值类型:BIGINT。

  • 示例:

    1. select dayofyear(timestamp '2007-02-03 00:12:12');
    2. +--------------------------------------------+
    3. | dayofyear(TIMESTAMP '2007-02-03 00:12:12') |
    4. +--------------------------------------------+
    5. | 34 |
    1. select dayofyear(datetime '2007-02-03 00:12:12');
    2. +-------------------------------------------+
    3. | dayofyear(DATETIME '2007-02-03 00:12:12') |
    4. +-------------------------------------------+
    5. | 34 |
    1. select dayofyear(date '2007-02-03');
    2. +------------------------------+
    3. | dayofyear(DATE '2007-02-03') |
    4. +------------------------------+
    5. | 34 |
    1. select dayofyear('2007-02-03');
    2. +-------------------------+
    3. | dayofyear('2007-02-03') |
    4. +-------------------------+
    5. | 34 |

EXTRACT

  1. EXTRACT(unit FROM date)
  • 命令说明:返回日期或时间的单独部分,由unit指定,比如年、月、日、小时、分钟等。

    unit可取值为:secondminutehourdaymonthyearminute_secondhour_secondhour_minuteday_secondday_minuteday_houryear_month

  • 支持抽取的入参时间类型:VARCHAR、TIMESTAMP、DATETIME、TIME。

  • 返回值类型:BIGINT。

  • 示例:

    1. select extract(second from '2019-07-02 00:12:34');
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 34 |
    1. select extract(minute from '2019-07-02 00:12:34');
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 12 |
    1. select extract(hour from '2019-07-02 00:12:34');
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 0 |
    1. select extract(month from '2019-07-02 00:12:34');
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 7 |
    1. select extract(minute_second from '2019-07-02 00:12:34');
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 1234 |
    1. select extract(hour_second from '2019-07-02 12:12:34');
    2. +--------+
    3. | _col0 |
    4. +--------+
    5. | 121234 |
    1. select extract(hour_minute from '2019-07-02 12:12:34');
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 1212 |
    1. select extract(day_second from '2019-07-02 12:12:34');
    2. +---------+
    3. | _col0 |
    4. +---------+
    5. | 2121234 |
    1. select extract(day_hour from '2019-07-02 12:12:34');
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 212 |
    1. select extract(day from '2019-07-02 00:12:34');
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2 |
    1. select extract(year_month from '2019-07-02 00:12:34');
    2. +--------+
    3. | _col0 |
    4. +--------+
    5. | 201907 |
    1. select extract(day_minute from '2019-07-02 00:12:34');
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 20012 |
    1. select extract(year from timestamp '2019-05-30');
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2019 |
    1. select extract(year from datetime '2019-05-30');
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2019 |
    1. select extract(year from time '15:23:22');
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2019 |

FROM_DAYS

  1. FROM_DAYS(N)
  • 命令说明:根据指定的天数N,返回对应的DATE值。

  • 参数类型:

    1. from_days(varchar)
    2. from_days(bigint)
  • 返回值类型:DATE。

  • 示例:

    1. select from_days(730669);
    2. +-------------------+
    3. | from_days(730669) |
    4. +-------------------+
    5. | 2000-07-03 |
    1. select from_days('730669');
    2. +---------------------+
    3. | from_days('730669') |
    4. +---------------------+
    5. | 2000-07-03 |

FROM_UNIXTIME

  1. FROM_UNIXTIME(unix_timestamp[,format])
  • 命令说明:返回unixtime时间戳。

    format遵从DATE_FORMAT函数中的format格式。

  • 参数类型:

    1. from_unixtime(varchar, varchar)
    2. from_unixtime(varchar)
    3. from_unixtime(double, varchar)
    4. from_unixtime(double)
  • 返回值类型:DATETIME。

  • 示例:

    1. select from_unixtime('1447430881','%Y %M %h:%i:%s %x');
    2. +--------------------------------------------------+
    3. | from_unixtime('1447430881', '%Y %M %h:%i:%s %x') |
    4. +--------------------------------------------------+
    5. | 2015 November 12:08:01 2015 |
    1. select from_unixtime('1447430881');
    2. +-----------------------------+
    3. | from_unixtime('1447430881') |
    4. +-----------------------------+
    5. | 2015-11-14 00:08:01 |
    1. select from_unixtime(1447430881);
    2. +---------------------------+
    3. | from_unixtime(1447430881) |
    4. +---------------------------+
    5. | 2015-11-14 00:08:01 |
    1. select from_unixtime(1447430881,'%Y %M %h:%i:%s %x');
    2. +------------------------------------------------+
    3. | from_unixtime(1447430881, '%Y %M %h:%i:%s %x') |
    4. +------------------------------------------------+
    5. | 2015 November 12:08:01 2015 |

HOUR

  1. HOUR(time)
  • 命令说明:返回时间中的小时。

  • 参数类型:

    1. hour(timestamp)
    2. hour(datetime)
    3. hour(date)
    4. hour(time)
    5. hour(varchar)
  • 返回值类型:BIGINT。

  • 示例:

    1. select hour(timestamp '2019-12-07 10:05:03');
    2. +---------------------------------------+
    3. | hour(TIMESTAMP '2019-12-07 10:05:03') |
    4. +---------------------------------------+
    5. | 10 |
    1. select hour(datetime '2019-12-07 10:05:03');
    2. +--------------------------------------+
    3. | hour(DATETIME '2019-12-07 10:05:03') |
    4. +--------------------------------------+
    5. | 10 |
    1. select hour(date '2019-12-07');
    2. +-------------------------+
    3. | hour(DATE '2019-12-07') |
    4. +-------------------------+
    5. | 0 |
    1. select hour(time '10:05:03');
    2. +-----------------------+
    3. | hour(TIME '10:05:03') |
    4. +-----------------------+
    5. | 10 |
    1. select hour('10:05:03');
    2. +------------------+
    3. | hour('10:05:03') |
    4. +------------------+
    5. | 10 |

LAST_DAY

  1. LAST_DAY(date)
  • 命令说明:返回日期或者日期时间中对应月份的最后一天。

  • 参数类型:

    1. last_day(varchar)
    2. last_day(timestamp)
    3. last_day(datetime)
    4. last_day(date)
  • 返回值类型:DATE。

  • 示例:

    1. select last_day('2003-02-05');
    2. +------------------------+
    3. | last_day('2003-02-05') |
    4. +------------------------+
    5. | 2003-02-28 |
    1. select last_day(timestamp '2003-02-05 12:12:12');
    2. +-------------------------------------------+
    3. | last_day(TIMESTAMP '2003-02-05 12:12:12') |
    4. +-------------------------------------------+
    5. | 2003-02-28 |
    1. select last_day(datetime '2003-02-05 12:12:12');
    2. +------------------------------------------+
    3. | last_day(DATETIME '2003-02-05 12:12:12') |
    4. +------------------------------------------+
    5. | 2003-02-28 |
    1. select last_day(date '2003-02-05');
    2. +-----------------------------+
    3. | last_day(DATE '2003-02-05') |
    4. +-----------------------------+
    5. | 2003-02-28 |

LOCALTIME/LOCALTIMESTAMP/NOW

  1. localtime
  2. localtime()
  3. localtimestamp
  4. localtimestamp()
  5. now()
  • 命令说明:返回当前时间戳。

  • 返回值类型:DATETIME。

  • 示例:

    1. select now();
    2. +---------------------+
    3. | now() |
    4. +---------------------+
    5. | 2019-05-25 00:28:37 |
    1. select localtime;
    2. +---------------------+
    3. | localtime() |
    4. +---------------------+
    5. | 2019-05-28 20:44:25 |
    1. select localtime();
    2. +---------------------+
    3. | localtime() |
    4. +---------------------+
    5. | 2019-05-31 17:37:36 |
    1. select localtimestamp;
    2. +---------------------+
    3. | localtimestamp() |
    4. +---------------------+
    5. | 2019-05-28 20:44:44 |
    1. select localtimestamp();
    2. +---------------------+
    3. | localtimestamp() |
    4. +---------------------+
    5. | 2019-05-31 17:38:13 |

MAKEDATE

  1. MAKEDATE(year,dayofyear)
  • 命令说明:按照参数yeardayofyear,返回一个日期。

  • 参数类型:

    1. makedate(bigint, bigint)
    2. makedate(varchar, varchar)
  • 返回值类型:DATE。

  • 示例:

    1. select makedate(2011,31), makedate(2011,32);
    2. +--------------------+--------------------+
    3. | makedate(2011, 31) | makedate(2011, 32) |
    4. +--------------------+--------------------+
    5. | 2011-01-31 | 2011-02-01 |
    1. select makedate('2011','31'), makedate('2011','32');
    2. +------------------------+------------------------+
    3. | makedate('2011', '31') | makedate('2011', '32') |
    4. +------------------------+------------------------+
    5. | 2011-01-31 | 2011-02-01 |

MAKETIME

  1. MAKETIME(hour,minute,second)
  • 命令说明:按照参数hourminutesecond,返回一个时间。

  • 参数类型:

    1. maketime(bigint, bigint, bigint)
    2. maketime(varchar, varchar, varchar)
  • 返回值类型:TIME。

  • 示例:

    1. select maketime(12,15,30);
    2. +----------------------+
    3. | maketime(12, 15, 30) |
    4. +----------------------+
    5. | 12:15:30 |
    1. select maketime('12','15','30');
    2. +----------------------------+
    3. | maketime('12', '15', '30') |
    4. +----------------------------+
    5. | 12:15:30 |

MINUTE

  1. MINUTE(time)
  • 命令说明:返回时间中的分钟。

  • 参数类型:

    1. minute(timestamp)
    2. minute(datetime)
    3. minute(date)
    4. minute(time)
    5. minute(varchar)
  • 返回值类型:BIGINT。

  • 示例:

    1. select minute(timestamp '2008-02-03 10:05:03');
    2. +-----------------------------------------+
    3. | minute(TIMESTAMP '2008-02-03 10:05:03') |
    4. +-----------------------------------------+
    5. | 5 |
    1. select minute(datetime '2008-02-03 10:05:03');
    2. +----------------------------------------+
    3. | minute(DATETIME '2008-02-03 10:05:03') |
    4. +----------------------------------------+
    5. | 5 |
    1. select minute(date '2008-02-03');
    2. +---------------------------+
    3. | minute(DATE '2008-02-03') |
    4. +---------------------------+
    5. | 0 |
    1. select minute(time '12:12:12');
    2. +-------------------------+
    3. | minute(TIME '12:12:12') |
    4. +-------------------------+
    5. | 12 |
    1. select minute('2008-02-03 10:05:03');
    2. +-------------------------------+
    3. | minute('2008-02-03 10:05:03') |
    4. +-------------------------------+
    5. | 5 |

MONTH

  1. MONTH(date)
  • 命令说明:返回日期中的月份。

  • 参数类型:

    1. month(timestamp)
    2. month(datetime)
    3. month(date)
    4. month(time)
    5. month(varchar)
  • 返回值类型:BIGINT。

  • 示例:

    1. select month(timestamp '2008-02-03 00:00:00');
    2. +----------------------------------------+
    3. | month(TIMESTAMP '2008-02-03 00:00:00') |
    4. +----------------------------------------+
    5. | 2 |
    1. select month(datetime '2008-02-03 00:00:00');
    2. +---------------------------------------+
    3. | month(DATETIME '2008-02-03 00:00:00') |
    4. +---------------------------------------+
    5. | 2 |
    1. select month(date '2008-02-03');
    2. +--------------------------+
    3. | month(DATE '2008-02-03') |
    4. +--------------------------+
    5. | 2 |

    MONTH函数也可以返回SQL执行时的月份,例如以下SQL是2019年5月执行的, 返回结果为5。

    1. select month(time '12:12:12');
    2. +------------------------+
    3. | month(TIME '12:12:12') |
    4. +------------------------+
    5. | 5 |
    1. select month('2008-02-03');
    2. +---------------------+
    3. | month('2008-02-03') |
    4. +---------------------+
    5. | 2 |

MONTHNAME

  1. MONTHNAME(date)
  • 命令说明:返回日期中月份的全名。

  • 参数类型:

    1. monthname(timestamp)
    2. monthname(datetime)
    3. monthname(date)
    4. monthname(varchar)
  • 返回值类型:VARCHAR。

  • 示例:

    1. select monthname(timestamp '2008-02-03 00:00:00');
    2. +--------------------------------------------+
    3. | monthname(TIMESTAMP '2008-02-03 00:00:00') |
    4. +--------------------------------------------+
    5. | February |
    1. select monthname(datetime '2008-02-03 00:00:00');
    2. +-------------------------------------------+
    3. | monthname(DATETIME '2008-02-03 00:00:00') |
    4. +-------------------------------------------+
    5. | February |
    1. select monthname(date '2008-02-03');
    2. +------------------------------+
    3. | monthname(DATE '2008-02-03') |
    4. +------------------------------+
    5. | February |
    1. select monthname('2008-02-03');
    2. +-------------------------+
    3. | monthname('2008-02-03') |
    4. +-------------------------+
    5. | February |

PERIOD_ADD

  1. PERIOD_ADD(P,N)
  • 命令说明:将日期格式的参数P增加N个月。

  • 参数类型:

    1. period_add(bigint, bigint)
    2. period_add(varchar, varchar)
    3. period_add(varchar, bigint)
  • 返回值类型:BIGINT。

  • 示例:

    1. select period_add(200801,2);
    2. +-----------------------+
    3. | period_add(200801, 2) |
    4. +-----------------------+
    5. | 200803 |
    1. select period_add('200801','2');
    2. +---------------------------+
    3. | period_add('200801', '2') |
    4. +---------------------------+
    5. | 200803 |
    1. select period_add('200801',2);
    2. +-------------------------+
    3. | period_add('200801', 2) |
    4. +-------------------------+
    5. | 200803 |

PERIOD_DIFF

  1. PERIOD_DIFF(P1,P2)
  • 命令说明:返回P1P2之间相差的月数。

  • 参数类型:

    1. period_diff(bigint, bigint)
    2. period_diff(varchar, varchar)
  • 返回值类型:BIGINT。

  • 示例:

    1. select period_diff(200802,200703);
    2. +-----------------------------+
    3. | period_diff(200802, 200703) |
    4. +-----------------------------+
    5. | 11 |
    1. select period_diff('200802','200703');
    2. +---------------------------------+
    3. | period_diff('200802', '200703') |
    4. +---------------------------------+
    5. | 11 |

QUARTER

  1. QUARTER(date)
  • 命令说明:返回日期在一年中的季度,取值范围为[1,4]

  • 参数类型:

    1. quarter(datetime)
    2. quarter(varchar)
    3. quarter(timestamp)
    4. quarter(date)
  • 返回值类型:BIGINT。

  • 示例:

    1. select quarter(datetime '2008-04-01 12:12:12');
    2. +-----------------------------------------+
    3. | quarter(DATETIME '2008-04-01 12:12:12') |
    4. +-----------------------------------------+
    5. | 2 |
    1. select quarter('2008-04-01');
    2. +-----------------------+
    3. | quarter('2008-04-01') |
    4. +-----------------------+
    5. | 2 |
    1. select quarter(timestamp '2008-04-01 12:12:12');
    2. +------------------------------------------+
    3. | quarter(TIMESTAMP '2008-04-01 12:12:12') |
    4. +------------------------------------------+
    5. | 2 |
    1. select quarter(date '2008-04-01');
    2. +----------------------------+
    3. | quarter(DATE '2008-04-01') |
    4. +----------------------------+
    5. | 2 |

SEC_TO_TIME

  1. SEC_TO_TIME(seconds)
  • 命令说明:将seconds转换为时间。

  • 参数类型:

    1. sec_to_time(bigint)
    2. sec_to_time(varchar)
  • 返回值类型:TIME。

  • 示例:

    1. select sec_to_time(2378);
    2. +-------------------+
    3. | sec_to_time(2378) |
    4. +-------------------+
    5. | 00:39:38 |
    1. select sec_to_time('2378');
    2. +---------------------+
    3. | sec_to_time('2378') |
    4. +---------------------+
    5. | 00:39:38 |

SECOND

  1. SECOND(time)
  • 命令说明:返回时间中的秒,范围为[0,59]

  • 参数类型:

    1. second(timestamp)
    2. second(datetime)
    3. second(date)
    4. second(time)
    5. second(varchar)
  • 返回值类型:BIGINT。

  • 示例:

    1. select second(timestamp '2019-03-12 12:13:14');
    2. +-----------------------------------------+
    3. | second(TIMESTAMP '2019-03-12 12:13:14') |
    4. +-----------------------------------------+
    5. | 14 |
    1. select second(datetime '2019-03-12 12:13:14');
    2. +----------------------------------------+
    3. | second(DATETIME '2019-03-12 12:13:14') |
    4. +----------------------------------------+
    5. | 14 |
    1. select second(date '2019-03-12');
    2. +---------------------------+
    3. | second(DATE '2019-03-12') |
    4. +---------------------------+
    5. | 0 |
    1. select second(time '12:13:14');
    2. +-------------------------+
    3. | second(TIME '12:13:14') |
    4. +-------------------------+
    5. | 14 |
    1. select second('12:12:23');
    2. +--------------------+
    3. | second('12:12:23') |
    4. +--------------------+
    5. | 23 |

STR_TO_DATE

  1. STR_TO_DATE(str,format)
  • 命令说明:按照指定日期或时间显示格式,将字符串转换为日期或日期时间类型。

    format遵从DATE_FORMAT函数中的format格式。

  • 参数类型:

    1. str_to_date(varchar, varchar)
  • 返回值类型:DATETIME。

  • 示例:

    1. select str_to_date('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s') as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2017-01-06 10:20:30 |

SUBTIME

  1. SUBTIME(expr1,expr2)
  • 命令说明:返回expr1减去expr2后的时间。

  • 参数类型:

    1. subtime(date, varchar)
    2. subtime(datetime, varchar)
    3. subtime(timestamp, varchar)
    4. subtime(time, varchar)
    5. subtime(varchar, varchar)
  • 返回值类型:DATETIME。

  • 示例:

    1. select subtime(date '2018-10-31','0:1:1');
    2. +-------------------------------------+
    3. | subtime(DATE '2018-10-31', '0:1:1') |
    4. +-------------------------------------+
    5. | 2018-10-30 23:58:59 |
    1. select subtime(datetime '2018-10-31 12:12:12','0:1:1');
    2. +--------------------------------------------------+
    3. | subtime(DATETIME '2018-10-31 12:12:12', '0:1:1') |
    4. +--------------------------------------------------+
    5. | 2018-10-31 12:11:11 |
    1. select subtime(timestamp '2018-10-31 12:12:12','0:1:1');
    2. +---------------------------------------------------+
    3. | subtime(TIMESTAMP '2018-10-31 12:12:12', '0:1:1') |
    4. +---------------------------------------------------+
    5. | 2018-10-31 12:11:11 |
    1. select subtime(time '12:12:12','0:1:1');
    2. +-----------------------------------+
    3. | subtime(TIME '12:12:12', '0:1:1') |
    4. +-----------------------------------+
    5. | 12:11:11 |
    6. +-----------------------------------+
    1. select subtime('2018-10-31 23:59:59','0:1:1');
    2. +-----------------------------------------+
    3. | subtime('2018-10-31 23:59:59', '0:1:1') |
    4. +-----------------------------------------+
    5. | 2018-10-31 23:58:58 |

SYSDATE

  1. SYSDATE()
  • 命令说明:获取系统时间。

  • 返回值类型:DATETIME。

  • 示例:

    1. select sysdate();
    2. +---------------------+
    3. | sysdate() |
    4. +---------------------+
    5. | 2019-05-26 00:47:21 |

TIME

  1. TIME(expr)
  • 命令说明:以字符串形式返回expr中的时间。

  • 参数类型:

    1. time(varchar)
    2. time(datetime)
    3. time(timestamp)
  • 返回值类型:VARCHAR。

  • 示例:

    1. select time('2003-12-31 01:02:03');
    2. +-----------------------------+
    3. | time('2003-12-31 01:02:03') |
    4. +-----------------------------+
    5. | 01:02:03 |
    1. select time(datetime '2003-12-31 01:02:03');
    2. +--------------------------------------+
    3. | time(DATETIME '2003-12-31 01:02:03') |
    4. +--------------------------------------+
    5. | 01:02:03 |
    1. select time(timestamp '2003-12-31 01:02:03');
    2. +---------------------------------------+
    3. | time(TIMESTAMP '2003-12-31 01:02:03') |
    4. +---------------------------------------+
    5. | 01:02:03 |

TIME_FORMAT

  1. TIME_FORMAT(time,format)
  • 命令说明:按照format指定的格式,以字符串格式显示时间time

    format遵从DATE_FORMAT函数中的format格式。

  • 参数类型:

    1. time_format(varchar, varchar)
    2. time_format(timestamp, varchar)
    3. time_format(datetime, varchar)
    4. time_format(time, varchar)
    5. time_format(date, varchar)
  • 返回值类型:VARCHAR。

  • 示例:

    1. select time_format('12:00:00', '%H %k %h %I %l');
    2. +-------------------------------------------+
    3. | time_format('12:00:00', '%H %k %h %I %l') |
    4. +-------------------------------------------+
    5. | 12 12 12 12 12 |
    1. select time_format(timestamp '1998-01-01 23:00:00','%H %k %h %I %l')as result;
    2. +----------------+
    3. | result |
    4. +----------------+
    5. | 23 23 11 11 11 |
    1. select time_format(datetime '1998-01-01 23:00:00','%H %k %h %I %l')as result;
    2. +----------------+
    3. | result |
    4. +----------------+
    5. | 23 23 11 11 11 |
    1. select time_format(time '23:00:00','%H %k %h %I %l');
    2. +------------------------------------------------+
    3. | time_format(TIME '23:00:00', '%H %k %h %I %l') |
    4. +------------------------------------------------+
    5. | 23 23 11 11 11 |
    1. select time_format(date '1998-01-01','%H %k %h %I %l');
    2. +--------------------------------------------------+
    3. | time_format(DATE '1998-01-01', '%H %k %h %I %l') |
    4. +--------------------------------------------------+
    5. | 00 0 12 12 12 |

TIME_TO_SEC

  1. TIME_TO_SEC(time)
  • 命令说明:返回time转换为秒的结果。

  • 参数类型:

    1. time_to_sec(varchar)
    2. time_to_sec(datetime)
    3. time_to_sec(timestamp)
    4. time_to_sec(date)
    5. time_to_sec(time)
  • 返回值类型:BIGINT。

  • 示例:

    1. select time_to_sec(datetime '2009-12-12 22:23:00');
    2. +---------------------------------------------+
    3. | time_to_sec(DATETIME '2009-12-12 22:23:00') |
    4. +---------------------------------------------+
    5. | 80580 |
    1. select time_to_sec(timestamp '2009-12-12 22:23:00');
    2. +----------------------------------------------+
    3. | time_to_sec(TIMESTAMP '2009-12-12 22:23:00') |
    4. +----------------------------------------------+
    5. | 80580 |
    1. select time_to_sec(date '2009-12-12');
    2. +--------------------------------+
    3. | time_to_sec(DATE '2009-12-12') |
    4. +--------------------------------+
    5. | 0 |
    1. select time_to_sec(time '12:12:12');
    2. +------------------------------+
    3. | time_to_sec(TIME '12:12:12') |
    4. +------------------------------+
    5. | 43932 |
    1. select time_to_sec('22:23:00');
    2. +-------------------------+
    3. | time_to_sec('22:23:00') |
    4. +-------------------------+
    5. | 80580 |

TIMEDIFF

  1. TIMEDIFF(expr1,expr2)
  • 命令说明:返回expr1减去expr2后的时间,与SUBTIME作用相同。

  • 参数类型:

    1. timediff(time, varchar)
    2. timediff(time, time)
    3. timediff(varchar, varchar)
  • 返回值类型:DATETIME。

  • 示例:

    1. select timediff(time '12:00:00','10:00:00');
    2. +---------------------------------------+
    3. | timediff(TIME '12:00:00', '10:00:00') |
    4. +---------------------------------------+
    5. | 02:00:00 |
    1. select timediff('12:00:00','10:00:00');
    2. +----------------------------------+
    3. | timediff('12:00:00', '10:00:00') |
    4. +----------------------------------+
    5. | 02:00:00 |
    1. select timediff(time '12:00:00',time '10:00:00');
    2. +--------------------------------------------+
    3. | timediff(TIME '12:00:00', TIME '10:00:00') |
    4. +--------------------------------------------+
    5. | 02:00:00 |

TIMESTAMP

  1. TIMESTAMP(expr)
  • 命令说明:返回expr表示的日期或日期时间。

  • 参数类型:

    1. timestamp(date)
    2. timestamp(varchar)
  • 返回值类型:DATETIME。

  • 示例:

    1. select timestamp(date '2019-05-27');
    2. +------------------------------+
    3. | timestamp(DATE '2019-05-27') |
    4. +------------------------------+
    5. | 2019-05-27 00:00:00 |
    1. select timestamp('2019-05-27');
    2. +-------------------------+
    3. | timestamp('2019-05-27') |
    4. +-------------------------+
    5. | 2019-05-27 00:00:00 |

TIMESTAMPADD

  1. TIMESTAMPADD(unit,interval,datetime_expr)
  • 命令说明:将interval添加到日期或日期时间表达式datetime_expr中。 interval的单位由unit规定。

    unit可取值为: secondminutehourdayweekmonthquarteryear

  • 参数类型:

    1. timestampadd(varchar, varchar, timestamp)
    2. timestampadd(varchar, bigint, timestamp)
    3. timestampadd(varchar, varchar, date)
    4. timestampadd(varchar, bigint, date)
    5. timestampadd(varchar, varchar, datetime)
    6. timestampadd(varchar, bigint, datetime)
    7. timestampadd(varchar, varchar, varchar)
    8. timestampadd(varchar, bigint, varchar)
  • 返回值类型:DATETIME。

  • 示例:

    1. select timestampadd(second,'1',timestamp '2003-01-02 12:12:12')as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2003-01-02 12:12:13 |
    1. select timestampadd(second,1,timestamp '2003-01-02 12:12:12')as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2003-01-02 12:12:13 |
    1. select timestampadd(second,'1',date '2003-01-02 12:12:12')as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2003-01-02 00:00:01 |
    1. select timestampadd(second,1,date '2003-01-02 12:12:12')as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2003-01-02 00:00:01 |
    1. select timestampadd(second,'1',datetime '2003-01-02 12:12:12')as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2003-01-02 12:12:13 |
    1. select timestampadd(second,1,datetime '2003-01-02 12:12:12')as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2003-01-02 12:12:13 |
    1. select timestampadd(second,'1','2003-01-02 12:12:12')as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2003-01-02 12:12:13 |
    1. select timestampadd(second,1,'2003-01-02 12:12:12')as result;
    2. +---------------------+
    3. | result |
    4. +---------------------+
    5. | 2003-01-02 12:12:13 |
    1. select timestampadd(second,1,'2003-01-02 12:12:12');
    2. +--------------------------------------------------+
    3. | timestampadd('second', 1, '2003-01-02 12:12:12') |
    4. +--------------------------------------------------+
    5. | 2003-01-02 12:12:13 |
    1. select timestampadd(minute,8820,'2019-08-24 09:00:00');
    2. +-----------------------------------------------------+
    3. | timestampadd('MINUTE', 8820, '2019-08-24 09:00:00') |
    4. +-----------------------------------------------------+
    5. | 2019-08-30 12:00:00 |
    1. select timestampadd(hour,1,'2003-01-02 12:12:12');
    2. +------------------------------------------------+
    3. | timestampadd('hour', 1, '2003-01-02 12:12:12') |
    4. +------------------------------------------------+
    5. | 2003-01-02 13:12:12 |
    1. select timestampadd(day,1,'2003-01-02 12:12:12');
    2. +-----------------------------------------------+
    3. | timestampadd('day', 1, '2003-01-02 12:12:12') |
    4. +-----------------------------------------------+
    5. | 2003-01-03 12:12:12 |
    1. select timestampadd(week,1,'2003-01-02 12:12:12');
    2. +------------------------------------------------+
    3. | timestampadd('week', 1, '2003-01-02 12:12:12') |
    4. +------------------------------------------------+
    5. | 2003-01-09 12:12:12 |
    1. select timestampadd(month,1,'2003-01-02 12:12:12');
    2. +-------------------------------------------------+
    3. | timestampadd('month', 1, '2003-01-02 12:12:12') |
    4. +-------------------------------------------------+
    5. | 2003-02-02 12:12:12 |
    1. select timestampadd(year,1,'2003-01-02 12:12:12');
    2. +------------------------------------------------+
    3. | timestampadd('year', 1, '2003-01-02 12:12:12') |
    4. +------------------------------------------------+
    5. | 2004-01-02 12:12:12 |
    1. select timestampadd(quarter,1,'2003-01-02 12:12:12');
    2. +---------------------------------------------------+
    3. | timestampadd('quarter', 1, '2003-01-02 12:12:12') |
    4. +---------------------------------------------------+
    5. | 2003-04-02 12:12:12 |

TIMESTAMPDIFF

  1. TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
  • 命令说明:返回日期或日期时间表达式datetime_expr1减去datetime_expr2后的结果,结果的单位由unit指定。

    unit可取值为:secondminutehourdayweekmonthquarteryear

    使用方法和TIMESTAMPADD相同。

  • 参数类型:

    1. timestampdiff(varchar, timestamp, timestamp)
    2. timestampdiff(varchar, date, date)
    3. timestampdiff(varchar, datetime, datetime)
    4. timestampdiff(varchar, varchar, varchar)
  • 返回值类型:BIGINT。

  • 示例:

    1. select timestampdiff(second,datetime '2003-02-01 10:12:13',datetime '2003-05-01 10:12:13')as result;
    2. +---------+
    3. | result |
    4. +---------+
    5. | 7689600 |
    1. select timestampdiff(minute,datetime '2003-02-01 10:12:13',datetime '2003-05-01 10:12:13')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | 128160 |
    1. select timestampdiff(hour,datetime '2003-02-01 10:12:13',datetime '2003-05-01 10:12:13')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | 2136 |
    1. select timestampdiff(day,timestamp '2003-02-01',timestamp '2003-05-01')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | 89 |
    1. select timestampdiff(day,date '2003-02-01',date '2003-05-01');
    2. +------------------------------------------------------------+
    3. | timestampdiff('day', DATE '2003-02-01', DATE '2003-05-01') |
    4. +------------------------------------------------------------+
    5. | 89 |
    1. select timestampdiff(day,datetime '2003-02-01 10:12:13',datetime '2003-05-01 10:12:13')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | 89 |
    1. select timestampdiff(day,'2003-02-01','2003-05-01');
    2. +--------------------------------------------------+
    3. | timestampdiff('day', '2003-02-01', '2003-05-01') |
    4. +--------------------------------------------------+
    5. | 89 |
    1. select timestampdiff(week,'2003-02-01','2003-05-01');
    2. +---------------------------------------------------+
    3. | timestampdiff('week', '2003-02-01', '2003-05-01') |
    4. +---------------------------------------------------+
    5. | 12 |
    1. select timestampdiff(quarter,'2003-02-01','2003-05-01');
    2. +------------------------------------------------------+
    3. | timestampdiff('quarter', '2003-02-01', '2003-05-01') |
    4. +------------------------------------------------------+
    5. | 1 |
    1. select timestampdiff(month,'2003-02-01','2003-05-01');
    2. +----------------------------------------------------+
    3. | timestampdiff('month', '2003-02-01', '2003-05-01') |
    4. +----------------------------------------------------+
    5. | 3 |
    1. select timestampdiff(year,datetime '2003-02-01 10:12:13',datetime '2001-05-01 10:12:13')as result;
    2. +--------+
    3. | result |
    4. +--------+
    5. | -1 |

TO_DAYS

  1. TO_DAYS(date)
  • 命令说明:根据给定日期date,返回自0年开始的天数。

  • 参数类型:

    1. to_days(date)
    2. to_days(time)
    3. to_days(varchar)
    4. to_days(timestamp)
    5. to_days(datetime)
  • 返回值类型:BIGINT。

  • 示例:

    1. select to_days(date '2018-12-12');
    2. +----------------------------+
    3. | to_days(DATE '2018-12-12') |
    4. +----------------------------+
    5. | 737405 |
    1. select to_days(time '12:12:12');
    2. +--------------------------+
    3. | to_days(TIME '12:12:12') |
    4. +--------------------------+
    5. | 737572 |
    1. select to_days(now());
    2. +----------------+
    3. | to_days(now()) |
    4. +----------------+
    5. | 737572 |

    上述查询等价于to_days(curdate())

    1. select to_days(curdate());
    2. +--------------------+
    3. | to_days(curdate()) |
    4. +--------------------+
    5. | 737573 |
    1. select to_days(datetime '2019-09-08 12:12:12');
    2. +-----------------------------------------+
    3. | to_days(DATETIME '2019-09-08 12:12:12') |
    4. +-----------------------------------------+
    5. | 737675 |
    1. select to_days('2019-09-08 12:12:12');
    2. +--------------------------------+
    3. | to_days('2019-09-08 12:12:12') |
    4. +--------------------------------+
    5. | 737675 |
    1. select to_days(timestamp '2019-09-08 12:12:12');
    2. +------------------------------------------+
    3. | to_days(TIMESTAMP '2019-09-08 12:12:12') |
    4. +------------------------------------------+
    5. | 737675 |

TO_SECONDS

  1. TO_SECONDS(expr)
  • 命令说明:根据给定的expr,返回自0年开始的秒数。

  • 参数类型:

    1. to_seconds(date)
    2. to_seconds(datetime)
    3. to_seconds(timestamp)
    4. to_seconds(varchar)
    5. to_seconds(time)
  • 返回值类型:BIGINT。

  • 示例:

    1. select to_seconds(date '2019-09-08');
    2. +-------------------------------+
    3. | to_seconds(DATE '2019-09-08') |
    4. +-------------------------------+
    5. | 63735120000 |
    1. select to_seconds(datetime '2019-09-08 09:09:00');
    2. +--------------------------------------------+
    3. | to_seconds(DATETIME '2019-09-08 09:09:00') |
    4. +--------------------------------------------+
    5. | 63735152940 |
    1. select to_seconds(timestamp '2019-09-08 09:09:00');
    2. +---------------------------------------------+
    3. | to_seconds(TIMESTAMP '2019-09-08 09:09:00') |
    4. +---------------------------------------------+
    5. | 63735152940 |

    执行以下SQL,系统将返回'09:09:00'加上curdate()后的结果。

    1. select to_seconds(time '09:09:00');
    2. +-----------------------------+
    3. | to_seconds(TIME '09:09:00') |
    4. +-----------------------------+
    5. | 63726253740 |
    1. select to_seconds('2019-09-08');
    2. +--------------------------+
    3. | to_seconds('2019-09-08') |
    4. +--------------------------+
    5. | 63735120000 |

UNIX_TIMESTAMP

  1. UNIX_TIMESTAMP([date])
  • 命令说明:UNIX_TIMESTAMP()返回自'1970-01-01 00:00:00' UTC以来秒数的Unix时间戳。UNIX_TIMESTAMP(date) 将参数的值返回为'1970-01-01 00:00:00'UTC后的秒数的Unix时间戳。

  • 参数类型:

    1. unix_timestamp()
    2. unix_timestamp(varchar)
    3. unix_timestamp(timestamp)
    4. unix_timestamp(date)
    5. unix_timestamp(datetime)
  • 返回值类型:BIGINT。

  • 示例:

    1. select unix_timestamp();
    2. +------------------+
    3. | unix_timestamp() |
    4. +------------------+
    5. | 1558935850 |
    1. select unix_timestamp(timestamp '2019-09-08 12:12:12');
    2. +-------------------------------------------------+
    3. | unix_timestamp(TIMESTAMP '2019-09-08 12:12:12') |
    4. +-------------------------------------------------+
    5. | 1567915932 |
    1. select unix_timestamp(date '2019-09-08');
    2. +-----------------------------------+
    3. | unix_timestamp(DATE '2019-09-08') |
    4. +-----------------------------------+
    5. | 1567872000 |
    1. select unix_timestamp(datetime '2019-09-08 12:12:12');
    2. +------------------------------------------------+
    3. | unix_timestamp(DATETIME '2019-09-08 12:12:12') |
    4. +------------------------------------------------+
    5. | 1567915932 |
    1. select unix_timestamp('2019-09-08 12:12:12');
    2. +---------------------------------------+
    3. | unix_timestamp('2019-09-08 12:12:12') |
    4. +---------------------------------------+
    5. | 1567915932 |

UTC_DATE

  1. UTC_DATE()
  • 命令说明:返回UTC日期。

  • 返回值类型:VARCHAR。

  • 示例:

    1. select utc_date();
    2. +------------+
    3. | utc_date() |
    4. +------------+
    5. | 2019-05-27 |

UTC_TIME

  1. UTC_TIME()
  • 命令说明:返回UTC时间。

  • 返回值类型:VARCHAR。

  • 示例:

    1. select utc_time();
    2. +------------+
    3. | utc_time() |
    4. +------------+
    5. | 05:53:19 |

UTC_TIMESTAMP

  1. utc_timestamp()
  • 命令说明:返回UTC时间戳。

  • 返回值类型:VARCHAR。

  • 示例:

    1. select utc_timestamp();
    2. +---------------------+
    3. | utc_timestamp() |
    4. +---------------------+
    5. | 2019-05-27 05:55:15 |

WEEK

  1. WEEK(date[,mode])
  • 命令说明:返回date对应的周数,即date是日期年份中的哪一周。

    • date是要获取周数的日期。

    • mode可选参数,用于确定周数计算的逻辑。它允许您指定本周是从星期一还是星期日开始,返回的周数应在052之间或053之间。mode支持的格式如下表所示。

      模式 一周的第一天 范围
      0 星期日 0-53
      1 星期一 0-53
      2 星期日 1-53
      3 星期一 1-53
      4 星期日 0-53
      5 星期一 0-53
      6 星期日 1-53
      7 星期一 1-53
  • 参数类型:

    1. week(varchar)
    2. week(varchar, bigint)
    3. week(date)
    4. week(date, bigint)
    5. week(datetime)
    6. week(datetime, bigint)
    7. week(timestamp)
    8. week(timestamp, bigint)
  • 返回值类型:BIGINT。

  • 示例:

    1. select week('2019-05-27');
    2. +--------------------+
    3. | week('2019-05-27') |
    4. +--------------------+
    5. | 21 |
    1. select week('2008-02-20',1);
    2. +-----------------------+
    3. | week('2008-02-20', 1) |
    4. +-----------------------+
    5. | 8 |
    1. select week(date '2008-02-20');
    2. +-------------------------+
    3. | week(DATE '2008-02-20') |
    4. +-------------------------+
    5. | 7 |
    1. select week(date '2008-02-20',1);
    2. +----------------------------+
    3. | week(DATE '2008-02-20', 1) |
    4. +----------------------------+
    5. | 8 |
    1. select week(datetime '2008-02-20 00:00:00',1);
    2. +-----------------------------------------+
    3. | week(DATETIME '2008-02-20 00:00:00', 1) |
    4. +-----------------------------------------+
    5. | 8 |
    1. select week(datetime '2008-02-20 00:00:00');
    2. +--------------------------------------+
    3. | week(DATETIME '2008-02-20 00:00:00') |
    4. +--------------------------------------+
    5. | 7 |
    1. select week(timestamp '2008-02-20 00:00:00');
    2. +---------------------------------------+
    3. | week(TIMESTAMP '2008-02-20 00:00:00') |
    4. +---------------------------------------+
    5. | 7 |
    1. select week(timestamp '2008-02-20 00:00:00',1);
    2. +------------------------------------------+
    3. | week(TIMESTAMP '2008-02-20 00:00:00', 1) |
    4. +------------------------------------------+
    5. | 8 |

WEEKDAY

  1. WEEKDAY(date)
  • 命令说明:返回date对应的工作日即0= Monday,1= Tuesday,... 6= Sunday

  • 参数类型:

    1. weekday(timestamp)
    2. weekday(datetime)
    3. weekday(date)
    4. weekday(varchar)
  • 返回值类型:BIGINT。

  • 示例:

    1. select weekday(timestamp '2019-05-27 00:09:00');
    2. +------------------------------------------+
    3. | weekday(TIMESTAMP '2019-05-27 00:09:00') |
    4. +------------------------------------------+
    5. | 0 |
    1. select weekday(datetime '2019-05-27 00:09:00');
    2. +-----------------------------------------+
    3. | weekday(DATETIME '2019-05-27 00:09:00') |
    4. +-----------------------------------------+
    5. | 0 |
    1. select weekday(date '2019-05-27 00:09:00');
    2. +-------------------------------------+
    3. | weekday(DATE '2019-05-27 00:09:00') |
    4. +-------------------------------------+
    5. | 0 |
    1. select weekday('2019-05-27');
    2. +-----------------------+
    3. | weekday('2019-05-27') |
    4. +-----------------------+
    5. | 0 |

    WEEKOFYEAR

  1. WEEKOFYEAR(date)
  • 命令说明:返回date对应的日历周,取值范围为[1,53]

  • 参数类型:

    1. weekofyear(timestamp)
    2. weekofyear(datetime)
    3. weekofyear(date)
    4. weekofyear(varchar)
  • 返回值类型:BIGINT。

  • 示例:

    1. select weekofyear(timestamp '2019-05-27 09:00:00');
    2. +---------------------------------------------+
    3. | weekofyear(TIMESTAMP '2019-05-27 09:00:00') |
    4. +---------------------------------------------+
    5. | 22 |
    1. select weekofyear(datetime '2019-05-27 09:00:00');
    2. +--------------------------------------------+
    3. | weekofyear(DATETIME '2019-05-27 09:00:00') |
    4. +--------------------------------------------+
    5. | 22 |
    1. select weekofyear(date '2019-05-27');
    2. +-------------------------------+
    3. | weekofyear(DATE '2019-05-27') |
    4. +-------------------------------+
    5. | 22 |
    1. select weekofyear('2019-05-27');
    2. +--------------------------+
    3. | weekofyear('2019-05-27') |
    4. +--------------------------+
    5. | 22 |

YEAR

  1. YEAR(date)
  • 命令说明:返回date中的年份。

  • 参数类型:

    1. year(timestamp)
    2. year(datetime)
    3. year(date)
    4. year(time)
    5. year(varchar)
  • 返回值类型:BIGINT。

  • 示例:

    1. select year(timestamp '2019-05-27 00:00:00');
    2. +---------------------------------------+
    3. | year(TIMESTAMP '2019-05-27 00:00:00') |
    4. +---------------------------------------+
    5. | 2019 |
    1. select year(datetime '2019-05-27 00:00:00');
    2. +--------------------------------------+
    3. | year(DATETIME '2019-05-27 00:00:00') |
    4. +--------------------------------------+
    5. | 2019 |
    1. select year(date '2019-05-27');
    2. +-------------------------+
    3. | year(DATE '2019-05-27') |
    4. +-------------------------+
    5. | 2019 |

    执行以下SQL,系统将返回'00:00:00'加上curdate时间部分后的结果,结果数据类型为字符串。

    1. select year(time '00:00:00');
    2. +-----------------------+
    3. | year(TIME '00:00:00') |
    4. +-----------------------+
    5. | 2019 |
    1. select year('2019-05-27');
    2. +--------------------+
    3. | year('2019-05-27') |
    4. +--------------------+
    5. | 2019 |

YEARWEEK

  1. YEARWEEK(date)
  2. YEARWEEK(date,mode)
  • 命令说明:返回日期的年份和星期。

    返回结果中的年份可能与一年中第一周和最后一周的日期参数中的年份不同。

    modeWEEK函数中的mode作用相同。对于单参数语法,mode值为0

  • 参数类型:

    1. yearweek(timestamp)
    2. yearweek(timestamp, bigint)
    3. yearweek(datetime)
    4. yearweek(datetime, bigint)
    5. yearweek(date, bigint)
    6. yearweek(date)
    7. yearweek(varchar)
    8. yearweek(varchar, bigint)
  • 返回值类型:BIGINT。

  • 示例:

    1. select yearweek(timestamp '2019-05-27 00:00:00');
    2. +-------------------------------------------+
    3. | yearweek(TIMESTAMP '2019-05-27 00:00:00') |
    4. +-------------------------------------------+
    5. | 201921 |
    1. select yearweek(timestamp '2019-05-27 00:00:00',1);
    2. +----------------------------------------------+
    3. | yearweek(TIMESTAMP '2019-05-27 00:00:00', 1) |
    4. +----------------------------------------------+
    5. | 201922 |
    1. select yearweek(datetime '2019-05-27 00:00:00');
    2. +------------------------------------------+
    3. | yearweek(DATETIME '2019-05-27 00:00:00') |
    4. +------------------------------------------+
    5. | 201921 |
    1. select yearweek(datetime '2019-05-27 00:00:00',1);
    2. +---------------------------------------------+
    3. | yearweek(DATETIME '2019-05-27 00:00:00', 1) |
    4. +---------------------------------------------+
    5. | 201922 |
    1. select yearweek(date '2019-05-27',1);
    2. +--------------------------------+
    3. | yearweek(DATE '2019-05-27', 1) |
    4. +--------------------------------+
    5. | 201922 |
    1. select yearweek(date '2019-05-27');
    2. +-----------------------------+
    3. | yearweek(DATE '2019-05-27') |
    4. +-----------------------------+
    5. | 201921 |
    1. select yearweek('2019-05-27');
    2. +------------------------+
    3. | yearweek('2019-05-27') |
    4. +------------------------+
    5. | 201921 |
    1. select yearweek('2019-05-27',1);
    2. +---------------------------+
    3. | yearweek('2019-05-27', 1) |
    4. +---------------------------+
    5. | 201922 |