SQL Fundamentals || Single-Row Functions || 日期函数date functions
SQL Fundamentals || Single-Row Functions || 字符函数 character functions
SQL Fundamentals || Single-Row Functions || 数字函数number functions
SQL Fundamentals || Single-Row Functions || 日期函数date functionsSQL Fundamentals || Single-Row Functions || 转换函数 Conversion function
SQL Fundamentals || Single-Row Functions || 通用函数 General function
日期函数date functions
- Date functions - Date arithmetic operations return date or numeric values. Functions under the category are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND and TRUNC.
- MONTHS_BETWEEN function returns the count of months between the two dates.
- ADD_MONTHS function add 'n' number of months to an input date.
- NEXT_DAY function returns the next day of the date specified.
- LAST_DAY function returns last day of the month of the input date.
- ROUND and TRUNC functions are used to round and truncates the date value.
- The oracle database stores dates in an internal numeric format: century, year, month, day, hours, minutes ,and seconds.
数据库内部以数字格式存储日期,可表示世纪、年,月,日,时,分,秒.
- This data is stored internally as follow:
CENTYURY YEAR MONTH DAY HOUR MINUTE SECOND
19 89 06 17 17 10 43
- 当带有日期列的记录被插入到表中时,世纪信息从SYSDATE函数中获取。
- 当日期列被显示在屏幕上时,世纪部分默认不被显示.
- The default date display format is DD-MON-RR.
默认显示和输入格式是DD-MON-RR.
- Enables you to store 21st-century dates in the 20th century by specifying only the last two digits of the year
- Enables you to store 20th-century dates in the 21st century in the same way
(3.3.1)SYSDATE and CURRENT_DATE
Using the SYSDATE Function
SYSDATE is a function that returns:
- Date
- Time
SQL> select sysdate from dual;
SYSDATE
------------------
04-JUL-17
CURRENT_DATE也可以返回当前时间,但是CURRENT_DATE函数返回会话时区中的当前日期.
如:在中国访问美国的远程数据库,SYSDATE会返回美国的日期和时间;CURRENT_DATE会返回中国的日期和时间.
(3.3.2)NLS(国家语言字符支持National Language Support )
如何查看数据库使用的字符集:select * from V$NLS_PARAMETERS;
(3.3.3)RR Date Format
RR日期格式类似于YY格式,但是可使用它来指定不同的世纪.
Current Year |
Specified Date |
RR Format |
YY Format |
1995 |
27-OCT-95 |
1995 |
1995 |
1995 |
27-OCT-17 |
2017 |
1917 |
2001 |
27-OCT-17 |
2017 |
2017 |
2001 |
27-OCT-95 |
1995 |
2095 |
|
If the specified two-digit year is: |
||||||||||||||
0-49 |
50-99 |
||||||||||||||
If two digits of the current year are |
0-49 |
The return date is in the current century 当前世纪年在0-49,指定世纪年在0-49则返回当前世纪年
|
The return date is in the century before the current one 当前世纪年在0-49,指定世纪年在50-99则返回当前世纪年的前一年
|
||||||||||||
50-99 |
The return date is in the century after the current one 当前世纪年在50-99,指定世纪年在0-49则返回当前世纪年的后一年
|
The return date is in the current century 当前世纪年在50-99,指定世纪年在50-99,则返回当前世纪年.
|
(3.3.4)arithmetic with Dates
因为数据库把日期作为数字存储,因此可以对日期进行加减运算.
- Add or subtract a number to or from a date for a resultant date value.
- Subtract two dates to find the number of days between those dates.
- Add hours to a date by dividing the number of hours by 24.
Operation |
Result |
Description |
Date+number |
Date |
Adds a number of days to a date给日期加几天 |
Date-number |
Date |
Subtracts a number of days from a date减去几天 |
Date-date |
Number of days |
Subtracts one date from another两个日期相减 |
Date+number/24 |
Date |
Adds a number of hours to a date 加几个小时 |
(3.3.3)其他日期函数
注:数字函数ROUND和TRUNC还可以对日期做操作.
SYSDATE='25-JUL-03'
function |
purpose |
ROUND(SYSDATE,'MONTH')四舍五入 MONTH:日期是1-15,当前月的第一天,如期是16-31会是下个月的第1天 |
01-AUG-03 |
ROUND(SYSDATE,'YEAR')四舍五入 YEAR:月1-6会是当前年的1月1日,月7-12会是下一年的1月1日 |
01-JAN-04 |
TRUNC(SYSDATE,'MONTH')截断 MONTH:当前月第一天 |
01-JUL-03 |
TRUNC(SYSDATE,'YEAR')截断 当前年的1月1日 |
01-JAN-03 |
MONTHS_BETWEEN(date1,date2) |
Number of months between two dates SQL> SELECT 2 MONTHS_BETWEEN('1-OCT-2012','10-NOV-2012') 3 FROM DUAL;
MONTHS_BETWEEN('1-OCT-2012','10-NOV-2012') ------------------------------------------ -1.2903226 |
ADD_MONTHS(date, n) |
Add calendar months to date |
NEXT_DAY(date, 'char') char表示星期几 |
Next day of the date specified 找到date开始的下一个星期几的日期 SQL> SELECT NEXT_DAY('1-OCT-2012','Friday') FROM DUAL;
NEXT_DAY('1-OCT-20 ------------------ 05-OCT-12 |
LAST_DAY(date) |
Last day of the month 每月的最后一天 SQL> SELECT LAST_DAY('1-FEB-2012') FROM DUAL;
LAST_DAY('1-FEB-20 ------------------ 29-FEB-12
|
日期函数是直接对日期进行相关的操作;
在ORACLE中,用户可以直接通过SYSDATE伪列(不是表中的列但是可以使用)表示出当前的系统时间.
SQL> SELECT SYSDATE FROM DUAL;
修改日期显示格式:
SQL> ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
日期操作公式:
日期-数字=日期
日期+数字=日期
日期-日期=数字(天数)
今天,三天前,三天后:
SQL> SELECT SYSDATE,SYSDATE+3,SYSDATE-3 FROM DUAL;
今天,5天前和3天前相差多少天:
SQL> SELECT SYSDATE,SYSDATE+5-SYSDATE-3 FROM DUAL;
今天到雇用日期的天数,十天前到雇佣日期的天数:
SQL> SELECT ENAME,SYSDATE-hiredate,(SYSDATE-10)-hiredate FROM emp;
使用TRUNCT函数处理天数:
SQL> SELECT ENAME,TRUNC(SYSDATE-hiredate),TRUNC((SYSDATE-10)-hiredate) FROM emp;
日期函数:避免闰年,一个月有不同天数的问题,通过日期函数的计算可以得到一个准确的时间.
函数名称 |
描述 |
ADD_MONTHS(日期,数字) |
在指定的日期上加入指定的月数,求出新的日期 SQL> SELECT SYSDATE,ADD_MONTHS(SYSDATE,3) FROM DUAL; SQL> SELECT ename,hiredate,ADD_MONTHS(hiredate,3) FROM emp; 返回的是一个日期. |
MONTHS_BETWEEN(日期1,日期2) |
求出两个日期间的雇佣月数 雇佣总月数. SQL> SELECT ename,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate))AS MONTH FROM emp; 雇佣总年数: SQL> SELECT ename,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12)AS year FROM emp; 查询出每个雇员的编号、姓名、雇佣日期、已雇佣的年数、月数、天数(几年几月几天): 步骤一: SELECT empno 雇员编号,ename 雇员姓名, hiredate 雇佣日期 , TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) 已雇佣年数 FROM emp ; 步骤二: SELECT empno 雇员编号,ename 雇员姓名, hiredate 雇佣日期 , TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) 已雇佣年数 , TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12)) 已雇佣月数 FROM emp ; 步骤三: SELECT empno 雇员编号,ename 雇员姓名, hiredate 雇佣日期 ,TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) 已雇佣年数 , TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12)) 已雇佣月数 , TRUNC(sysdate-ADD_MONTHS(hiredate,MONTHS_BETWEEN(sysdate,hiredate))) 已雇佣天数 FROM emp ;
SELECT ename,sysdate,hiredate, TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) AS countyear, TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12)) AS countmonth, TRUNC(sysdate-ADD_MONTHS(hiredate,MONTHS_BETWEEN(sysdate,hiredate)))AS countday FROM emp;
----计算年的时候存在小数,这里面的数据就是月,所以取余数就是月数. ----天数:天应该抛去年和月的数字信息,如果要计算天数,就是将日期1-日期2;日期1是SYSDATE,日期2应该去掉年和月,利用ADD_MONTHS函数加上. 返回的是一个数字. |
NEXT_DAY(日期,星期数) |
求出下一个的星期X的具体日期 SQL> SELECT SYSDATE,NEXT_DAY(SYSDATE,'Fri') FROM DUAL; 返回的是一个日期. |
LAST_DAY(日期) |
求出指定日期的最后一天日期 查询员工雇佣时间在雇佣当月倒数第三天的员工. SQL> SELECT ename,hiredate,LAST_DAY(hiredate) FROM emp WHERE LAST_DAY(hiredate)-2=hiredate; 返回的是一个日期. |
EXTRACT(格式 FROM 数据) |
EXTRACT ([ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND ] | [ TIMEZONE_HOUR | TIMEZONE_MINUTE ] | [ TIMEZONE_REGION | TIMEZONE_ABBR ] FROM [ 日期(date_value) | 时间间隔(interval_value)] )
EXTRACT(MONTH FROM DATE '2001-09-19') months , EXTRACT(DAY FROM DATE '2001-09-19') days FROM dual ;
SELECT EXTRACT(YEAR FROM SYSTIMESTAMP) years , EXTRACT(MONTH FROM SYSTIMESTAMP) months , EXTRACT(DAY FROM SYSTIMESTAMP) days , EXTRACT(HOUR FROM SYSTIMESTAMP) hours , EXTRACT(MINUTE FROM SYSTIMESTAMP) minutes , EXTRACT(SECOND FROM SYSTIMESTAMP) seconds FROM dual ;
1、将字符串变为时间戳 SELECT TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') ,TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')FROM DUAL; 2、两个时间戳的时间间隔:320天 SELECT EXTRACT(DAY FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') -TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days FROM DUAL; DAYS ---------- 320 3、子查询,继续取得时间间隔的时分秒. SELECT TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_one ,TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss') datetime_two FROM DUAL; 4、使用EXTRACT计算时间间隔 SELECT EXTRACT(DAY FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') -TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days, EXTRACT(HOUR FROM datetime_one-datetime_two) hours FROM( SELECT TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_one ,TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss') datetime_two FROM DUAL ); DAYS HOURS ---------- ---------- 320 3 5、分秒 SELECT EXTRACT(DAY FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') -TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days, EXTRACT(HOUR FROM datetime_one-datetime_two) hours, EXTRACT(MINUTE FROM datetime_one-datetime_two) minutes, EXTRACT(SECOND FROM datetime_one-datetime_two) seconds FROM( SELECT TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_one ,TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss') datetime_two FROM DUAL ); DAYS HOURS MINUTES SECONDS ---------- ---------- ---------- ---------- 320 3 9 24
总结: SELECT EXTRACT(DAY FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') - TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days , EXTRACT(HOUR FROM datetime_one - datetime_two) hours , EXTRACT(MINUTE FROM datetime_one - datetime_two) minutes , EXTRACT(SECOND FROM datetime_one - datetime_two) seconds FROM ( SELECT TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_one , TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss') datetime_two FROM dual) ; |
Day |
Abbreviation |
Monday |
Mon |
Tuesday |
Tue |
Wednesday |
Wed |
Thursday |
Thu |
Friday |
Fri |
Saturday |
Sat |
Sunday |
Sun |
Months |
Days |
Abbreviation |
January |
31days |
Jan |
February |
28 days in a common year and 29 days in Leap years |
Feb |
March |
31 days |
Mar |
April |
30 days |
Apr |
May |
31 days |
|
June |
30 days |
|
July |
31 days |
|
August |
31 days |
Aug |
September |
30 days |
Sept |
October |
31 days |
Oct |
November |
30 days |
Nov |
December |
31 days |
Dec |