日期和时间函数是什么?

时区转换


运算符：AT TIME ZONE，用于设置一个时间戳的时区:

1. SELECT timestamp ‘2012-10-31 01:00 UTC’; 2012-10-31 01:00:00.000 UTC
3. SELECT timestamp ‘2012-10-31 01:00 UTC’ AT TIME ZONE ‘America/Los_Angeles’; 2012-10-30 18:00:00.000 America/Los_Angeles

日期时间函数


current_date -> date
返回查询开始时的当前日期.
current_time -> time with time zone
返回查询开始时的当前时间.
current_timestamp -> timestamp with time zone
返回查询开始时的当前时间戳.
current_timezone() → varchar
以IANA（例如，America / Los_Angeles）定义的格式返回当前时区，或以UTC的固定偏移量（例如+08：35）返回当前时区
from_iso8601_timestamp(string) → timestamp with timezone
将ISO 8601格式化的字符串解析为具有时区的时间戳
from_iso8601_date(string) → date
将ISO 8601格式的字符串解析为日期
from_unixtime(unixtime) → timestamp
返回unixtime时间戳
from_unixtime(unixtime, string) → timestamp with timezone
返回指定时区的unixtime时间戳
from_unixtime(unixtime, hours, minutes) → timestamp withtime zone
返回为hours和minutes对应时区的unixtime时间戳
localtime -> time
返回查询开始时的当前时间
localtimestamp -> timestamp
返回查询开始时的当前时间戳
now() → timestamp with time zone
这是current_timestam的另一种表达
to_iso8601(x) → varchar
将x格式化为ISO 8601字符串。 x可以是date, timestamp,或带时区的timestamp
to_unixtime(timestamp) → double
转换为unix时间戳

Note


下列SQL标准的函数不使用括号：

• current_date
• current_time
• current_timestamp
• localtime
• localtimestamp

截取函数


函数date_trunc支持如下单位：

单位	示例结果
second	2001-08-22 03:04:05.000
minute	2001-08-22 03:04:00.000
hour	2001-08-22 03:00:00.000
day	2001-08-22 00:00:00.000
week	2001-08-20 00:00:00.000
month	2001-08-01 00:00:00.000
quarter	2001-07-01 00:00:00.000
year	2001-01-01 00:00:00.000

上面的例子使用时间戳： 2001-08-22 03:04:05.321 作为输入。
date_trunc(unit, x) → [same as input]返回x截取到单位unit之后的值

间隔函数


本章中的函数支持如下所列的间隔单位：

单位	描述
millisecond	Milliseconds
second	Seconds
minute	Minutes
hour	Hours
day	Days
week	Weeks
month	Months
quarter	Quarters of a year
year	Years

date_add(unit, value, timestamp) → [same as input]
在timestamp的基础上加上value个unit。如果想要执行相减的操作，可以通过将value赋值为负数来完成
date_diff(unit, timestamp1, timestamp2) → bigint
返回 timestamp2 - timestamp1之后的值，该值的表示单位是unit

MySQL日期函数


在这一章节使用与MySQLdate_parse和str_to_date方法兼容的格式化字符串。下面的表格是基于MySQL手册列出的，描述了各种格式化描述符：

分类符	说明
%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	Fraction of second (6 digits for printing: 000000.. 999000; 1 - 9 digits for parsing: 0 .. 999999999)
%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 orPM)
%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 ofthe week
%u	Week (00 .. 53), where Monday is the first day ofthe week
%V	Week (01 .. 53), where Sunday is the first day ofthe week; used with %X
%v	Week (01 .. 53), where Monday is the first day ofthe week; used with %x
%W	Weekday name (Sunday .. Saturday)
%w	Day of the week (0 .. 6), where Sunday is the firstday of the week
%X	Year for the week where Sunday is the first day ofthe week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day ofthe 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 listedabove

• 
  [1] Timestamp被截断为毫秒。
• 
  [2] 解析时，两位数的年份格式假定为1970.2069，因此1970年将会产生“70”，而“69”将产生2069年。
• 
  [3] 下列说明符尚不支持: %D %U %u %V %w %X

date_format(timestamp, format) →varchar使用format指定的格式，将timestamp格式化成字符串。
date_parse(string, format) →timestamp按照format指定的格式，将字符串string解析成timestamp。

Java日期函数


在这一章节中使用的格式化字符串都是与Java的SimpleDateFormat样式兼容的。
format_datetime(timestamp, format) → varchar
使用format指定的格式，将timestamp格式化成字符串。
parse_datetime(string, format) → timestamp with timezone
按照format指定的格式，将字符串string解析成带时间戳的timestamp。

抽取函数


可以使用抽取函数来抽取如下域：

域	描述
YEAR	year()
QUARTER	quarter()
MONTH	month()
WEEK	week()
DAY	day()
DAY_OF_MONTH	day()
DAY_OF_WEEK	day_of_week()
DOW	day_of_week()
DAY_OF_YEAR	day_of_year()
DOY	day_of_year()
YEAR_OF_WEEK	year_of_week()
YOW	year_of_week()
HOUR	hour()
MINUTE	minute()
SECOND	second()
TIMEZONE_HOUR	timezone_hour()
TIMEZONE_MINUTE	timezone_minute()

抽取函数支持的数据类型取决于需要抽取的域。大多数域都支持日期和时间类型。
extract(field FROM x) → bigint
从x中返回域

Note

• SQL标准的函数一般都会使用特定的语法来指定参数。

便利的抽取函数


day(x) → bigint
返回指定日期在当月的天数
day_of_month(x) → bigint
day(x)的另一种表述
day_of_week(x) → bigint
返回指定日期对应的星期值，值范围从1 (星期一) 到 7 (星期天).
day_of_year(x) → bigint
返回指定日期对应一年中的第几天，值范围从1到 366.
dow(x) → bigint
day_of_week()的另一种表达
doy(x) → bigint
day_of_year()的另一种表达
hour(x) → bigint
返回指定日期对应的小时，值范围从1到 23
minute(x) → bigint
返回指定日期对应的分钟
month(x) → bigint
返回指定日期对应的月份
quarter(x) → bigint
返回指定日期对应的季度，值范围从1到 4
second(x) → bigint
返回指定日期对应的秒
timezone_hour(timestamp) → bigint
返回从指定时间戳对应时区偏移的小时数
timezone_minute(timestamp) → bigint
返回从指定时间戳对应时区偏移的分钟数
week(x) → bigint
返回指定日期对应一年中的ISO week，值范围从1到 53
week_of_year(x) → bigint
week的另一种表述
year(x) → bigint
返回指定日期对应的年份
year_of_week(x) → bigint
返回指定日期对应的ISO week的年份
yow(x) → bigint
year_of_week()的另一种表达
这一部分使用了和Teradata SQL 的datetime函数兼容的字符串格式. 下表基于Teradata的使用手册, , 介绍了支持的格式:

格式	描述
- / , . ; :	标点符号被忽略
dd	天 (1-31)
hh	12小时制 (1-12)
hh24	24小时制 (0-23)
mi	分钟 (0-59)
mm	月 (01-12)
ss	秒 (0-59)
yyyy	4位年
yy	2位年

Warning

• 目前不支持大小写区分，所有说明符必须为小写

to_char(timestamp, format) → varchar
timestamp 转化为 format 格式的时期字符串.
to_timestamp(string, format) → timestamp
将 string 用 format 解析为 TIMESTAMP .
to_date(string, format) → date
将 string 用 format 解析为 DATE .

DateTime 函数MySQL兼容性(ADS已经支持的MySQL函数)



ADDDATE


Add time values (intervals) to a date value. When invoked with the INTERVALform of the second argument, ADDDATE() is a synonym for DATE_ADD().
支持的语法：
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)
例子：

1. SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
2. SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
3. SELECT ADDDATE('2008-01-02', 31);

ADDTIME


ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time ordatetime expression, and expr2 is a time expression.
支持的语法：
ADDTIME(expr1,expr2)
例子：

1. SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
2. SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

CURDATE


Returns the current date as a value in ‘YYYY-MM-DD’. CURRENT_DATE andCURRENT_DATE() are synonyms for CURDATE().
支持的语法：
CURDATE()
例子：

1. SELECT CURDATE();
2. SELECT CURRENT_DATE();
3. SELECT CURRENT_DATE;

SYSDATE


Returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’format.
支持的语法：
SYSDATE()
例子：

1. SELECT SYSDATE();

CURRENT_DATE


Synonyms for CURDATE()
支持的语法：
CURRENT_DATE ， CURRENT_DATE()
例子：

1. SELECT CURRENT_DATE;
2. SELECT CURRENT_DATE();

CURRENT_TIME


Synonyms for CURTIME()
支持的语法：
CURRENT_TIME ， CURRENT_TIME()
例子：

1. SELECT CURRENT_TIME;
2. SELECT CURRENT_TIME();

CURTIME


Returns the current date as a value in ‘HH.MM.SS’. CURRENT_TIME andCURRENT_TIME() are synonyms for CURTIME().
支持的语法：
CURTIME()
例子：

1. SELECT CURTIME();
2. SELECT CURRENT_TIME();
3. SELECT CURRENT_TIME;

Yearmonth


查询指定列的日和月，例如YEARMONTH(‘20140602’)=201406;

DATE


Extracts the date part of the date or datetime expression expr.
支持的语法：
DATE(expr)
例子：

1. SELECT DATE('2003-12-31 01:02:03')

DATEDIFF


DATEDIFF() returns expr1 − expr2 expressed as a value in days from one dateto the other. expr1 and expr2 are date or date-and-time expressions. Only thedate parts of the values are used in the calculation.
支持的语法：
DATEDIFF(expr1,expr2)
例子：

1. SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
2. SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');

DATE_FORMAT


Formats the date value according to the format string.
支持的语法：
DATE_FORMAT(expr1,expr2)
例子：

1. SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
2. SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
3. SELECT DATE_FORMAT('1997-10-04 22:23:00', '%d');
4. SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d');
5. SELECT DATE_FORMAT('2009-10-04 22:23:00', '%y-%m-%d');
6. SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d %T');
7. SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d %r');

DAY


DAY() is a synonym for DAYOFMONTH().
支持的语法：
DAY(date)
例子：

1. SELECT DAY('2007-02-03');
2. SELECT DAYOFMONTH('2007-02-03');

DAYNAME


Returns the name of the weekday for date.
支持的语法：
DAYNAME(date)
例子：

1. SELECT DAYNAME('2007-02-03');

DAYOFWEEK


Returns the weekday index for date.
支持的语法：
DAYOFWEEK(date)
例子：

1. SELECT DAYOFWEEK('2007-02-03');

DAYOFYEAR


Returns the day of the year for date, in the range 1 to 366.
支持的语法：
DAYOFYEAR(date)
例子：

1. SELECT DAYOFYEAR('2007-02-03');

EXTRACT


The EXTRACT() function uses the same kinds of unit specifiers as DATE_ADD()or DATE_SUB(), but extracts parts from the date rather than performing datearithmetic.
支持的语法：
EXTRACT(unit FROM date)
例子：

1. SELECT EXTRACT(YEAR FROM '2009-07-02');
2. SELECT EXTRACT(MONTH FROM '2009-07-02');
3. SELECT EXTRACT(DAY FROM '2009-07-02');
4. SELECT EXTRACT(HOUR FROM '2003-01-02 10:30:00');
5. SELECT EXTRACT(MINUTE FROM '2003-01-02 10:30:00');
6. SELECT EXTRACT(SECOND FROM '2003-01-02 10:30:00');

FROM_DAYS


Given a day number N, returns a DATE value.
支持的语法：
FROM_DAYS(N)
例子：

1. SELECT FROM_DAYS(730669);

FROM_UNIXTIME


Returns a representation of the unix_timestamp argument as a value in‘YYYY-MM-DD HH:MM:SS’.
支持的语法：
FROM_UNIXTIME(unix_timestamp) ,FROM_UNIXTIME(unix_timestamp,format)
例子：

1. SELECT FROM_UNIXTIME(1447430881);
2. SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd');

HOUR


Returns the hour for time. The range of the return value is 0 to 23 fortime-of-day values.
支持的语法：
HOUR(time)
例子：

1. SELECT HOUR('10:05:03');
2. SELECT HOUR('272:59:59');

LAST_DAY


Takes a date or datetime value and returns the corresponding value for thelast day of the month.
支持的语法：
LAST_DAY(date)
例子：

1. SELECT LAST_DAY('2003-02-05');
2. SELECT LAST_DAY('2004-01-01 01:01:01');

LOCALTIME


LOCALTIME and LOCALTIME() are synonyms for NOW()
支持的语法：
LOCALTIME , LOCALTIME()
例子：

1. SELECT LOCALTIME;
2. SELECT LOCALTIME();

NOW


NOW() is synonyms for LOCALTIME and LOCALTIME()
支持的语法：
NOW()
例子：

1. SELECT NOW();

LOCALTIME


LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for NOW()
支持的语法：
LOCALTIME , LOCALTIME()
例子：

1. SELECT LOCALTIMESTAMP;
2. SELECT LOCALTIMESTAMP();

MAKETIME


Returns a time value calculated from the hour, minute, and secondarguments.
支持的语法：
MAKETIME(hour,minute,second)
例子：

1. SELECT MAKETIME(12,15,30);

MINUTE


Returns the minute for time, in the range 0 to 59.
支持的语法：
MINUTE(time)
例子：

1. SELECT MINUTE('2008-02-03 10:05:03');

MONTH


Returns the month for date, in the range 1 to 12 for January to December.
支持的语法：
MONTH(date)
例子：

1. SELECT MONTH('2008-02-03');

MONTHNAME


Returns the full name of the month for date.
支持的语法：
MONTHNAME(date)
例子：

1. SELECT MONTHNAME('2008-02-03');

NOW


Returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’.
支持的语法：
NOW()
例子：

1. SELECT NOW();

PERIOD_ADD


Adds N months to period P (in the format YYMM or YYYYMM). Returns a value inthe format YYYYMM.
支持的语法：
PERIOD_ADD(P,N)
例子：

1. SELECT PERIOD_ADD(200801,2);

PERIOD_DIFF


Returns the number of months between periods P1 and P2.
支持的语法：
PERIOD_DIFF(P1,P2)
例子：

1. SELECT PERIOD_DIFF(200802,200703);

QUARTER


Returns the quarter of the year for date, in the range 1 to 4.
支持的语法：
QUARTER(date)
例子：

1. SELECT QUARTER('2008-04-01');

SECOND


Returns the second for time, in the range 0 to 59.
支持的语法：
SECOND(time)
例子：

1. SELECT SECOND('10:05:03');

SEC_TO_TIME


Returns the seconds argument, converted to hours, minutes, and seconds, as aTIME value.
支持的语法：
SEC_TO_TIME(seconds)
例子：

1. SELECT SEC_TO_TIME(2378);

STR_TO_DATE


This is the inverse of the DATE_FORMAT() function. It takes a string str anda format string format.
支持的语法：
STR_TO_DATE(str,format)
例子：

1. SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');

SUBDATE


When invoked with the INTERVAL form of the second argument, SUBDATE() is asynonym for DATE_SUB().
支持的语法：
SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)
例子：

1. SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY);
2. SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
3. SELECT SUBDATE('2008-01-02 12:00:00', 31);

SUBTIME


SUBTIME() returns expr1 − expr2 expressed as a value in the same format asexpr1
支持的语法：
SUBTIME(expr1,expr2)
例子：

1. SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
2. SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');

STR_TO_DATE




This is the inverse of the DATE_FORMAT() function. It takes a string str anda format string format.
支持的语法：
STR_TO_DATE(str,format)
例子：

1. SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');

TIME


Extracts the time part of the time or datetime expression expr and returns itas a string.
支持的语法：
TIME(expr)
例子：

1. SELECT TIME('2003-12-31 01:02:03');

TIMESTAMP


With a single argument, this function returns the date or datetime expressionexpr as a datetime value. With two arguments, it adds the time expression expr2to the date or datetime expression expr1 and returns the result as a datetimevalue.
支持的语法：
TIMESTAMP(expr) , TIMESTAMP(expr1,expr2)
例子：

1. SELECT TIMESTAMP('2003-12-31');
2. SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');

TIMESTAMPADD


Adds the integer expression interval to the date or datetime expressiondatetime_expr.
支持的语法：
TIMESTAMPADD(unit,interval,datetime_expr)
例子：

1. SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
2. SELECT TIMESTAMPADD(YEAR,1,'2003-01-02');
3. SELECT TIMESTAMPADD(MONTH,1,'2003-01-02');
4. SELECT TIMESTAMPADD(DAY,1,'2003-01-02');

TIMESTAMPDIFF


Returns datetime_expr2 − datetime_expr1, where datetime_expr1 anddatetime_expr2 are date or datetime expressions.
支持的语法：
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
例子：

1. SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
2. SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
3. SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');

TIME_TO_SEC


Returns the time argument, converted to seconds.
支持的语法：
TIME_TO_SEC(time)
例子：

1. SELECT TIME_TO_SEC('22:23:00');
2. SELECT TIME_TO_SEC('00:39:38');

TO_DAYS


Given a date date, returns a day number (the number of days since year0).
支持的语法：
TO_DAYS(date)
例子：

1. SELECT TO_DAYS('2007-10-07');
2. SELECT TO_DAYS('2008-10-07');
3. SELECT TO_DAYS('0000-00-00');

TO_SECONDS


Given a date or datetime expr, returns the number of seconds since the year0. If expr is not a valid date or datetime value, returns NULL.
支持的语法：
TO_SECONDS(date)
例子：

1. SELECT TO_SECONDS('2009-11-29');
2. SELECT TO_SECONDS('2009-11-29 13:43:32');
3. SELECT TO_SECONDS('0000-00-00');

TO_SECONDS


Given a date or datetime expr, returns the number of seconds since the year0. If expr is not a valid date or datetime value, returns NULL.
支持的语法：
TO_SECONDS(date)
例子：

1. SELECT TO_SECONDS('2009-11-29');
2. SELECT TO_SECONDS('2009-11-29 13:43:32');
3. SELECT TO_SECONDS('0000-00-00');

UNIX_TIMESTAMP


If called with no argument, returns a Unix timestamp (seconds since‘1970-01-01 00:00:00’ UTC).
支持的语法：
UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date)
例子：

1. SELECT UNIX_TIMESTAMP();
2. SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
3. SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');
4. SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');

UTC_DATE


Returns the current UTC date as a value in ‘YYYY-MM-DD’.
支持的语法：
UTC_DATE()
例子：

1. SELECT UTC_DATE()

UTC_TIME


Returns the current UTC time as a value in ‘HH:MM:SS’.
支持的语法：
UTC_TIME()
例子：

1. SELECT UTC_TIME()

UTC_TIMESTAMP


Returns the current UTC date and time as a value in ‘YYYY-MM-DDHH:MM:SS’.
支持的语法：
UTC_TIMESTAMP()
例子：

1. SELECT UTC_TIMESTAMP()

WEEK


This function returns the week number for date. The two-argument form ofWEEK() enables you to specify whether the week starts on Sunday or Monday andwhether the return value should be in the range from 0 to 53 or from 1 to53.
支持的语法：
WEEK(date[,mode])
例子：

1. SELECT WEEK('2008-02-20');
2. SELECT WEEK('2008-02-20',0);
3. SELECT WEEK('2008-02-20',1);

WEEKDAY


Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 =Sunday).
支持的语法：
WEEKDAY(date)
例子：

1. SELECT WEEKDAY('2008-02-03 22:23:00');
2. SELECT WEEKDAY('2007-11-06');

WEEKOFYEAR


Returns the calendar week of the date as a number in the range from 1 to53.
支持的语法：
WEEKOFYEAR(date)
例子：

1. SELECT WEEKOFYEAR('2008-02-20');

YEAR


Returns the year for date, in the range 1000 to 9999, or 0 for the “zero”date.
支持的语法：
YEAR(date)
例子：

1. SELECT YEAR('1987-01-01');

YEARWEEK


Returns year and week for a date. The year in the result may be differentfrom the year in the date argument for the first and the last week of theyear.
支持的语法：
YEARWEEK(date) , YEARWEEK(date,mode)
例子：

1. SELECT YEARWEEK('1987-01-01');
2. SELECT YEARWEEK('1987-01-01',1);

TIMEDIFF


TIMEDIFF() returns expr1 − expr2 expressed as a time value. expr1 and expr2are date-and-time expressions, but both must be of the same type.
支持的语法：
TIMEDIFF(expr1,expr2)
例子：

1. SELECT TIMEDIFF('2008-12-31 23:59:50', '2008-12-31 23:59:59');
2. SELECT TIMEDIFF('2008-12-30 23:59:59', '2008-12-31 23:59:59');
3. SELECT TIMEDIFF('2008-12-30 11:59:59', '2008-12-31 23:59:59');
4. SELECT TIMEDIFF('2008-12-30 11:50:59', '2008-12-31 23:59:59');
5. SELECT TIMEDIFF('2008-12-30 11:50:50', '2008-12-31 23:59:59');

CONVERT_TZ


CONVERT_TZ() converts a datetime value dt from the time zone given by from_tzto the time zone given by to_tz and returns the resulting value.
支持的语法：
CONVERT_TZ(dt,from_tz,to_tz)
例子：

1. SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

TIME_FORMAT


This is used like the DATE_FORMAT() function, but the format string maycontain format specifiers only for hours, minutes, seconds, and microseconds.Other specifiers produce a NULL value or 0.
支持的语法：
TIME_FORMAT(time,format)
例子：

1. SELECT TIME_FORMAT('10:00:00', '%H %k %h %I %l');

Datetime函数Oracle兼容性(ADS已经支持的Oracle函数)



ADD_MONTHS


ADD_MONTHS returns the date date plus integer months.
支持的语法：
ADD_MONTHS(date, integer)
例子：

1. ADD_MONTHS('2010-10-10',1)

CURRENT_DATE


CURRENT_DATE returns the current date in the session time zone, in a value inthe Gregorian calendar of datatype DATE.
支持的语法：
CURRENT_DATE
例子：

1. SELECT CURRENT_DATE;

CURRENT_TIMESTAMP


CURRENT_TIMESTAMP returns the current date and time in the session time zone,in a value of datatype TIMESTAMP WITH TIME ZONE.
支持的语法：
CURRENT_TIMESTAMP
例子：

1. SELECT CURRENT_TIMESTAMP;

DBTIMEZONE


DBTIMEZONE returns the value of the database time zone.
支持的语法：
DBTIMEZONE()
例子：

1. SELECT DBTIMEZONE();

EXTRACT


EXTRACT extracts and returns the value of a specified datetime field from adatetime or interval value expression.
支持的语法：
EXTRACT(unit FROM datetime)
例子：

1. SELECT EXTRACT(YEAR FROM '2001-01-01');
2. SELECT EXTRACT(MONTH FROM '2001-01-01');
3. SELECT EXTRACT(DAY FROM '2001-01-01');
4. SELECT EXTRACT(HOUR FROM '2001-01-01 19:10:11');
5. SELECT EXTRACT(MINUTE FROM '2001-01-01 19:10:11');
6. SELECT EXTRACT(SECOND FROM '2001-01-01 19:10:11');

LAST_DAY


LAST_DAY returns the date of the last day of the month that containsdate.
支持的语法：
LAST_DAY
例子：

1. LAST_DAY('2001-01-01');

LOCALTIMESTAMP


LOCALTIMESTAMP returns the current date and time in the session time zone ina value of datatype TIMESTAMP.
支持的语法：
LOCALTIMESTAMP()
例子：

1. SELECT LOCALTIMESTAMP();

MONTH_BETWEEN


MONTHS_BETWEEN returns number of months between dates date1 and date2.
支持的语法：
MONTH_BETWEEN(date1, date2)
例子：

1. SELECT MONTH_BETWEEN('2017-03-03', '2017-07-07');
2. SELECT MONTH_BETWEEN('2017-04-03', '2017-07-07');

NEXT_DAY


NEXT_DAY returns the date of the first weekday named by char that is laterthan the date date.
支持的语法：
NEXT_DAY(date, char)
例子：

1. SELECT NEXT_DAY('2010-10-10','TUESDAY');
2. SELECT NEXT_DAY('2010-10-10','TUE');

ROUND


ROUND returns date rounded to the unit specified by the format model fmt. Thevalue returned is always of datatype DATE, even if you specify a differentdatetime datatype for date.
支持的语法：
ROUND(date, fmt)
例子：

1. SELECT ROUND(TIMESTAMP '2010-08-21', 'YY');
2. SELECT ROUND(TIMESTAMP '2010-08-21', 'MM');
3. SELECT ROUND(TIMESTAMP '2010-08-21', 'q');
4. SELECT ROUND(TIMESTAMP '2010-08-21', 'D');
5. SELECT ROUND(TIMESTAMP '2010-08-21 19:00:00', 'DD');

SESSIONTIMEZONE


SESSIONTIMEZONE returns the time zone of the current session.
支持的语法：
SESSIONTIMEZONE()
例子：

1. SELECT SESSIONTIMEZONE();

SYSDATE


SYSDATE returns the current date and time set for the operating system onwhich the database resides.
支持的语法：
SYSDATE()
例子：

1. SELECT SYSDATE();

TO_CHAR


TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP,TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a valueof VARCHAR2 datatype in the format specified by the date format fmt.
支持的语法：
TO_CHAR(datetime, fmt)
例子：

1. SELECT TO_CHAR('2013-05-17 23:35:10', '%Y-%m-%d %H:%i:%s');
2. SELECT TO_CHAR('2013-05-17 00:35:10', '%Y-%m-%d %H:%i:%s');
3. SELECT TO_CHAR('2013-05-17 12:35:10', '%Y-%m-%d %h:%i:%s %p');

TRUNC


The TRUNC (date) function returns date with the time portion of the daytruncated to the unit specified by the format model fmt.
支持的语法：
TRUNC(date, fmt)
例子：

1. SELECT TRUNC(TIMESTAMP '2010-08-21', 'YY');
2. SELECT TRUNC(TIMESTAMP '2010-08-21', 'MM');
3. SELECT TRUNC(TIMESTAMP '2010-08-21', 'q');
4. SELECT TRUNC(TIMESTAMP '2010-08-21 19:00:00', 'DD');

TO_DATE


TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to avalue of DATE datatype.
支持的语法：
TO_DATE(char, fmt)
例子：

1. SELECT TO_DATE('2013-05', '%Y-%m');
2. SELECT TO_DATE('2013-05-17 12:35:10', '%Y-%m-%d %h:%i:%s');

NEW_TIME


NEW_TIME returns the date and time in time zone timezone2 when date and timein time zone timezone1 are date.
支持的语法：
NEW_TIME(date, timezone1, timezone2)
例子：

1. SELECT NEW_TIME('2004-01-01 12:00:00','GMT','MET');
2. SELECT NEW_TIME('2004-01-01 12:00:00.123','GMT','MET');