oracle的datetime函数,日期和时间函数

最新推荐文章于 2024-05-10 10:52:30 发布

我有改名卡

最新推荐文章于 2024-05-10 10:52:30 发布

阅读量2.6k

点赞数 1

文章标签： oracle的datetime函数

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

时区转换

运算符：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时间戳。

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

单位

示例结果

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

分类符

说明

Abbreviated weekday name (Sun .. Sat)

Abbreviated month name (Jan .. Dec)

Month, numeric (0 .. 12)

Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)

Day of the month, numeric (00 .. 31)

Day of the month, numeric (0 .. 31)

Fraction of second (6 digits for printing: 000000 .. 999000; 1 - 9 digits for parsing:

0 .. 999999999)

Hour (00 .. 23)

Hour (01 .. 12)

Minutes, numeric (00 .. 59)

Day of year (001 .. 366)

Hour (0 .. 23)

Hour (1 .. 12)

Month name (January .. December)

Month, numeric (00 .. 12)

AM or PM

Time, 12-hour (hh:mm:ss followed by AM or PM)

Seconds (00 .. 59)

Time, 24-hour (hh:mm:ss)

Week (00 .. 53), where Sunday is the first day of the week

Week (00 .. 53), where Monday is the first day of the week

Week (01 .. 53), where Sunday is the first day of the week; used with %X

Week (01 .. 53), where Monday is the first day of the week; used with %x

Weekday name (Sunday .. Saturday)

Day of the week (0 .. 6), where Sunday is the first day of the week

Year for the week where Sunday is the first day of the week, numeric, four digits;

used with %V

Year for the week, where Monday is the first day of the week, numeric, four digits;

used with %v

Year, numeric, four digits

Year, numeric (two digits)

A literal % character

x, 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。

抽取函数

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

域

描述

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的使用手册, , 介绍了支持的格式：

格式

描述

- / , . ; :

标点符号被忽略

天 (1-31)

12小时制 (1-12)

hh24

24小时制 (0-23)

分钟 (0-59)

月 (01-12)

秒 (0-59)

yyyy

4位年

2位年

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

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');

我有改名卡

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle的datetime函数,日期和时间函数

本文主要介绍一些日期和时间函数的类型。时区转换运算符：AT TIME ZONE，用于设置一个时间戳的时区。SELECT timestamp ‘2012-10-31 01:00 UTC’; 2012-10-31 01:00:00.000 UTCSELECT timestamp ‘2012-10-31 01:00 UTC’ AT TIME ZONE ‘America/Los_Angeles’; 201...
复制链接

扫一扫