百度数据仓库 Palo SQL语法手册 - 时间和日期函数

百度数据仓库 Palo Doris版

简介/价格/文档

百度数据仓库 Palo SQL语法手册 - 时间和日期函数

文档简介：

PALO 支持的时间类型包括 DATE 和 DATETIME 两种类型。 DATE 类型格式为："2020-10-10" DATETIME 类型格式为："2020-10-10 11:10:06" PALO 支持的日期和时间函数如下：

*此产品及展示信息均由百度智能云官方提供。免费试用咨询热线：400-826-7010，为您提供专业的售前咨询，让您快速了解云产品，助您轻松上云！微信咨询

免费试用、价格特惠

文档详情

PALO 支持的时间类型包括 DATE 和 DATETIME 两种类型。

DATE 类型格式为："2020-10-10"
DATETIME 类型格式为："2020-10-10 11:10:06"

PALO 支持的日期和时间函数如下：

1.add_months
2.adddate
3.convert_tz
4.curdate,current_date
5.current_timestamp
6.curtime,current_time
7.date_add
8.date_sub
9.date_format
10.datediff
11.day,dayofmonth
12.dayname
13.dayofweek
14.dayofyear
15.days_add
16.days_diff
17.days_sub
18.extract
19.from_days
20.from_unixtime 21.unix_timestamp
22.utc_timestamp
23.hour
24.hours_add
25.hours_diff
26.hours_sub
27.localtime,localtimestamp
28.microseconds_add
29.microseconds_sub
30.minute
31.minutes_add
32.minutes_diff
33.minutes_sub
34.month
35.monthname
36.months_add
37.months_diff
38.months_sub
39.now
40.second
41.seconds_add
42.seconds_diff
43.seconds_sub
44.subdate
45.str_to_date 46.time_round
47.timediff
48.timestampadd 49.timestampdiff 50.to_date
51.to_days
52.weeks_add
53.weekofyear
54.weeks_diff
55.weeks_sub
56.quarter
57.year
58.year_floor
59.years_add
60.years_diff
61.years_sub
62.makedate
63.week
64.yearweek

ADD_MONTHS

Description

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

TIME_ROUND

Description

DATETIME TIME_ROUND(DATETIME expr) DATETIME TIME_ROUND(DATETIME expr, INT period) DATETIME TIME_ROUND(DATETIME

expr, DATETIME origin) DATETIME TIME_ROUND(DATETIME expr, INT period, DATETIME origin)

功能：函数名 TIME_ROUND 由两部分组成，每部分由以下可选值组成 TIME: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR ROUND: FLOOR, CEIL 返回 expr 的上/下界。 period 指定每个周期有多少个 TIME 单位组成，默认为 1。 origin 指定周期的开始时间，默认为 1970-01-01T00:00:00，WEEK 的默认开始时间为 1970-01-04T00:00:00，即周日。可以比 expr 大。请尽量选择常见 period，如 3 MONTH，90 MINUTE 等，如设置了非常用 period，请同时指定 origin。
返回类型：string类型

Example

MySQL> SELECT YEAR_FLOOR('20200202000000'); +------------------------------+

| year_floor('20200202000000') | +------------------------------+ | 2020-01-01 00:00:00

| +------------------------------+ MySQL> SELECT MONTH_CEIL(CAST('2020-02-02 13:09:20'

 AS DATETIME), 3); --quarter +-----------------------------------------------------

---+ | month_ceil(CAST('2020-02-02 13:09:20' AS DATETIME), 3) | +---------------------

-----------------------------------+ | 2020-04-01 00:00:00 | +---------------------

-----------------------------------+ MySQL> SELECT WEEK_CEIL('2020-02-02 13:09:20', '

2020-01-06'); --monday +---------------------------------------------------------+

| week_ceil('2020-02-02 13:09:20', '2020-01-06 00:00:00') | +-------------------------

--------------------------------+ | 2020-02-03 00:00:00 | +--------------------------

-------------------------------+ MySQL> SELECT MONTH_CEIL(CAST('2020-02-02 13:09:20'

 AS DATETIME), 3, CAST('1970-01-09 00:00:00' AS DATETIME)); --next rent day +--------

-----------------------------------------------------------------------------------

------+ | month_ceil(CAST('2020-02-02 13:09:20' AS DATETIME), 3, CAST('1970-01-09 00:00:00'

AS DATETIME)) | +-------------------------------------------------------------------

------------------------------+ | 2020-04-09 00:00:00 | +------------------------------

------------------------------------------------------------------+

Keywords

TIME_ROUND

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

TIMESTAMPADD

Description

TIMESTAMPADD(unit, interval, DATETIME datetime_expr)

功能：将整数表达式间隔添加到日期或日期时间表达式datetime_expr中。 unit可选值：SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR
返回类型：string类型

Example

mysql> select timestampadd(DAY, 1, '2021-07-02 12:31:44'); +-----------------------------------

----------+ | timestampadd(DAY, 1, '2021-07-02 12:31:44') | +-------------------------------

--------------+ | 2021-07-03 12:31:44 | +---------------------------------------------+

Keywords

timestampadd

TIMESTAMPDIFF

Description

TIMESTAMPDIFF(unit,DATETIME datetime_expr1, DATETIME datetime_expr2)

功能：返回datetime_expr2−datetime_expr1，其中datetime_expr1和datetime_expr2是日期或日期时间表达式。结果(整数)的单位由unit参数给出。 unit可选值:SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR
返回类型：int类型

Example

mysql> select timestampdiff(SECOND, '2021-07-01 12:31:44', '2021-07-02 12:31:44'); +--------------

-------------------------------------------------------+ | timestampdiff(SECOND, '2021-07-01 12:31:44', '

2021-07-02 12:31:44') | +---------------------------------------------------------------------+

| 86400 | +---------------------------------------------------------------------+

Keywords

timestampdiff

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

MAKEDATE

Description

DATE MAKEDATE(INT year, INT dayofyear)

功能：返回指定年份和 dayofyear 构建的日期。dayofyear 必须大于0，否则结果为空。
返回类型：date

Example

mysql> select makedate(2021,1), makedate(2021,100), makedate(2021,400); +--------------

-----+---------------------+---------------------+ | makedate(2021, 1) | makedate(2021, 100)

 | makedate(2021, 400) | +-------------------+---------------------+---------------------+

| 2021-01-01 | 2021-04-10 | 2022-02-04 | +-------------------+---------------------+---------------------+

Keywords

makedate

WEEK

Description

INT YEARWEEK(DATE date) INT YEARWEEK(DATE date, INT mode)

*功能：返回指定日期的星期数。mode的值默认为0。

参数mode的作用参见下面的表格：

Mode	星期的第一天	星期数的范围	第一个星期的定义
0	星期日	0-53	这一年中的第一个星期日所在的星期
1	星期一	0-53	这一年的日期所占的天数大于等于4天的第一个星期
2	星期日	1-53	这一年中的第一个星期日所在的星期
3	星期一	1-53	这一年的日期所占的天数大于等于4天的第一个星期
4	星期日	0-53	这一年的日期所占的天数大于等于4天的第一个星期
5	星期一	0-53	这一年中的第一个星期一所在的星期
6	星期日	1-53	这一年的日期所占的天数大于等于4天的第一个星期
7	星期一	1-53	这一年中的第一个星期一所在的星期

参数为Date或者Datetime类型

Example

mysql> select yearweek('2021-1-1'); +----------------------+ | yearweek('2021-1-1') | +----------

------------+ | 202052 | +----------------------+ mysql> select yearweek('2020-7-1'); +---------

-------------+ | yearweek('2020-7-1') | +----------------------+ | 202026 | +-----------------

-----+ mysql> select yearweek('2024-12-30',1); +------------------------------------+ | yearweek

('2024-12-30 00:00:00', 1) | +------------------------------------+ | 202501 | +------

------------------------------+

keywords

week

YEARWEEK

Description

INT YEARWEEK(DATE date) INT YEARWEEK(DATE date, INT mode)

功能：返回指定日期的年份和星期数。mode的值默认为0。

当日期所在的星期属于上一年时，返回的是上一年的年份和星期数；

当日期所在的星期属于下一年时，返回的是下一年的年份，星期数为1。

参数mode的作用参见下面的表格：

Mode	星期的第一天	星期数的范围	第一个星期的定义
0	星期日	1-53	这一年中的第一个星期日所在的星期
1	星期一	1-53	这一年的日期所占的天数大于等于4天的第一个星期
2	星期日	1-53	这一年中的第一个星期日所在的星期
3	星期一	1-53	这一年的日期所占的天数大于等于4天的第一个星期
4	星期日	1-53	这一年的日期所占的天数大于等于4天的第一个星期
5	星期一	1-53	这一年中的第一个星期一所在的星期
6	星期日	1-53	这一年的日期所占的天数大于等于4天的第一个星期
7	星期一	1-53	这一年中的第一个星期一所在的星期

参数为Date或者Datetime类型

Example

mysql> select yearweek('2021-1-1'); +----------------------+ | yearweek('2021-1-1')

| +----------------------+ | 202052 | +----------------------+ mysql> select yearweek

('2020-7-1'); +----------------------+ | yearweek('2020-7-1') | +--------------------

--+ | 202026 | +----------------------+ mysql> select yearweek('2024-12-30',1); +----

--------------------------------+ | yearweek('2024-12-30 00:00:00', 1) | +-----------

-------------------------+ | 202501 | +------------------------------------+

Keywords

yearweek

相似文档

百度数据仓库 Palo SQL语法手册 - 格式转换函数
PALO 的格式转换函数如下： 1.aes_encrypt 2.aes_decrypt 3.to_base64 4.from_base64 5.md5,md5sum AES_ENCRYPT Description： aes_encrypt(string str, string key) 功能：使用key作为密钥，给str加密，返回加密后的结果。
百度数据仓库 Palo SQL语法手册 - 窗口函数
窗口函数是一类特殊的内置函数。和聚合函数类似，窗口函数也是对于多个输入行做计算得到一个数据值。不同的是，窗口函数是在一个特定的窗口内对输入数据做处理，而不是按照 group by 来分组计算。
百度数据仓库 Palo SQL语法手册 - 类型转换函数
PALO 支持以下类型转换函数： CAST Description： cast(expr as type) 功能：转换函数通常会和其他函数一同使用，显示的将expression转换成指定的参数类型。PALO 对于函数的参数类型有严格的数据类型定义。例如 PALO 不会自动将 bigtint 转换成 int 类型，或者其余可能会损失精度或者产生溢出的转换。
百度数据仓库 Palo SQL语法手册 - 聚合函数
聚合函数的行为是将多行的结果聚合成一行。 PALO 支持以下聚合函数： 1.avg 2.count 3.approx_count_distinct 4.max 5.min 6.sum 7.group_concat 8.variance_samp 9.variance_pop 10.percentile_approx 11.topn
百度数据仓库 Palo SQL语法手册 - 通用函数
PALO 通用函数如下： 1.sleep 2.version SLEEP Description： sleep(int n) 功能：会话休眠n秒钟，返回成功与否返回值：bool类型

文档中心

全民上云·上云补贴申领

免费试用（限企业）

ADD_MONTHS

Description

Example

Keywords

ADDDATE

Description

Example

Keywords

CONVERT_TZ

Description

Example

Keywords

CURDATE,CURRENT_DATE

Description

Example

Keywords

CURRENT_TIMESTAMP

Description

Example

Keywords

CURTIME,CURRENT_TIME

Description

Example

Keywords

DATE_ADD

Description

Example

Keywords

DATE_SUB

Description

Example

Keywords

DATE_FORMAT

Description

Example

Keywords

DATEDIFF

Description

Example

Keywords

DAY,DAYOFMONTH

Description

Example

Keywords

DAYNAME

Description

Example

Keywords

DAYOFWEEK

Description

Example

Keywords

DAYOFYEAR

Description

Example

Keywords

DAYS_ADD

Description

Example

Keywords

DAYS_DIFF

Description

Example

Keywords

DAYS_SUB

Description

Example

Keywords

EXTRACT

Description

Example

Keywords

FROM_DAYS

Description

Example

Keywords

FROM_UNIXTIME