本文主要介绍一些日期和时间函数的类型。

时区转换

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

SELECT timestamp ‘2012-10-31 01:00 UTC’; 2012-10-31 01:00:00.000 UTC

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 format

返回指定格式的unixtime时间戳。

from_unixtime(unixtime, hours, minutes) → timestamp with time zone

返回为hours和minutes对应时区的unixtime时间戳。

localtime -> time

返回查询开始时的当前时间。

localtimestamp -> timestamp

返回查询开始时的当前时间戳。

now() → timestamp with time zone

这是current_timestamp的另一种表达。

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支持如下单位:

单位示例结果
second2001-08-22 03:04:05.000
minute2001-08-22 03:04:00.000
hour2001-08-22 03:00:00.000
day2001-08-22 00:00:00.000
week2001-08-20 00:00:00.000
month2001-08-01 00:00:00.000
quarter2001-07-01 00:00:00.000
year2001-01-01 00:00:00.000

上面的例子使用时间戳: 2001-08-22 03:04:05.321 作为输入。

date_trunc(unit, x) → [same as input]返回x截取到单位unit之后的值。

间隔函数

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

单位描述
millisecondMilliseconds
secondSeconds
minuteMinutes
hourHours
dayDays
weekWeeks
monthMonths
quarterQuarters of a year
yearYears

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手册列出的,描述了各种格式化描述符:

分类符说明
%aAbbreviated weekday name (Sun .. Sat)
%bAbbreviated month name (Jan .. Dec)
%cMonth, numeric (0 .. 12)
%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%dDay of the month, numeric (00 .. 31)
%eDay of the month, numeric (0 .. 31)
%fFraction of second (6 digits for printing: 000000 .. 999000; 1 - 9 digits for parsing: 0 .. 999999999)
%HHour (00 .. 23)
%hHour (01 .. 12)
%IHour (01 .. 12)
%iMinutes, numeric (00 .. 59)
%jDay of year (001 .. 366)
%kHour (0 .. 23)
%lHour (1 .. 12)
%MMonth name (January .. December)
%mMonth, numeric (00 .. 12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00 .. 59)
%sSeconds (00 .. 59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00 .. 53), where Sunday is the first day of the week
%uWeek (00 .. 53), where Monday is the first day of the week
%VWeek (01 .. 53), where Sunday is the first day of the week; used with %X
%vWeek (01 .. 53), where Monday is the first day of the week; used with %x
%WWeekday name (Sunday .. Saturday)
%wDay of the week (0 .. 6), where Sunday is the first day of the week
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric (two digits)
%%A literal % character
%xx, for any x not listed above
  • Timestamp被截断为毫秒。
  • 解析时,两位数的年份格式假定为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。

抽取函数

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

描述
YEARyear()
QUARTERquarter()
MONTHmonth()
WEEKweek()
DAYday()
DAY_OF_MONTHday()
DAY_OF_WEEKday_of_week()
DOWday_of_week()
DAY_OF_YEARday_of_year()
DOYday_of_year()
YEAR_OF_WEEKyear_of_week()
YOWyear_of_week()
HOURhour()
MINUTEminute()
SECONDsecond()
TIMEZONE_HOURtimezone_hour()
TIMEZONE_MINUTEtimezone_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)
hh12小时制 (1-12)
hh2424小时制 (0-23)
mi分钟 (0-59)
mm月 (01-12)
ss秒 (0-59)
yyyy4位年
yy2位年
说明 目前不支持大小写区分,所有说明符必须为小写。

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兼容性

DLA 已经支持的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)

例子:

SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
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)

例子:

SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
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()

例子:

SELECT CURDATE();
SELECT CURRENT_DATE();
SELECT CURRENT_DATE;

SYSDATE

Returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ format.

支持的语法:

SYSDATE()

例子:

SELECT SYSDATE();

CURRENT_DATE

Synonyms for CURDATE()

支持的语法:

CURRENT_DATE , CURRENT_DATE()

例子:

SELECT CURRENT_DATE;
SELECT CURRENT_DATE();

CURRENT_TIME

Synonyms for CURTIME()

支持的语法:

CURRENT_TIME , CURRENT_TIME()

例子:

SELECT CURRENT_TIME;
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()

例子:

SELECT CURTIME();
SELECT CURRENT_TIME();
SELECT CURRENT_TIME;

Yearmonth

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

DATE

Extracts the date part of the date or datetime expression expr.

支持的语法:

DATE(expr)

例子:

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)

例子:

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
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)

例子:

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%d');
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d');
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%y-%m-%d');
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d %T');
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d %r');

DAY

DAY() is a synonym for DAYOFMONTH().

支持的语法:

DAY(date)

例子:

SELECT DAY('2007-02-03');
SELECT DAYOFMONTH('2007-02-03');

DAYNAME

Returns the name of the weekday for date.

支持的语法:

DAYNAME(date)

例子:

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

DAYOFWEEK

Returns the weekday index for date.

支持的语法:

DAYOFWEEK(date)

例子:

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

DAYOFYEAR

Returns the day of the year for date, in the range 1 to 366.

支持的语法:

DAYOFYEAR(date)

例子:

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)

例子:

SELECT EXTRACT(YEAR FROM '2009-07-02');
SELECT EXTRACT(MONTH FROM '2009-07-02');
SELECT EXTRACT(DAY FROM '2009-07-02');
SELECT EXTRACT(HOUR FROM '2003-01-02 10:30:00');
SELECT EXTRACT(MINUTE FROM '2003-01-02 10:30:00');
SELECT EXTRACT(SECOND FROM '2003-01-02 10:30:00');

FROM_DAYS

Given a day number N, returns a DATE value.

支持的语法:

FROM_DAYS(N)

例子:

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)

例子:

SELECT FROM_UNIXTIME(1447430881);
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)

例子:

SELECT HOUR('10:05:03');
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)

例子:

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

LOCALTIME

LOCALTIME and LOCALTIME() are synonyms for NOW()

支持的语法:

LOCALTIME , LOCALTIME()

例子:

SELECT LOCALTIME;
SELECT LOCALTIME();

NOW

NOW() is synonyms for LOCALTIME and LOCALTIME()

支持的语法:

NOW()

例子:

SELECT NOW();

LOCALTIME

LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for NOW()

支持的语法:

LOCALTIME , LOCALTIME()

例子:

SELECT LOCALTIMESTAMP;
SELECT LOCALTIMESTAMP();

MAKETIME

Returns a time value calculated from the hour, minute, and second arguments.

支持的语法:

MAKETIME(hour,minute,second)

例子:

SELECT MAKETIME(12,15,30);

MINUTE

Returns the minute for time, in the range 0 to 59.

支持的语法:

MINUTE(time)

例子:

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)

例子:

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

MONTHNAME

Returns the full name of the month for date.

支持的语法:

MONTHNAME(date)

例子:

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

NOW

Returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’.

支持的语法:

NOW()

例子:

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)

例子:

SELECT PERIOD_ADD(200801,2);

PERIOD_DIFF

Returns the number of months between periods P1 and P2.

支持的语法:

PERIOD_DIFF(P1,P2)

例子:

SELECT PERIOD_DIFF(200802,200703);

QUARTER

Returns the quarter of the year for date, in the range 1 to 4.

支持的语法:

QUARTER(date)

例子:

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

SECOND

Returns the second for time, in the range 0 to 59.

支持的语法:

SECOND(time)

例子:

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)

例子:

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)

例子:

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)

例子:

SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY);
SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
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)

例子:

SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
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)

例子:

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)

例子:

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)

例子:

SELECT TIMESTAMP('2003-12-31');
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)

例子:

SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
SELECT TIMESTAMPADD(YEAR,1,'2003-01-02');
SELECT TIMESTAMPADD(MONTH,1,'2003-01-02');
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)

例子:

SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
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)

例子:

SELECT TIME_TO_SEC('22:23:00');
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)

例子:

SELECT TO_DAYS('2007-10-07');
SELECT TO_DAYS('2008-10-07');
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)

例子:

SELECT TO_SECONDS('2009-11-29');
SELECT TO_SECONDS('2009-11-29 13:43:32');
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)

例子:

SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');
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()

例子:

SELECT UTC_DATE()

UTC_TIME

Returns the current UTC time as a value in ‘HH:MM:SS’.

支持的语法:

UTC_TIME()

例子:

SELECT UTC_TIME()

UTC_TIMESTAMP

Returns the current UTC date and time as a value in ‘YYYY-MM-DD HH:MM:SS’.

支持的语法:

UTC_TIMESTAMP()

例子:

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])

例子:

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

WEEKDAY

Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

支持的语法:

WEEKDAY(date)

例子:

SELECT WEEKDAY('2008-02-03 22:23:00');
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)

例子:

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)

例子:

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)

例子:

SELECT YEARWEEK('1987-01-01');
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)

例子:

SELECT TIMEDIFF('2008-12-31 23:59:50', '2008-12-31 23:59:59');
SELECT TIMEDIFF('2008-12-30 23:59:59', '2008-12-31 23:59:59');
SELECT TIMEDIFF('2008-12-30 11:59:59', '2008-12-31 23:59:59');
SELECT TIMEDIFF('2008-12-30 11:50:59', '2008-12-31 23:59:59');
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)

例子:

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)

例子:

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

Datetime函数Oracle兼容性

DLA 目前已经支持Oracle函数。

ADD_MONTHS

ADD_MONTHS returns the date date plus integer months.

支持的语法:

ADD_MONTHS(date, integer)

例子:

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

例子:

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

例子:

SELECT CURRENT_TIMESTAMP;

DBTIMEZONE

DBTIMEZONE returns the value of the database time zone.

支持的语法:

DBTIMEZONE()

例子:

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)

例子:

SELECT EXTRACT(YEAR FROM '2001-01-01');
SELECT EXTRACT(MONTH FROM '2001-01-01');
SELECT EXTRACT(DAY FROM '2001-01-01');
SELECT EXTRACT(HOUR FROM '2001-01-01 19:10:11');
SELECT EXTRACT(MINUTE FROM '2001-01-01 19:10:11');
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

例子:

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()

例子:

SELECT LOCALTIMESTAMP();

MONTH_BETWEEN

MONTHS_BETWEEN returns number of months between dates date1 and date2.

支持的语法:

MONTH_BETWEEN(date1, date2)

例子:

SELECT MONTH_BETWEEN('2017-03-03', '2017-07-07');
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)

例子:

SELECT NEXT_DAY('2010-10-10','TUESDAY');
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)

例子:

SELECT ROUND(TIMESTAMP '2010-08-21', 'YY');
SELECT ROUND(TIMESTAMP '2010-08-21', 'MM');
SELECT ROUND(TIMESTAMP '2010-08-21', 'q');
SELECT ROUND(TIMESTAMP '2010-08-21', 'D');
SELECT ROUND(TIMESTAMP '2010-08-21 19:00:00', 'DD');

SESSIONTIMEZONE

SESSIONTIMEZONE returns the time zone of the current session.

支持的语法:

SESSIONTIMEZONE()

例子:

SELECT SESSIONTIMEZONE();

SYSDATE

SYSDATE returns the current date and time set for the operating system on which the database resides.

支持的语法:

SYSDATE()

例子:

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)

例子:

SELECT TO_CHAR('2013-05-17 23:35:10', '%Y-%m-%d %H:%i:%s');
SELECT TO_CHAR('2013-05-17 00:35:10', '%Y-%m-%d %H:%i:%s');
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)

例子:

SELECT TRUNC(TIMESTAMP '2010-08-21', 'YY');
SELECT TRUNC(TIMESTAMP '2010-08-21', 'MM');
SELECT TRUNC(TIMESTAMP '2010-08-21', 'q');
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)

例子:

SELECT TO_DATE('2013-05', '%Y-%m');
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)

例子:

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