SQL 基礎之時區函數（二十一）

使用數據類型來存儲兩個日期時間值之間的時間差

使用下列的日期時間函數：

– CURRENT_DATE

– CURRENT_TIMESTAMP

– LOCALTIMESTAMP

– DBTIMEZONE

– SESSIONTIMEZONE

– EXTRACT

– TZ_OFFSET

– FROM_TZ

– TO_TIMESTAMP

– TO_YMINTERVAL

– TO_DSINTERVAL

TIME_ZONE 可以設置為：

絕對偏移量

數據庫的時區

OS本地時區

區域名

alter session set time_zone = '-05:00';

alter session set time_zone = dbtimezone;

alter session set time_zone = local;

alter session set time_zone = 'America/New_York';

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

select * from web_orders;

DATE 與 TIMESTAMP的區別

select hire_date from employees;

alter table employees modify hire_date timestamp;

select hire_date from employees;

• CURRENT_DATE:
  

– 從用戶會話返回當前的日期

– 返回的是DATE數據類型

• CURRENT_TIMESTAMP:

– 從用戶會話返回當前的日期和時間

– 返回的是TIMESTAMP WITH TIME ZONE數據類型

• LOCALTIMESTAMP:

– 從用戶會話返回當前的日期和時間

– 返回的是TIMESTAMP數據類型

比較會話的時區的日期和時間

將參數TIME_ZONE設置為–5:00，然后使用SELECT 語句查看每個日期和時間的差異比較。

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

alter session set time_zone = '-5:00';

select sessiontimezone, current_date from dual;

SESSIONTIMEZONE   CURRENT_DATE

--------------------------- --------------------------------------------------

-05:00                            27-3  -2017 01:12:37

select sessiontimezone, current_timestamp from dual;

SESSIONTIMEZONE   CURRENT_TIMESTAMP

--------- ---------------------------------------------------------------------------

-05:00                            27-3  -17 01.13.23.473132  -05:00

select sessiontimezone, localtimestamp from dual;

SESSIONTIMEZONE   LOCALTIMESTAMP

------------ ---------------------------------------------------------------------------

-05:00                           27-3  -17 01.14.06.470998

DBTIMEZONE 和 和 SESSIONTIMEZONE

顯示數據庫時區：

select dbtimezone from dual;

DBTIMEZONE

------------------

+00:00

顯示會話時區：

select sessiontimezone from dual;

INTERVAL 數據類型

INTERVAL 數據類型用于存儲兩個日期的差值。

有兩種類型的間隔:

– Year-month

– Day-time

時間間隔的精度：

– 實際的范圍子集構成的間隔

– 指定的時間間隔

INTERVAL 范圍

INTERVAL YEAR TO MONTH ：示例

create table warranty (prod_id number, warranty_time interval year(3) to month);

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

insert into warranty values (155, interval '200' year(3));

insert into warranty values (678, '200-11');

select * from warranty;

INTERVAL DAY TO SECOND 示例

create table lab ( exp_id number, test_time interval day(2) to second);

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

insert into lab values (56098,

interval '6 03:30:16' day to second);

EXTRACT

從SYSDATE顯示年份：

select extract (year from sysdate) from dual;

顯示MANAGER_ID為100的員工的HIRE_DATE的月份：

select last_name, hire_date, extract (month from hire_date) from employees

where manager_id = 100;

TZ_OFFSET

顯示UTC與‘US/Eastern’ （美國/ 東部）, ‘Canada/Yukon’ （加拿大/ 育空） 和 ‘Europe/London’ （歐洲/ 倫敦） 的時區偏移量

select tz_offset('us/eastern'),

tz_offset('canada/yukon'),

tz_offset('europe/london')

from dual;

FROM_TZ

顯示TIMESTAMP 值 ‘2000-03-28 08:00:00’ 時區為‘Australia/North’（澳大利亞/北）， TIMESTAMP WITH TIME ZONE值。

select from_tz(timestamp '2000-07-12 08:00:00', 'australia/north') from dual;

TO_TIMESTAMP

顯示字符串‘2007-03-06 11:00:00’ 的 TIMESTAMP 值：

select to_timestamp ('2007-03-06 11:00:00','YYYY-MM-DD HH:MI:SS') from dual;

TO_YMINTERVAL

顯示DEPARTMENT_ID為20的員工的雇傭日期1年零2個月后的日期。

select hire_date,hire_date + TO_YMINTERVAL('01-02') AS

HIRE_DATE_YMININTERVAL

from employees where department_id = 20;

TO_DSINTERVAL

顯示全體員工雇傭日期100天零10小時候的日期

select last_name,

TO_CHAR(hire_date, 'mm-dd-yy:hh:mi:ss') hire_date,

TO_CHAR(hire_date +

TO_DSINTERVAL('100 10:00:00'),

'mm-dd-yy:hh:mi:ss') hiredate2

from employees;

夏令時

4月的第一個星期日

– Time jumps from 01:59:59 AM to 03:00:00 AM.

– Values from 02:00:00 AM to 02:59:59 AM are not valid.

– 時間從上午01:59:59跳躍到上午03:00:00 

– 值從上午02:00:00至02:59:59時是無效的

10月的最后一個星期日

– Time jumps from 02:00:00 AM to 01:00:01 AM.

– Values from 01:00:01 AM to 02:00:00 AM are ambiguous

because they are visited twice.

– 時間從上午02:00:00跳躍到上午01:00:01 。

– 值從上午01:00:01至上午02:00:00是不明確的，因為他們去過兩次