全部产品
阿里云办公

日期和时间函数

更新时间:2017-10-11 17:41:26

时区转换

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

  1. SELECT timestamp 2012-10-31 01:00 UTC’; 2012-10-31 01:00:00.000 UTC
  2. 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 time zone

将ISO 8601格式化的字符串解析为具有时区的时间戳

from_iso8601_date(string) → date

将ISO 8601格式的字符串解析为日期

from_unixtime(unixtime) → timestamp

返回unixtime时间戳

from_unixtime(unixtime, string) → timestamp with time zone

返回指定时区的unixtime时间戳

from_unixtime(unixtime, hours, minutes) → timestamp with time 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_parsestr_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 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
%u Week (00 .. 53), where Monday is the first day of the week
%V Week (01 .. 53), where Sunday is the first day of the week; used with %X
%v Week (01 .. 53), where Monday is the first day of the week; used with %x
%W Weekday name (Sunday .. Saturday)
%w Day of the week (0 .. 6), where Sunday is the first day of the week
%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
  • [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 time zone

按照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 INTERVAL form 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 or datetime 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 and CURRENT_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 and CURRENT_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 date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date 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 date arithmetic.

支持的语法:

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 for time-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 the last 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 second arguments.

支持的语法:

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 in the 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 a TIME 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 and a 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 a synonym 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 as expr1

支持的语法:

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 and a 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 it as 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 expression expr as a datetime value. With two arguments, it adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value.

支持的语法:

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 expression datetime_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 and datetime_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 year 0).

支持的语法:

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 year 0. 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 year 0. 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-DD HH:MM:SS’.

支持的语法:

UTC_TIMESTAMP()

例子:

  1. SELECT UTC_TIMESTAMP()

WEEK

This function returns the week number for date. The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53.

支持的语法:

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 to 53.

支持的语法:

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 different from the year in the date argument for the first and the last week of the year.

支持的语法:

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 expr2 are 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_tz to 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 may contain 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 in the 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 a datetime 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 contains date.

支持的语法:

LAST_DAY

例子:

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

LOCALTIMESTAMP

LOCALTIMESTAMP returns the current date and time in the session time zone in a 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 later than 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. The value returned is always of datatype DATE, even if you specify a different datetime 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 on which 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 value of 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 day truncated 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 a value 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 time in 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');