日期和时间函数是什么?-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文

日期和时间函数是什么?

nicenelly 2017-10-31 14:18:01 8090

时区转换


运算符: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 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支持如下单位:
单位示例结果
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_parse和str_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 orPM)
%SSeconds (00 .. 59)
%sSeconds (00 .. 59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00 .. 53), where Sunday is the first day ofthe week
%uWeek (00 .. 53), where Monday is the first day ofthe week
%VWeek (01 .. 53), where Sunday is the first day ofthe week; used with %X
%vWeek (01 .. 53), where Monday is the first day ofthe week; used with %x
%WWeekday name (Sunday .. Saturday)
%wDay of the week (0 .. 6), where Sunday is the firstday of the week
%XYear for the week where Sunday is the first day ofthe week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day ofthe week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric (two digits)
%%A literal % character
%xx, 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。

抽取函数


可以使用抽取函数来抽取如下域:
描述
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位年


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

云服务器登录 云服务器设置
分享到
取消 提交回答
全部回答(0)
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

相似问题
最新问题