oracledatetime精度,ORACLE ⽇期时间数据类型与时间间隔数
据类型讲解
23.1 概述
数据类型
说明
DATE
⽇期时间型，精确到秒
TIMESTAMP(fractional_seconds_precision)
时间精确到秒后⾯fractional_seconds_precision位，默认6位，最多9位
TIMESTAMP(fractional_seconds_precision)
WITH TIME ZONE
带时区信息，时间精确到秒后⾯fractional_seconds_precision位，默认6位，最多9位
TIMESTAMP(fractional_seconds_precision)
WITH LOCAL TIME ZONE
带时区信息，时间精确到秒后⾯fractional_seconds_precision位，默认6位，最多9位，但是你查询出来的是经过转换成会话所在时区的时间，是个神奇的字段哦
INTERVAL YEAR(year_precision) TO
MONTH
存储⼀个时间间隔，year_precision说明年的整数位数，最多9位，默认2位。
INTERVAL DAY (day_precision) TO
SECOND(fractional_seconds_precision)
存储⼀个时间间隔，day_precision说明天的整数位数，最多9位，默认2位。fractional_seconds_precision说明秒后⾯⼩数位数，默认6位，最多9位。
⼀ 般将前四种数据类型DATE, TIMESTAMP, TIMESTAMP
WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE
⼀般称之为⽇期时间数据类型(datetimes)，⽽将INTERVAL YEAR TO MONTH and INTERVAL DAY
TO SECOND 这两种数据类型称之为时间间隔数据类型(intervals).
23.2 ⼩技巧：不⽤TO_DATE来输⼊或⽐较不带时分秒的⽇期
对于不带时分秒的⽇期，可⽤使⽤DATE这种⽅法，使⽤这种⽅法是后⾯的⽇期需要按照YYYY-MM-DD的格式，如下
insert into test_ly(a) values(DATE
'2008-08-10')
如果是输⼊当天的0时0分0秒，使⽤
insert into test_ly(mydate)
values(trunc(sysdate))
DATE⽅法还可以⽤于⽐较⽇期
select * from BOM_CALENDAR_DATES where
calendar_code='HELS_CAL' AND ALENDAR_DATE>=DATE
'2009-01-01' ORDER BY CALENDAR_DATE
如何查询某天的数据？
select * from test_ly where
trunc(mydate)=date '2009-11-03'
对
于格式是参数NLS_DATE_FORMAT的字符串(具体使⽤的哪种可以通过 select * from
nls_session_parameters where parameter='NLS_DATE_FORMAT';
查看，只是可惜的是这个参数⼀般没有修改成为符合国⼈习惯的YYYY-MM-DD格式)，还可以直接使⽤单引号将⽇期包括起来代表⽇期，⽐如：
insert into test_ly(mydate)
values('01-1⽉-09')
⽐如我使⽤alter session set
NLS_DATE_FORMAT='YYYY-MM-DD' 修改后
然后就这样可以插⼊⽇期了
insert into test_ly(mydate)
values('2009-01-01')
23.3 某天星期⼏？
今天星期⼏？
select to_char(sysdate,'DAY',
'NLS_DATE_LANGUAGE = ''SIMPLIFIED CHINESE''') from dual;
某天星期⼏？
select to_char(DATE
'2009-11-07','DAY', 'NLS_DATE_LANGUAGE = ''SIMPLIFIED CHINESE''')
from dual;
select
to_char(HIREDATE,'DAY','NLS_DATE_LANGUAGE = ''SIMPLIFIED
CHINESE''') as xingqi,HIREDATE from EMP;
如果当前会话的NLS_DATE_LANGUAGE参数(这个参数值可以通过动态视图nls_session_parameters查询到
)为SIMPLIFIED CHINESE，也可简单点：
select to_char(DATE
'2009-11-07','DAY') from dual;
23.4 获取年，⽉，⽇，时，分，秒
23.4.1 获取年、⽉、⽇
对于4种⽇期时间型数据，获取年⽉⽇的⽅法都是⼀样的，使⽤EXTRACT函数。
select mydate,EXTRACT(YEAR FROM
mydate) as date_nian,EXTRACT(MONTH FROM mydate) AS
date_yue,EXTRACT(DAY FROM mydate) AS date_ri,
mytimestamp,EXTRACT(YEAR FROM mytimestamp) as ts_nian,EXTRACT(MONTH
FROM mytimestamp) AS ts_yue,EXTRACT(DAY FROM mytimestamp) AS tm_ri
FROM test_ly;
23.4.2 TIMESTAMP类型字段获取时分秒
获取的是记录本⾝的时分秒，与⽤户会话所处时区没有关系，注意，⼩时返回的是24⼩时制时间，秒钟会返回指定精度，默认是6位⼩数。
select mytimestamp,EXTRACT(HOUR FROM
oracle中trunc函数用法
mytimestamp) as xiaoshi,EXTRACT(MINUTE FROM mytimestamp) as
fenzhong,EXTRACT(SECOND FROM mytimestamp) as miao FROM test_ly;
23.4.3 TIMESTAMP WITH TIME
ZONE类型字段获取时分秒
获
取的是这个字段所记载的时区的分秒，但是获取的⼩时数却是时间标准时间(UTC)的⼩时
数，与⽤户会话所处时区没有关系。注意，⼩时返回的是24⼩时制时间，秒钟会返回指定精度，默认是6位⼩数。解释：当时记载的是东8区(北京时间所在时
区)的下午1点，但是取得的⼩时却是5，也就是13-8=5，换算成了世界标准时间。
select mytswtm,EXTRACT(HOUR FROM
mytswtm) as xiaoshi,EXTRACT(MINUTE FROM mytswtm) as
fenzhong,EXTRACT(SECOND FROM mytswtm) as miao FROM
test_ly;
23.4.4 TIMESTAMP WITH LOCAL TIME
ZONE类型字段获取时分秒
这
种数据类型从某种程度上是欺骗你的，也是⾃适应的，它显⽰的字段值以及获取的时分秒与会话
所在的时区有关，会⾃动转化成会话所在时区的时间。你看，我当时插⼊的记录是东8区下午1点多，我⽤命令将⾃⼰会话的时区设置为东9区(ALTER
SESSION SET TIME_ZONE = '+9:00';)，然后查看数据，会发现⼩时数加了⼀个⼩时。
select mytswltm,EXTRACT(HOUR FROM
mytswltm) as xiaoshi,EXTRACT(MINUTE FROM mytswltm) as
fenzhong,EXTRACT(SECOND FROM mytswltm) as miao FROM test_ly;
23.4.5 DATE类型数据获取时分秒
系
统看起来并没有直接提供获取DATE数据类型时分秒的⽅⾯，前⾯⽤到的EXTRACT函数
对DATE数据类型来获取时分秒并不使⽤，看来只有另想办法。我想到的办法是将DATE类型转换为TIMESTAMP数据类型再取时分秒，事实证明可⾏，
或许还有更好的办法我不知道吧。
select mydate,EXTRACT(HOUR FROM
TO_TIMESTAMP(TO_CHAR(mydate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD
HH24:MI:SS')) as xiaoshi,EXTRACT(MINUTE FROM
TO_TIMESTAMP(TO_CHAR(mydate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD
HH24:MI:SS')) as fenzhong,EXTRACT(SECOND FROM
TO_TIMESTAMP(TO_CHAR(mydate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD
HH24:MI:SS')) as miao FROM test_ly;
23.5⽇期和TIMESTAMP类型之间的转换
将当前⽇期时间转换为TIMESTAMP使⽤SYSTIMESTAMP函数
SELECT SYSTIMESTAMP FROM dual;
将TIMESTAMP数据类型转换为某个时区的时间可以使⽤FROM_TZ函数
⽐如将2003-11-20
19:30:00这个时间转换为东8区时间：
SELECT FROM_TZ(TIMESTAMP '2003-11-20
19:30:00', '+8:00') FROM dual;
23.6查看、修改时区
23.6.1 当前会话所处时区
select SESSIONTIMEZONE from dual
23.6.2 数据库所处时区
SELECT DBTIMEZONE from dual
23.6.3更改当前会话所处时区
⽐如更改当前会话所处时区为东9区
ALTER SESSION SET TIME_ZONE =
'+9:00';
23.6.4 如何查看TIMESTAMP WITH TIME
ZONE数据类型存储的是那个时区？
还是使⽤EXTRACT函数，可以看出，分别使⽤的是东8区和东9区。
select mytswtm,extract(TIMEZONE_HOUR
FROM mytswtm) AS shiqu from test_ly
23.6.5 如何查看总共有哪些时区地？
SELECT * FROM V$TIMEZONE_NAMES
23.7 DATE 和TIMESTAMP类型的区别
⾸先⼀个区别便是，DATE数据类型精确到秒，⽽其他三种(TIMESTAMP,
TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME
ZONE)最多可以精确到秒后⾯的第9位⼩数，其他三种是DATE类型的扩展。
做试验说明更多：
create table test_ly(
mydate date,
mytimestamp TIMESTAMP(6),
mytswtm TIMESTAMP(6) WITH TIME ZONE,
mytswltm TIMESTAMP(6) WITH LOCAL TIME ZONE,
myiytm INTERVAL YEAR(2) TO MONTH,
myidts INTERVAL DAY(6) TO SECOND(6)
)
插
⼊⼀⾏记录⽐较，注意LOCALTIMESTAMP返回的是会话当前时区的当前时间的
TIMESTAMP类型的值，不带时区信息，⽽CURRENT_TIMESTAMP返回的是会话当前时区的当前时间并带有当前会话时区的TIMESTAMP WITH TIME ZONE类型的值。具体区别如下表：
Table 10-1. Comparison of functions that return current date and
time
Function
Time zone
Datatype returned
CURRENT_DATE
Session
DATE
CURRENT_TIMESTAMP
Session
TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP
Session
TIMESTAMP