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，如需转载请自行联系原作者