时间和日期函数
所有文档

          数据仓库 Palo

          时间和日期函数

          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.timediff
          47.to_date
          48.to_days
          49.weeks_add
          50.weekofyear
          51.weeks_diff
          52.weeks_sub
          53.quarter
          54.year
          55.year_floor
          56.years_add
          57.years_diff
          58.years_sub

          ADD_MONTHS

          Description

          add_months(timestamp date, int months)
          
          add_months(timestamp date, bigint months)
          • 功能:返回指定date加上months个月的新date。和months_add()相同
          • 返回类型:timestamp类型

          Example

          如果这个月的这一日并不存在于目标月中,那么结果将是那个月的最后一天;如果参数中的months是负数,则是求先前的月。

           mysql> select now(), add_months(now(), 2);
           +---------------------+---------------------+
           | now()               | add_months(now(), 2)|
           +---------------------+---------------------+
           | 2016-05-31 10:47:00 | 2016-07-31 10:47:00 |
           +---------------------+---------------------+
           1 row in set (0.01 sec)
          
           mysql> select now(), add_months(now(), 1);
           +---------------------+---------------------+
           | now()               | add_months(now(), 1)|
           +---------------------+---------------------+
           | 2016-05-31 10:47:14 | 2016-06-30 10:47:14 |
           +---------------------+---------------------+
           1 row in set (0.01 sec)
          
           mysql> select now(), add_months(now(), -1);
           +---------------------+----------------------+
           | now()               | add_months(now(), -1)|
           +---------------------+----------------------+
           | 2016-05-31 10:47:31 | 2016-04-30 10:47:31  |
           +---------------------+----------------------+
           1 row in set (0.01 sec)

          Keywords

          add_months

          ADDDATE

          Description

          adddate(timestamp startdate, int days)
          
          adddate(timestamp startdate, bigint days)
          • 功能:给startdate加上指定的天数
          • 返回类型:timestamp类型

          Example

           mysql> select adddate(date_column, 10) from big_table limit 1;
           +-------------------------------+
           |   adddate(date_column, 10)    |
           +-------------------------------+
           |          2014-01-11 00:00:00  |
           +-------------------------------+

          Keywords

          adddate

          CONVERT_TZ

          Description

          convert_tz(timestamp date, string from, string to)
          • 功能:转化指定时间的时区
          • 返回类型:timestamp类型

          Example

          mysql> select convert_tz('2020-12-20 12:00:00','+00:00','+10:00');
          +-------------------------------------------------------+
          | convert_tz('2020-12-20 12:00:00', '+00:00', '+10:00') |
          +-------------------------------------------------------+
          | 2020-12-20 22:00:00                                   |
          +-------------------------------------------------------+
          1 row in set (0.09 sec)

          Keywords

          convert_tz 

          CURDATE,CURRENT_DATE

          Description

          curdate()
          current_date()
          • 功能:获取当前的日期
          • 返回类型:timestamp类型

          Example

          mysql> select curdate();
          +------------+
          | curdate()  |
          +------------+
          | 2020-12-25 |
          +------------+
          1 row in set (0.03 sec)
          
          mysql> select current_date();
          +----------------+
          | current_date() |
          +----------------+
          | 2020-12-25     |
          +----------------+
          1 row in set (0.02 sec)

          Keywords

          curdate,current_date

          CURRENT_TIMESTAMP

          Description

          current_timestamp()
          • 功能:和now()函数功能相同,获取当前的时间
          • 返回类型:timestamp类型

          Example

          mysql> select current_timestamp();
          +---------------------+
          | current_timestamp() |
          +---------------------+
          | 2020-12-25 14:13:10 |
          +---------------------+
          1 row in set (0.03 sec)

          Keywords

          CURRENT_TIMESTAMP

          CURTIME,CURRENT_TIME

          Description

          curtime()
          current_time()
          • 功能:获取当前的时间,不包含日期字段
          • 返回类型:timestamp类型

          Example

          mysql> select curtime();
          +-----------+
          | curtime() |
          +-----------+
          | 14:24:07  |
          +-----------+
          1 row in set (0.02 sec)
          
          mysql> select current_time();
          +----------------+
          | current_time() |
          +----------------+
          | 14:24:22       |
          +----------------+
          1 row in set (0.08 sec)

          Keywords

          curtime,current_time

          DATE_ADD

          Description

          date_add(timestamp startdate, int days)
          • 功能:给TIMESTAMP值加上指定的天数。第一个参数可以是字符串,如果字符串符合TIMESTAMP数据类型的格式,该字符串会自动转成TIMESTAMP类型。第二个参数是时间间隔。
          • 返回类型:timestamp类型

          Example

          mysql> select date_add('2020-12-25',20);
          +-------------------------------------+
          | date_add('2020-12-25 00:00:00', 20) |
          +-------------------------------------+
          | 2021-01-14 00:00:00                 |
          +-------------------------------------+
          1 row in set (0.03 sec)

          Keywords

          date_add
             

          DATE_SUB

          Description

          date_sub(timestamp startdate, int days)
          • 功能:给TIMESTAMP值减去指定的天数。第一个参数可以是字符串,如果字符串符合TIMESTAMP数据类型的格式,该字符串会自动转成成TIMESTAMP类型。第二个参数是时间间隔。
          • 返回类型:timestamp类型

          Example

          mysql> select date_sub('2020-12-25',20);
          +-------------------------------------+
          | date_sub('2020-12-25 00:00:00', 20) |
          +-------------------------------------+
          | 2020-12-05 00:00:00                 |
          +-------------------------------------+
          1 row in set (0.02 sec)

          Keywords

          date_sub

          DATE_FORMAT

          Description

          date_format(timestamp day, string fmt)
          • 功能:将日期类型按照format的类型转化为字符串,当前支持最大128字节的字符串,如果返回长度超过128,则返回NULL。
          • 返回类型:string类型
          • format的含义如下:

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

          Example

          mysql> select date_format('2009-10-04 22:23:00', '%W %M %Y');
          +------------------------------------------------+
          | date_format('2009-10-04 22:23:00', '%W %M %Y') |
          +------------------------------------------------+
          | Sunday October 2009                            |
          +------------------------------------------------+
          1 row in set (0.01 sec)
          
          mysql> select date_format('2007-10-04 22:23:00', '%H:%i:%s');
          +------------------------------------------------+
          | date_format('2007-10-04 22:23:00', '%H:%i:%s') |
          +------------------------------------------------+
          | 22:23:00                                       |
          +------------------------------------------------+
          1 row in set (0.01 sec)
          
          mysql> select date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
          +------------------------------------------------------------+
          | date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
          +------------------------------------------------------------+
          | 4th 00 Thu 04 10 Oct 277                                   |
          +------------------------------------------------------------+
          1 row in set (0.03 sec)
          
          mysql> select date_format('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
          +------------------------------------------------------------+
          | date_format('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
          +------------------------------------------------------------+
          | 22 22 10 10:23:00 PM 22:23:00 00 6                         |
          +------------------------------------------------------------+
          1 row in set (0.00 sec)

          Keywords

          date_format

          DATEDIFF

          Description

          datediff(string enddate, string startdate)
          • 功能:返回两个日期的天数差值
          • 返回类型:int类型

          Example

          mysql> select datediff('2020-12-25','2019-11-20');
          +--------------------------------------------------------+
          | datediff('2020-12-25 00:00:00', '2019-11-20 00:00:00') |
          +--------------------------------------------------------+
          |                                                    401 |
          +--------------------------------------------------------+
          1 row in set (0.03 sec)

          Keywords

          datediff

          DAY,DAYOFMONTH

          Description

          day(string date)
          dayofmonth(string date)
          • 功能:返回日期中的天字段
          • 返回类型:int类型

          Example

          mysql> select day('2020-12-25');
          +----------------------------+
          | day('2020-12-25 00:00:00') |
          +----------------------------+
          |                         25 |
          +----------------------------+
          1 row in set (0.02 sec)
          
          mysql> select dayofmonth('2020-12-25');
          +-----------------------------------+
          | dayofmonth('2020-12-25 00:00:00') |
          +-----------------------------------+
          |                                25 |
          +-----------------------------------+
          1 row in set (0.07 sec)

          Keywords

          day,dayofmonth

          DAYNAME

          Description

          dayname(timestamp date)
          • 功能:返回指定日期是星期几(英文)
          • 返回类型:字符串类型

          Example

          mysql>  select dayname('2020-12-25');
          +--------------------------------+
          | dayname('2020-12-25 00:00:00') |
          +--------------------------------+
          | Friday                         |
          +--------------------------------+
          1 row in set (0.04 sec)

          Keywords

          dayname 

          DAYOFWEEK

          Description

          dayofweek(timestamp date)
          • 功能:返回指定日期为星期几(数字)

            说明: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.

          • 返回类型:int类型

          Example

          mysql> select dayofweek('2020-12-25');
          +----------------------------------+
          | dayofweek('2020-12-25 00:00:00') |
          +----------------------------------+
          |                                6 |
          +----------------------------------+
          1 row in set (0.07 sec)

          Keywords

          dayofweek

          DAYOFYEAR

          Description

          dayofyear(timestamp date)
          • 功能:返回指定日期是当年的第几天
          • 返回类型:int类型

          Example

          mysql> select dayofyear('2020-12-25');
          +----------------------------------+
          | dayofyear('2020-12-25 00:00:00') |
          +----------------------------------+
          |                              360 |
          +----------------------------------+
          1 row in set (0.02 sec)

          Keywords

          dayofyear
              

          DAYS_ADD

          Description

          days_add(timestamp startdate, int days)
          
          days_add(timestamp startdate, bigint days)
          • 功能:给startdate加上指定的天数,和date_add函数相似,差别在于本函数的参数是TIMESTAMP类型而不是string类型。
          • 返回类型:timestamp类型

          Example

          mysql> select days_add('2020-12-25',10);
          +-------------------------------------+
          | days_add('2020-12-25 00:00:00', 10) |
          +-------------------------------------+
          | 2021-01-04 00:00:00                 |
          +-------------------------------------+
          1 row in set (0.02 sec)

          Keywords

          days_add

          DAYS_DIFF

          Description

          days_diff(timestamp enddate, timestamp startdate)
          • 功能:开始时间到结束时间相差几天
          • 返回类型:int类型

          Example

          mysql> select days_diff('2020-12-25','2020-10-1');
          +---------------------------------------------------------+
          | days_diff('2020-12-25 00:00:00', '2020-10-01 00:00:00') |
          +---------------------------------------------------------+
          |                                                      85 |
          +---------------------------------------------------------+
          1 row in set (0.06 sec)

          Keywords

          days_diff

          DAYS_SUB

          Description

          days_sub(timestamp startdate, int days)
          
          days_sub(timestamp startdate, bigint days)
          • 功能:给startdate减去指定的天数,和date_sub函数相似,差别在于本函数的参数是TIMESTAMP类型而不是string类型。
          • 返回类型:timestamp类型

          Example

          mysql> select days_sub('2020-12-25',20);
          +-------------------------------------+
          | days_sub('2020-12-25 00:00:00', 20) |
          +-------------------------------------+
          | 2020-12-05 00:00:00                 |
          +-------------------------------------+
          1 row in set (0.03 sec)

          Keywords

          days_sub

          EXTRACT

          Description

          extract(unit FROM timestamp)
          • 功能:提取timestamp某个指定单位的值。单位可以为year, month, day, hour, minute或者second
          • 返回类型:int类型

          Example

          mysql> select now() as right_now,
              -> extract(year from now()) as this_year,
              -> extract(month from now()) as this_month;
          +---------------------+-----------+------------+
          | right_now           | this_year | this_month |
          +---------------------+-----------+------------+
          | 2020-12-25 18:27:13 |      2020 |         12 |
          +---------------------+-----------+------------+
          1 row in set (0.02 sec)
          
          mysql> select now() as right_now,
              -> extract(day from now()) as this_day,
              -> extract(hour from now()) as this_hour;
          +---------------------+----------+-----------+
          | right_now           | this_day | this_hour |
          +---------------------+----------+-----------+
          | 2020-12-25 18:28:26 |       25 |        18 |
          +---------------------+----------+-----------+
          1 row in set (0.02 sec)

          Keywords

          extract

          FROM_DAYS

          Description

          from_days(int days)
          • 功能:返回从0000-00-00开始向后指定天数的日期
          • 返回类型:timestamp

          Example

          mysql> select from_days(10000);
          +------------------+
          | from_days(10000) |
          +------------------+
          | 0027-05-19       |
          +------------------+
          1 row in set (0.09 sec)

          Keywords

          from_days

          FROM_UNIXTIME

          Description

          from_unixtime(bigint unixtime,[ string format])
          • 功能:将unix时间(自1970年1月1日起经过的秒数)转换成相应格式的日期类型
          • 返回类型:字符串类型
          • 使用说明:当前日期格式是大小写敏感的,用户尤其要区分小写m(表达分钟)和大写M(表达月份)。日期字符串的完整型式是"yyyy-MM-dd HH:mm:ss.SSSSSS",也可以只包含其中部分字段。

          Example

           mysql> select from_unixtime(100000);
           +-----------------------+
           | from_unixtime(100000) |
           +-----------------------+
           | 1970-01-02 11:46:40   |
           +-----------------------+
           1 row in set (0.01 sec)
          
           mysql> select from_unixtime(100000, 'yyyy-MM-dd');
           +-------------------------------------+
           | from_unixtime(100000, 'yyyy-MM-dd') |
           +-------------------------------------+
           | 1970-01-02                          |
           +-------------------------------------+
           1 row in set (0.00 sec)
           
           
           mysql> select from_unixtime(1392394861, 'yyyy-MM-dd');      
           +-----------------------------------------+
           | from_unixtime(1392394861, 'yyyy-MM-dd') |
           +-----------------------------------------+
           | 2014-02-15                              |
           +-----------------------------------------+
           1 row in set (0.00 sec)

          unix_timestamp()和from_unixtime()经常结合使用,将时间戳类型转换成指定格式的字符串。

          mysql> select from_unixtime(unix_timestamp(now()), 'yyyy-MM-dd');
          +--------------------------------------------------+
          | from_unixtime(unix_timestamp(now()), '%Y-%m-%d') |
          +--------------------------------------------------+
          | 2020-12-25                                       |
          +--------------------------------------------------+
          1 row in set (0.02 sec)
           
           mysql> select from_unixtime(unix_timestamp(now()), '%Y %D %M');
          +--------------------------------------------------+
          | from_unixtime(unix_timestamp(now()), '%Y %D %M') |
          +--------------------------------------------------+
          | 2020 25th December                               |
          +--------------------------------------------------+
          1 row in set (0.03 sec)

          UNIX_TIMESTAMP

          Description

          unix_timestamp()
          
          unix_timestamp(string datetime)
          
          unix_timestamp(string datetime, string format)
          
          unix_timestamp(timestamp datetime)
          • 功能:返回当前时间的时间戳(相对1970年1月1日的秒数)或者从一个指定的日期和时间转换成时间戳。返回的时间戳是相对于格林尼治时区的时间戳。
          • 返回类型:bigint类型

          Example

          mysql> select unix_timestamp();
          +------------------+
          | unix_timestamp() |
          +------------------+
          |       1608896139 |
          +------------------+
          1 row in set (0.03 sec)

          Keywords

          unix_timestamp

          UTC_TIMESTAMP

          Description

          utc_timestamp()
          • 功能:返回当前格林尼治时区的时间
          • 返回类型:timestamp

          Example

          mysql> select utc_timestamp();
          +---------------------+
          | utc_timestamp()     |
          +---------------------+
          | 2020-12-25 11:39:30 |
          +---------------------+
          1 row in set (0.02 sec)

          Keywords

          utc_timestamp

          HOUR

          Description

          hour(string date)
          • 功能:返回字符串所表达日期的小时字段
          • 返回类型:int类型

          Example

          mysql> select hour('2020-12-25 23:46');
          +--------------------------+
          | hour('2020-12-25 23:46') |
          +--------------------------+
          |                       23 |
          +--------------------------+
          1 row in set (0.02 sec)

          Keywords

          hour

          HOURS_ADD

          Description

          hours_add(timestamp date, int hours)
          
          hours_add(timestamp date, bigint hours)
          • 功能:返回指定的日期加上若干小时后的时间
          • 返回类型:timestamp

          Example

          mysql> select hours_add('2020-12-25 18:00', 24);
          +-----------------------------------+
          | hours_add('2020-12-25 18:00', 24) |
          +-----------------------------------+
          | 2020-12-26 18:00:00               |
          +-----------------------------------+
          1 row in set (0.06 sec)
          
          mysql> select hours_add(now(), 24);
          +----------------------+
          | hours_add(now(), 24) |
          +----------------------+
          | 2020-12-26 19:45:40  |
          +----------------------+
          1 row in set (0.02 sec)

          Keywords

          hours_add

          HOURS_DIFF

          Description

          hours_diff(timestamp enddate, timestamp startdate)
          • 功能:开始时间到结束时间相差几个小时
          • 返回类型:int类型

          Example

          mysql> select hours_diff('2020-12-26 08:00','2020-12-25 20:00');
          +----------------------------------------------------+
          | hours_diff('2020-12-26 08:00', '2020-12-25 20:00') |
          +----------------------------------------------------+
          |                                                 12 |
          +----------------------------------------------------+
          1 row in set (0.04 sec)

          Keywords

          hours_diff

          HOURS_SUB

          Description

          hours_sub(timestamp date, int hours)
          
          hours_sub(timestamp date, bigint hours)
          • 功能:返回指定的日期减去若干小时后的时间
          • 返回类型:timestamp

          Example

          mysql> select hours_sub('2020-12-25 20:00',12);
          +-----------------------------------+
          | hours_sub('2020-12-25 20:00', 12) |
          +-----------------------------------+
          | 2020-12-25 08:00:00               |
          +-----------------------------------+
          1 row in set (0.05 sec)

          Keywords

          hours_sub

          LOCALTIME,LOCALTIMESTAMP

          Description

          localtime ()
          
          localtimestamp()
          • 功能:和now()函数功能相同,获取当前的时间
          • 返回类型:timestamp类型

          Example

          mysql> select localtime();
          +---------------------+
          | localtime()         |
          +---------------------+
          | 2020-12-25 19:52:56 |
          +---------------------+
          1 row in set (0.04 sec)
          
          mysql> select localtimestamp();
          +---------------------+
          | localtimestamp()    |
          +---------------------+
          | 2020-12-25 19:53:10 |
          +---------------------+
          1 row in set (0.02 sec)

          Keywords

          localtime,localtimestamp

          MICROSECONDS_ADD

          Description

          microseconds_add(timestamp date, int microseconds)
          
          microseconds_add(timestamp date, bigint microseconds)
          • 功能:返回指定的日期加上若干微秒后的时间
          • 返回类型:timestamp

          Example

          mysql> select microseconds_add('2020-12-25',1000000);
          +--------------------------------------------------+
          | microseconds_add('2020-12-25 00:00:00', 1000000) |
          +--------------------------------------------------+
          | 2020-12-25 00:00:01                              |
          +--------------------------------------------------+
          1 row in set (0.02 sec)

          Keywords

          microseconds_add

          MICROSECONDS_SUB

          Description

          microseconds_sub(timestamp date, int microseconds)
          
          microseconds_sub(timestamp date, bigint microseconds)
          • 功能:返回指定的日期减去若干微秒后的时间
          • 返回类型:timestamp

          Example

          mysql> select microseconds_sub('2020-12-25',1000000);
          +--------------------------------------------------+
          | microseconds_sub('2020-12-25 00:00:00', 1000000) |
          +--------------------------------------------------+
          | 2020-12-24 23:59:59                              |
          +--------------------------------------------------+
          1 row in set (0.03 sec)

          Keywords

          microseconds_sub

          MINUTE

          Description

          minute(string date)
          • 功能:返回字符串所表达日期的分钟字段
          • 返回类型:int类型

          Example

          mysql> select minute('2020-12-25 20:25:35');
          +-------------------------------+
          | minute('2020-12-25 20:25:35') |
          +-------------------------------+
          |                            25 |
          +-------------------------------+
          1 row in set (0.04 sec)

          Keywords

          minute

          MINUTES_ADD

          Description

          minutes_add(timestamp date, int minutes)
          
          minutes_add(timestamp date, bigint minutes)
          • 功能:返回指定的日期加上若干分钟后的时间
          • 返回类型:timestamp

          Example

          mysql> select minutes_add('2020-12-25 20:00:00',25);
          +----------------------------------------+
          | minutes_add('2020-12-25 20:00:00', 25) |
          +----------------------------------------+
          | 2020-12-25 20:25:00                    |
          +----------------------------------------+
          1 row in set (0.02 sec)

          Keywords

          minutes_add

          MINUTES_DIFF

          Description

          minutes_diff(timestamp enddate, timestamp startdate)
          • 功能:开始时间到结束时间相差几分钟
          • 返回类型:int类型

          Example

          mysql> select minutes_diff('2020-12-25 22:00:00','2020-12-25 21:00:00');
          +------------------------------------------------------------+
          | minutes_diff('2020-12-25 22:00:00', '2020-12-25 21:00:00') |
          +------------------------------------------------------------+
          |                                                         60 |
          +------------------------------------------------------------+
          1 row in set (0.02 sec)

          Keywords

          minutes_diff

          MINUTES_SUB

          Description

          minutes_sub(timestamp date, int minutes)
          
          minutes_sub(timestamp date, bigint minutes)
          • 功能:返回指定的日期减去若干分钟后的时间
          • 返回类型:timestamp

          Example

          mysql> select minutes_sub('2020-12-25 20:00:00',25);
          +----------------------------------------+
          | minutes_sub('2020-12-25 20:00:00', 25) |
          +----------------------------------------+
          | 2020-12-25 19:35:00                    |
          +----------------------------------------+
          1 row in set (0.02 sec)

          Keywords

          minutes_sub

          MONTH

          Description

          month(string date)
          • 功能:返回字符串所表达的日期的月份字段(数字)
          • 返回类型:int类型

          Example

          mysql> select month('2020-12-25');
          +------------------------------+
          | month('2020-12-25 00:00:00') |
          +------------------------------+
          |                           12 |
          +------------------------------+
          1 row in set (0.02 sec)

          Keywords

          month

          MONTHNAME

          Description

          monthname(string date)
          • 功能:返回字符串所表达的日期的月份字段(英文)
          • 返回类型:字符串类型

          Example

          mysql> select monthname('2020-12-25');
          +----------------------------------+
          | monthname('2020-12-25 00:00:00') |
          +----------------------------------+
          | December                         |
          +----------------------------------+
          1 row in set (0.02 sec)

          Keywords

          MONTHS_ADD

          Description

          months_add(timestamp date, int months)
          
          months_add(timestamp date, bigint months)
          • 功能:返回指定的日期加上若干月份后的时间
          • 返回类型:timestamp

          Example

          mysql> select months_add('2020-12-25',2);
          +--------------------------------------+
          | months_add('2020-12-25 00:00:00', 2) |
          +--------------------------------------+
          | 2021-02-25 00:00:00                  |
          +--------------------------------------+
          1 row in set (0.03 sec)

          Keywords

          months_add

          MONTHS_DIFF

          Description

          months_diff(timestamp enddate, timestamp startdate)
          • 功能:开始时间到结束时间相差几个月
          • 返回类型:int类型

          Example

          mysql> select months_diff('2020-12-25','2020-05-25');
          +-----------------------------------------------------------+
          | months_diff('2020-12-25 00:00:00', '2020-05-25 00:00:00') |
          +-----------------------------------------------------------+
          |                                                         7 |
          +-----------------------------------------------------------+
          1 row in set (0.02 sec)

          Keywords

          months_diff

          MONTHS_SUB

          Description

          months_sub(timestamp date, int months)
          
          months_sub(timestamp date, bigint months)
          • 功能:返回指定的日期减去若干月份后的时间
          • 返回类型:timestamp

          Example

          mysql> select months_sub('2020-12-25',2);
          +--------------------------------------+
          | months_sub('2020-12-25 00:00:00', 2) |
          +--------------------------------------+
          | 2020-10-25 00:00:00                  |
          +--------------------------------------+
          1 row in set (0.02 sec)

          Keywords

          months_sub

          NOW

          Description

          now()
          • 功能:返回当前的日期和时间(东八区的时区)
          • 返回类型:timestamp

          Example

          mysql> select now();
          +---------------------+
          | now()               |
          +---------------------+
          | 2020-12-25 20:36:03 |
          +---------------------+
          1 row in set (0.20 sec)

          Keywords

          now

          SECOND

          Description

          second(string date)
          • 功能:返回字符串所表达的日期的秒字段
          • 返回类型:int 类型

          Example

          mysql> select now() as right_now,
              -> second(now());
          +---------------------+---------------+
          | right_now           | second(now()) |
          +---------------------+---------------+
          | 2020-12-25 20:44:25 |            25 |
          +---------------------+---------------+
          1 row in set (0.03 sec)

          Keywords

          second

          SECONDS_ADD

          Description

          seconds_add(timestamp date, int seconds)
          
          seconds_add(timestamp date, bigint seconds)
          • 功能:返回指定的日期加上若干秒后的时间
          • 返回类型:timestamp

          Example

          mysql> select now() as right_now,
              -> seconds_add(now(),20);
          +---------------------+------------------------+
          | right_now           | seconds_add(now(), 20) |
          +---------------------+------------------------+
          | 2020-12-25 20:45:07 | 2020-12-25 20:45:27    |
          +---------------------+------------------------+
          1 row in set (0.02 sec)

          Keywords

          seconds_add

          SECONDS_DIFF

          Description

          seconds_diff(timestamp enddate, timestamp startdate)
          • 功能:开始时间到结束时间相差几秒钟
          • 返回类型:int类型

          Example

          mysql> select seconds_diff('2020-12-25 08:00:00','2020-12-25 07:00:00');
          +------------------------------------------------------------+
          | seconds_diff('2020-12-25 08:00:00', '2020-12-25 07:00:00') |
          +------------------------------------------------------------+
          |                                                       3600 |
          +------------------------------------------------------------+
          1 row in set (0.05 sec)

          Keywords

          seconds_diff

          SECONDS_SUB

          Description

          seconds_sub(timestamp date, int seconds)
          
          seconds_sub(timestamp date, bigint seconds)
          • 功能:返回指定的日期减去若干秒后的时间
          • 返回类型:timestamp

          Example

          mysql> select now() as right_now,
              -> seconds_sub(now(),20);
          +---------------------+------------------------+
          | right_now           | seconds_sub(now(), 20) |
          +---------------------+------------------------+
          | 2020-12-25 20:46:10 | 2020-12-25 20:45:50    |
          +---------------------+------------------------+
          1 row in set (0.03 sec)

          Keywords

          seconds_sub

          SUBDATE

          Description

          subdate(timestamp startdate, int days)
          
          subdate(timestamp startdate, bigint days)
          • 功能:从startdate的时间减去若干天后的时间。和date_sub()函数相似,但是本函数的第一个参数是确切的TIMESTAMP,而非可以转成TIMESTAMP类型的字符串。
          • 返回类型:timestamp

          Example

          mysql> select subdate('2020-12-25',10);
          +------------------------------------+
          | subdate('2020-12-25 00:00:00', 10) |
          +------------------------------------+
          | 2020-12-15 00:00:00                |
          +------------------------------------+
          1 row in set (0.02 sec)

          Keywords

          subdate

          STR_TO_DATE

          Description

          str_to_date(string str, string format)
          • 功能:通过format指定的方式将str转化为timestamp类型,如果转化结果不对返回NULL。支持的format格式与date_format一致。
          • 返回类型:timestamp

          Example

          mysql> select str_to_date('20201225 1130','%Y%m%d %h%i');
          +---------------------------------------------+
          | str_to_date('20201225 1130', '%Y%m%d %h%i') |
          +---------------------------------------------+
          | 2020-12-25 11:30:00                         |
          +---------------------------------------------+
          1 row in set (0.03 sec)

          Keywords

          str_to_date

          TIMEDIFF

          Description

          timediff(string enddate, string startdate)
          • 功能:返回两个时间的时间戳差值
          • 返回类型:int类型

          Example

          mysql> select timediff('20201225','20201224');
          +--------------------------------------------------------+
          | timediff('2020-12-25 00:00:00', '2020-12-24 00:00:00') |
          +--------------------------------------------------------+
          | 24:00:00                                               |
          +--------------------------------------------------------+
          1 row in set (0.02 sec)

          Keywords

          timediff

          TO_DATE

          Description

          to_date(timestamp)
          • 功能:返回timestamp的date域
          • 返回类型:string类型

          Example

          mysql> select now() as right_now,
              -> concat('The date today is ',to_date(now()),'.') as date_announcement;
          +---------------------+-------------------------------+
          | right_now           | date_announcement             |
          +---------------------+-------------------------------+
          | 2020-12-25 21:08:02 | The date today is 2020-12-25. |
          +---------------------+-------------------------------+
          1 row in set (0.03 sec)

          Keywords

          to_date

          TO_DAYS

          Description

          to_days(timestamp date)
          • 功能:返回从0000-00-00开始到指定日期的天数
          • 返回类型:int类型

          Example

          mysql> select to_days('0000-12-25');
          +-----------------------+
          | to_days('0000-12-25') |
          +-----------------------+
          |                   359 |
          +-----------------------+
          1 row in set (0.03 sec)

          Keywords

          to_days

          WEEK_ADD

          Description

          weeks_add(timestamp date, int weeks)
          
          weeks_add(timestamp date, bigint weeks)
          • 功能:返回指定的日期加上若干周后的时间
          • 返回类型:timestamp

          Example

          mysql> select weeks_add('2020-12-25',3);
          +-------------------------------------+
          | weeks_add('2020-12-25 00:00:00', 3) |
          +-------------------------------------+
          | 2021-01-15 00:00:00                 |
          +-------------------------------------+
          1 row in set (0.03 sec)

          Keywords

          weeks_add

          WEEKOFYEAR

          Description

          weekofyear(timestamp date)
          • 功能:获得一年中的第几周
          • 返回类型:int

          Example

          mysql> select weekofyear('2020-12-25');
          +-----------------------------------+
          | weekofyear('2020-12-25 00:00:00') |
          +-----------------------------------+
          |                                52 |
          +-----------------------------------+
          1 row in set (0.03 sec)

          Keywords

          weekofyear

          WEEK_DIFF

          Description

          weeks_diff(timestamp enddate, timestamp startdate)
          • 功能:开始时间到结束时间相差几个星期
          • 返回类型:int类型

          Example

          mysql> select weeks_diff('2020-12-25','2020-10-25');
          +----------------------------------------------------------+
          | weeks_diff('2020-12-25 00:00:00', '2020-10-25 00:00:00') |
          +----------------------------------------------------------+
          |                                                        8 |
          +----------------------------------------------------------+
          1 row in set (0.02 sec)

          Keywords

          weeks_diff

          WEEK_SUD

          Description

          weeks_sub(timestamp date, int weeks)
          
          weeks_sub(timestamp date, bigint weeks)
          • 功能:返回指定的日期减去若干周后的时间
          • 返回类型:timestamp

          Example

          mysql> select weeks_sub('2020-12-25',3);
          +-------------------------------------+
          | weeks_sub('2020-12-25 00:00:00', 3) |
          +-------------------------------------+
          | 2020-12-04 00:00:00                 |
          +-------------------------------------+
          1 row in set (0.28 sec)

          Keywords

          weeks_sub

          QUARTER

          Description

          quarter(timestamp date)
          • 功能:返回指定的日期所属季度
          • 返回类型:int

          Example

          mysql> select quarter('2020-12-25');
          +--------------------------------+
          | quarter('2020-12-25 00:00:00') |
          +--------------------------------+
          |                              4 |
          +--------------------------------+
          1 row in set (0.02 sec)

          Keywords

          quarter

          YEAR

          Description

          year(string date)
          • 功能:返回字符串所表达的日期的年字段
          • 返回类型:int类型

          Example

          mysql> select year('2020-12-25');
          +-----------------------------+
          | year('2020-12-25 00:00:00') |
          +-----------------------------+
          |                        2020 |
          +-----------------------------+
          1 row in set (0.02 sec)

          Keywords

          year

          YEAR_FLOOR

          Description

          year_floor(timestamp date)
          • 功能:指定时间向下舍入,保留字段级别到年
          • 返回类型:timestamp

          Example

          mysql> select year_floor('2020-12-25');
          +-----------------------------------+
          | year_floor('2020-12-25 00:00:00') |
          +-----------------------------------+
          | 2020-01-01 00:00:00               |
          +-----------------------------------+
          1 row in set (0.04 sec)

          Keywords

          year_floor

          YEARS_ADD

          Description

          years_add(timestamp date, int years)
          
          years_add(timestamp date, bigint years)
          • 功能:返回指定的日期加上若干年后的时间
          • 返回类型:timestamp

          Example

          mysql> select years_add('2020-12-25',10);
          +--------------------------------------+
          | years_add('2020-12-25 00:00:00', 10) |
          +--------------------------------------+
          | 2030-12-25 00:00:00                  |
          +--------------------------------------+
          1 row in set (0.03 sec)

          Keywords

          years_add

          YEAR_DIFF

          Description

          years_diff(timestamp enddate, timestamp startdate)
          • 功能:开始时间到结束时间相差几年
          • 返回类型:int类型

          Example

          mysql> select years_diff('2020-12-25','2011-04-30');
          +----------------------------------------------------------+
          | years_diff('2020-12-25 00:00:00', '2011-04-30 00:00:00') |
          +----------------------------------------------------------+
          |                                                        9 |
          +----------------------------------------------------------+
          1 row in set (0.02 sec)

          Keywords

          years_diff

          YEAR_SUB

          Description

          years_sub(timestamp date, int years)
          
          years_sub(timestamp date, bigint years)
          • 功能:返回指定的日期减去若干年的时间
          • 返回类型:timestamp

          Example

          mysql> select years_sub('2020-12-25',10);
          +--------------------------------------+
          | years_sub('2020-12-25 00:00:00', 10) |
          +--------------------------------------+
          | 2010-12-25 00:00:00                  |
          +--------------------------------------+
          1 row in set (0.03 sec)

          Keywords

          years_sub
          上一篇
          语法帮助
          下一篇
          通用函数