SQL Fundamentals || Single-Row Functions || 日期函数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则返回当前世纪年

Current Year

Specified Date

RR Format

2001

27-OCT-17

2017
 

The return date is in the century before the current one

当前世纪年在0-49，指定世纪年在50-99则返回当前世纪年的前一年

Current Year

Specified Date

RR Format

2001

27-OCT-95

1995
 

50-99

The return date is in the century after the current one

当前世纪年在50-99，指定世纪年在0-49则返回当前世纪年的后一年

Current Year

Specified Date

RR Format

1995

27-OCT-17

2017

The return date is in the current century

当前世纪年在50-99，指定世纪年在50-99，则返回当前世纪年.

Current Year

Specified Date

RR Format

1995

27-OCT-95

1995

 

（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 数据)
      • 日期时间分割，或计算给定两个日期的间隔.
      • 在Oracle 9i之后增加了一个extract()函数，此函数的主要功能是可以从一个日期时间（DATE）或者是时间间隔（INTERVAL）中截取出特定的部分，此函数使用语法如下所示

EXTRACT ([ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND ]

| [ TIMEZONE_HOUR | TIMEZONE_MINUTE ]

| [ TIMEZONE_REGION | TIMEZONE_ABBR ]  

FROM [ 日期（date_value） | 时间间隔（interval_value）] ) 

 

      • 从日期时间之中取出年、月、日数据。
        • SELECT    EXTRACT(YEAR FROM DATE '2001-09-19') years ,

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 ;

 

      • 取得时间间隔(需要使用转换函数TO_TIMESTAMP()将字符串变为时间戳)

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

 

 


 

 

 

posted @ 2017-07-24 17:03  寻香径  阅读(490)  评论(0)  编辑  收藏  举报