日期函數

1、sysdate：得到數據庫服務器的當前日期和時間 

2、current_date:得到客戶端的當前日期和時間 

select current_date from dual; 

因為數據庫把日期作為數字存儲，因此可以對日期進行加減運算,單位是天。 

1 ) date + n:加減幾天，n可以是負的 

2）date + n/24:加減幾個小時 

3）date  date：相差的天數

計算90號部門的雇員在公司工作的總周數 

select last_name,(sysdate - hire_date)/7 weeks 

  from employees where department_id=90; 

例1:給當前日期分別加減3天 

例2:給當前日期加1個小時 

select to_char(sysdate,'YYYYMMDD HH24:MI:SS'), 

       to_char(sysdate + 1/24,'YYYYMMDD HH24:MI:SS') 

  from dual;

查找今天之后的下一個星期一是幾月幾號？ 注意字符集

select next_day(sysdate,'星期一') from dual; 

ORA01846: 周中的日無效 

select next_day(sysdate,'monday') from dual; 

ROUND(date[,'fmt']):  

進位規則:秒=30,分=30,時=12,日=16,月=7; 超過上面分割線就向前一位進1

TRUNC(date [, 'fmt']):  （常用,后面不接參數就是將日期的時分秒去掉）

例如 :

規則：按月進行截斷，直接截斷到給定日期的本月的1號 

            按年進行截斷，直接截斷到給定日期的本年月的1月1號  

select sysdate,trunc(sysdate,'month'), trunc(sysdate,'year')  from dual;

Oracle中，時區分為客戶端的和服務器的兩種，分別設置。

設置客戶端的時區使用會話參數:time_zone

1.查看數據庫,會話時區:

select dbtimezone from dual;

select sessiontimezone from dual;

2.設置會話時區為西五區(美國東部時間)

alter session set time_zone = '-05:00'; (絕對偏移量,負為西)

3.設置本會話使用數據庫的時區

alter session set time_zone = dbtimezone；

4.把時區設置成本地

alter session set time_zone = local;  -- (操作系統,可能不支持)

5.時區設置成某地域的時區

alter session set time_zone = 'American/New_York'; -- (操作系統,可能不支持)

和客戶端有關的日期時間函數：3個

current_date   current_timestamp  localtimestamp

三個都返回客戶端的當前日期和時間，區別在于值的數據類型不一樣

sessiontimezone：該函數返回客戶端的時區設置

查看系統各個時間函數:

select sessiontimezone ,

       current_date , 

       current_timestamp , 

       localtimestamp 

  from dual; ---(注意三個時間函數的精度)

數據庫的時區

返回數據庫的時區: 0時區

select dbtimezone from dual;

DBA通過指定手工建庫時CREATE DATABASE 語句的SET TIME_ZONE 子句

來設置數據庫的默認時區。如果省略，那么默認數據庫時區是

操作系統時區。

如果操作系統的時區格式是oracle不支持的，那么就把數據庫的時區設為0時區。官方推薦數據庫的時區都使用0時區。

ALTER SESSION語句不能改變數據庫時區。

TIMESTAMP類型：時間戳類型。3種

-TIMESTAMP數據類型是DATE數據類型的擴展：

1）TIMESTAMP (fractional_seconds_ PRecision)     

2）TIMESTAMP (fractional_seconds_precision) WITH 

     TIME ZONE     帶時區的時間戳

3）TIMESTAMP (fractional_seconds_precision) WITH 

     LOCAL TIME ZONE     帶本地時區的時間戳

其中：fractional_seconds_precision小數秒精度取

值范圍是0-9。默認是6(微秒 ms)

特別注意：最后一種timestamp類型的列，它的值在保存到表中時，會按照數據庫的時區進行自動轉換；當從表中把該類型的列值取出來時，有按照客戶端的時區做自動轉換。

前面兩種timestamp類型的列值不會做這樣的轉換。

例子:

--創建訂單表 CREATE TABLE web_orders   (      order_date    TIMESTAMP WITH TIME ZONE,         --訂貨時間使用 帶時區的時間戳     delivery_time TIMESTAMP WITH local TIME ZONE   --送貨時間使用 帶本地時區的時間戳  ); --美國客戶插入訂單 INSERT INTO web_orders VALUES     (current_date,             current_timestamp + 2); COMMIT; --美國客戶查詢。看到正確的日期（西五區的時間） SELECT * FROM   web_orders; --在sqlplus中，國內的物流哥們查詢，看到正確的時間（東八區的時間） SELECT * FROM   web_orders;

時間間隔類型

INTERVAL數據類型用來存儲兩個日期值之間的差值。有兩類INTERVAL： 

INTERVAL YEAR(year_precision) TO MONTH

INTERVAL DAY(day_precision) TO SECOND(fractional_seconds_precision)

year_precision                    是YEAR字段的精度，取值范圍0—9，默認為2.

fractional_seconds_precision 是小數秒的精度，取值范圍0—9，默認為6. 

day_precision                    是DAY字段的最大值（取值范圍0—9，默認為2. ）

l=eg:  

1、使用年到月的間隔

create table warranty(

  prod_id number,產品編號

  warranty_tiem interval year(3) to month       --擔保時間,year(3)-年的部分最少有三位

);

注意間隔類型的字面量寫法：ansi語法

insert into warranty values(123,interval '8' month);             --

SQL 錯誤: ORA01873: 間隔的前導精度太小

insert into warranty values(456,interval '200' year);      --必須指定年的精度有3位

insert into warranty values(456,interval '200' year(3));   --正確寫法

注意間隔類型的字面量寫法,oracle簡單語法：'年-月'

insert into warranty values(789,'200-11');   200年 零 11個月簡單寫法

2、使用天到秒的間隔

create table lab(

  exp_id number,

  test_time interval day to second  --day不指定精度默認位2 秒精度默認為6

);

實驗123的測試時間是90天一次

insert into lab values(123,'90 00:00:00');

insert into lab values(456, interval '06 03:30:16' day to second);

select * from lab;

間隔類型的使用常用于日期的加減運算中

其它日期時間函數

1.EXTRACT 函數:從給定的日期中抽取出特定的部分

extract(.... from .....)

SELECT  EXTRACT ([YEAR] [MONTH][DAY] 

                  [HOUR] [MINUTE][SECOND]

 FROM [datetime_value_expression] |

   [interval_value_expression]);

例如:

select sysdate,extract(year from sysdate)

  from dual;

查詢雇員入職的月份

select hire_date,extract(month from hire_date)

  from employees;

2、tz_offset()

將命名地區形式的時區轉換成時區的絕對偏移量

select tz_offset('Asia/Shanghai'),

       tz_offset('Canada/Yukon')

  from dual;     

要得到合法時區名的列表，可以查詢V$TIMEZONE_NAMES動態性能視圖。

SELECT * FROM V$TIMEZONE_NAMES;

3、to_timestamp(char,'fmt')

將字符串以給定的日期格式模型轉換成時間戳

select to_timestamp('20071010 15:25:00','YYYYMMDD HH24:MI:SS')

  from dual;

4、to_yminterval('year-month')

將字符串轉成成年到月的間隔

將雇員的入職日期加上1年2個月

select hire_date,

      hire_date + to_yminterval('01-02')

 from employees;

5、to_dsinterval('day hh:mi:ss')

將字符串轉成成天到秒的間隔 

將雇員的入職日期加上100天10個小時

select to_char(hire_date,'YYYYMMDD HH24:MI:SS'),

      to_char(hire_date + to_dsinterval('100 10:00:00'),

              'YYYYMMDD HH24:MI:SS')

 from employees;

查找今天之后的下一個星期一是幾月幾號？ 注意字符集

select next_day(sysdate,'星期一') from dual; 

ORA01846: 周中的日無效 

select next_day(sysdate,'monday') from dual; 

ROUND(date[,'fmt']):  

進位規則:秒=30,分=30,時=12,日=16,月=7; 超過上面分割線就向前一位進1

TRUNC(date [, 'fmt']):  （常用,后面不接參數就是將日期的時分秒去掉）

例如 :

規則：按月進行截斷，直接截斷到給定日期的本月的1號 

            按年進行截斷，直接截斷到給定日期的本年月的1月1號  

select sysdate,trunc(sysdate,'month'), trunc(sysdate,'year')  from dual;

oracle中，時區分為客戶端的和服務器的兩種，分別設置。

設置客戶端的時區使用會話參數:time_zone

1.查看數據庫,會話時區:

select dbtimezone from dual;

select sessiontimezone from dual;

2.設置會話時區為西五區(美國東部時間)

alter session set time_zone = '-05:00'; (絕對偏移量,負為西)

3.設置本會話使用數據庫的時區

alter session set time_zone = dbtimezone；

4.把時區設置成本地

alter session set time_zone = local;  -- (操作系統,可能不支持)

5.時區設置成某地域的時區

alter session set time_zone = 'American/New_York'; -- (操作系統,可能不支持)

和客戶端有關的日期時間函數：3個

current_date   current_timestamp  localtimestamp

三個都返回客戶端的當前日期和時間，區別在于值的數據類型不一樣

sessiontimezone：該函數返回客戶端的時區設置

查看系統各個時間函數:

select sessiontimezone ,

       current_date , 

       current_timestamp , 

       localtimestamp 

  from dual; ---(注意三個時間函數的精度)

數據庫的時區

返回數據庫的時區: 0時區

select dbtimezone from dual;

DBA通過指定手工建庫時CREATE DATABASE 語句的SET TIME_ZONE 子句

來設置數據庫的默認時區。如果省略，那么默認數據庫時區是

操作系統時區。

如果操作系統的時區格式是oracle不支持的，那么就把數據庫的時區設為0時區。官方推薦數據庫的時區都使用0時區。

ALTER SESSION語句不能改變數據庫時區。

TIMESTAMP類型：時間戳類型。3種

-TIMESTAMP數據類型是DATE數據類型的擴展：

1）TIMESTAMP (fractional_seconds_ precision)     

2）TIMESTAMP (fractional_seconds_precision) WITH 

     TIME ZONE     帶時區的時間戳

3）TIMESTAMP (fractional_seconds_precision) WITH 

     LOCAL TIME ZONE     帶本地時區的時間戳

其中：fractional_seconds_precision小數秒精度取

值范圍是0-9。默認是6(微秒 ms)

特別注意：最后一種timestamp類型的列，它的值在保存到表中時，會按照數據庫的時區進行自動轉換；當從表中把該類型的列值取出來時，有按照客戶端的時區做自動轉換。

前面兩種timestamp類型的列值不會做這樣的轉換。

例子:

--創建訂單表 CREATE TABLE web_orders   (      order_date    TIMESTAMP WITH TIME ZONE,         --訂貨時間使用 帶時區的時間戳     delivery_time TIMESTAMP WITH local TIME ZONE   --送貨時間使用 帶本地時區的時間戳  ); --美國客戶插入訂單 INSERT INTO web_orders VALUES     (current_date,             current_timestamp + 2); COMMIT; --美國客戶查詢。看到正確的日期（西五區的時間） SELECT * FROM   web_orders; --在sqlplus中，國內的物流哥們查詢，看到正確的時間（東八區的時間） SELECT * FROM   web_orders;

時間間隔類型

INTERVAL數據類型用來存儲兩個日期值之間的差值。有兩類INTERVAL： 

INTERVAL YEAR(year_precision) TO MONTH

INTERVAL DAY(day_precision) TO SECOND(fractional_seconds_precision)

year_precision                    是YEAR字段的精度，取值范圍0—9，默認為2.

fractional_seconds_precision 是小數秒的精度，取值范圍0—9，默認為6. 

day_precision                    是DAY字段的最大值（取值范圍0—9，默認為2. ）

l=eg:  

1、使用年到月的間隔

create table warranty(

  prod_id number,產品編號

  warranty_tiem interval year(3) to month       --擔保時間,year(3)-年的部分最少有三位

);

注意間隔類型的字面量寫法：ansi語法

insert into warranty values(123,interval '8' month);             --

SQL 錯誤: ORA01873: 間隔的前導精度太小

insert into warranty values(456,interval '200' year);      --必須指定年的精度有3位

insert into warranty values(456,interval '200' year(3));   --正確寫法

注意間隔類型的字面量寫法,oracle簡單語法：'年-月'

insert into warranty values(789,'200-11');   200年 零 11個月簡單寫法

2、使用天到秒的間隔

create table lab(

  exp_id number,

  test_time interval day to second  --day不指定精度默認位2 秒精度默認為6

);

實驗123的測試時間是90天一次

insert into lab values(123,'90 00:00:00');

insert into lab values(456, interval '06 03:30:16' day to second);

select * from lab;

間隔類型的使用常用于日期的加減運算中

其它日期時間函數

1.EXTRACT 函數:從給定的日期中抽取出特定的部分

extract(.... from .....)

SELECT  EXTRACT ([YEAR] [MONTH][DAY] 

                  [HOUR] [MINUTE][SECOND]

 FROM [datetime_value_expression] |

   [interval_value_expression]);

例如:

select sysdate,extract(year from sysdate)

  from dual;

查詢雇員入職的月份

select hire_date,extract(month from hire_date)

  from employees;

2、tz_offset()

將命名地區形式的時區轉換成時區的絕對偏移量

select tz_offset('Asia/Shanghai'),

       tz_offset('Canada/Yukon')

  from dual;     

要得到合法時區名的列表，可以查詢V$TIMEZONE_NAMES動態性能視圖。

SELECT * FROM V$TIMEZONE_NAMES;

3、to_timestamp(char,'fmt')

將字符串以給定的日期格式模型轉換成時間戳

select to_timestamp('20071010 15:25:00','YYYYMMDD HH24:MI:SS')

  from dual;

4、to_yminterval('year-month')

將字符串轉成成年到月的間隔

將雇員的入職日期加上1年2個月

select hire_date,

      hire_date + to_yminterval('01-02')

 from employees;

5、to_dsinterval('day hh:mi:ss')

將字符串轉成成天到秒的間隔 

將雇員的入職日期加上100天10個小時

select to_char(hire_date,'YYYYMMDD HH24:MI:SS'),

      to_char(hire_date + to_dsinterval('100 10:00:00'),

              'YYYYMMDD HH24:MI:SS')

 from employees;