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

年，月，日，時，分，秒與秒的小數部分

TIMESTAMP WITH TIME ZONE

與TIMESTAMP資料類型相同；還包括：TIMEZONE_HOUR，TIMEZONE_MINUTE或TIMEZONE_REGION

TIMESTAMP WITH LOCAL TIME ZONE

存儲類型與 TIMESTAMP 相似，在使用者送出時間給資料庫的時，該類型會轉換成資料庫的時區來儲存資料，即資料庫儲存的時間是資料庫本地時區，當使用者通路資料庫時 oracle 會自動将該時間轉換成目前用戶端的時間

TIMESTAMP 字段

Datetime  字段

有效值

YEAR

–4712 to 9999 (不包括0年）

MONTH

01 to 12

DAY

01 to 31

HOUR

00 to 23

MINUTE

00 to 59

SECOND

00 to 59.9(N) -- 注：9(N)為精度

TIMEZONE_HOUR

-12 to 14 

TIMEZONE_MINUTE

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;

<a href="https://s1.51cto.com/wyfs02/M00/8F/3F/wKiom1jYq7iSohpYAAAYKbYAM_A405.jpg" target="_blank"></a>

alter table employees modify hire_date timestamp;

<a href="https://s3.51cto.com/wyfs02/M00/8F/3F/wKiom1jYrBbApMmGAAA5RJHsG70584.jpg" target="_blank"></a>

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 YEAR TO MONTH

年、月

INTERVAL DAY TO SECOND

天、小時、分鐘、秒及小數部分

INTERVAL 範圍

INTERVAL範圍

間隔有效值

任何正、負的整數

00 to 11

00 to 59.9(N) –注：9(N)為精度 

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是不明确的，因為他們去過兩次

本文轉自 yuri_cto 51CTO部落格，原文連結：http://blog.51cto.com/laobaiv1/1910754，如需轉載請自行聯系原作者