oracle 日期时间数据类型

来源：互联网发布：网络兼职是真的吗编辑：程序博客网时间：2024/05/07 04:08

1. The datetime data types are DATE and TIMESTAMP;

2. The database stores dates internally as numbers. Dates are stored in

fixed-length fields of7 bytes each, corresponding to century, year, month,

day, hour, minute, andsecond.

例如：2013年6月15日12时10分10秒，那么存储的7字节分别是：20 13 06 15 12 10 10

3. The database displays datesaccording to the specified format model,The standard date format isDD-MON-RR。

4. You can change the default date format at both the instanceand and the session level

HR >selectto_char(HIRE_DATE,'yyyy-mm-dd:hh24:mi:ss') as date1,

to_char(HIRE_DATE,'DD-MON-RR') AS DATE2,

to_char(HIRE_DATE,'DD-MON-yy') AS DATE3

from EMPLOYEES WHERE ROWNUM<=3;;

DATE1 |DATE2 |DATE3

------------------------------|----------|--------------

1987-06-17:00:00:00 |17-6月 -87|17-6月 -87

1989-09-21:00:00:00 |21-9月 -89|21-9月 -89

1993-01-13:00:00:00 |13-1月 -93|13-1月 -93

SYS >grant select any dictionary to hr;

HR >show parameters NLS_DATE_FORMAT;

NAME |TYPE |VALUE

------------------------------------|-----------|------------------------------

nls_date_format |string |DD-MON-RR

HR >ALTER session SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'; ---仅对当前会话生效

(也可以直接修改环境变量，export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS')

HR >select sysdate from dual;

SYSDATE

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

2013-06-14 23:38:24

--注意，cc是直接读取当前年份前两位，所以这样得出的结果并不准确！应该直接用yyyy反映年份：

HR >selectto_char(HIRE_DATE,'cc-yy-mm-dd:hh24:mi:ss') AS DATE3 from EMPLOYEES WHERE ROWNUM<=3;

DATE3

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

20-87-06-17:00:00:00

20-89-09-21:00:00:00

20-93-01-13:00:00:00

5. 注意DD-MON-RR与DD-MON-YY区别：

1. 如果当前年份后两位是00-49

[~]#date 061501302013.20

2013年 06月 15日星期六01:30:20 CST

(1) 插入的年份后两位是00-49:

DD-MON-RR:

HR >alter session setnls_date_format='DD-MON-RR';

HR >insert into test values('23-1月 -23');

HR >select to_char(id,'rrrr') from test;

TO_C

----

2023

DD-MON-YY:

HR >alter session setnls_date_format='DD-MON-YY';

HR >insert into test values('23-1月 -23');

HR >select to_char(id,'YYYY') from test;

TO_C

----

2023

(2) 插入的年份后两位是50-99

DD-MON-RR: ---和当前年份相比，rr日期格式进行了舍处理。

HR >alter session setnls_date_format='DD-MON-RR';

HR >insert into test values('23-1月 -88');

HR >select to_char(id,'YYYY') from test;

TO_C

----

1988

DD-MON-YY： --没有做改变

HR >alter session set nls_date_format='DD-MON-YY';

HR >delete from test;

Elapsed: 00:00:00.01

HR >insert into test values('23-1月 -88');

HR >select to_char(sysdate,'YYYY') from test;

TO_C

----

2013

2. 如果当前年份后两位是50-99：

[~]#date 012311111998.12

1998年 01月 23日星期五 11:11:12 CST

（1）插入的年份后两位是00-49

DD-MON-RR: 可以看到，与当前所在的年份相比，RR日期方式对前两位进行了进位处理（2032）！

HR >show parameter nls_date_format;

NAME |TYPE |VALUE

------------------------------------|-----------|------------------------------

nls_date_format |string |DD-MON-RR

HR >insert into test values('23-1月 -32');

HR >select to_char(id,'yyyy') from test;

TO_C

----

2032

DD-MON-YY：

HR>alter session set nls_date_format='DD-MON-YY'; ---修改为yy日期方式

HR>delete from test;

HR>insert into test values('23-1月 -32');

HR>select to_char(id,'yyyy') from test;

TO_C

----

1932

（2）插入的年份后两位是50-99

DD-MON-RR: ----仍旧看作是1988

alter session set nls_date_format='DD-MON-RR'

HR >delete from test;

HR >insert into test values('23-1月 -88');

HR >select to_char(id,'rrrr') from test;

TO_C

----

1988

DD-MON-YY： --仍旧看作是1988

HR >alter session set nls_date_format='DD-MON-YY';

HR >insert into test values('23-1月 -88');

HR >select to_char(id,'rrrr') from test;

TO_C

----

1988

由此，我们得出结论：DD-MON-YY模式下，插入日期（DD-MON-YY格式）后，得出的年份的前两位就是当前年份的前两位。

对于DD-MON-RR模式，则存在下面规律（这个规律类似四舍五入）：

插入DD-MON-RR日期格式数据后，查看到的日期与当前日期存在如下关系：

如果当前日期的年份后两位是00-49，插入的DD-MON-RR日期格式的年份是50-99，那么实际得出的年份的前两位为当前年份的前两位减1（当前时间2013年，插入的日期RR部分是88，所以得出实际年份1988）

如果当前日期的年份后两位是50-99，插入的DD-MON-RR日期格式的年份是00-49，那么实际得出的年份的前两位是当前年分的前两位加1（当前时间是1988，插入的日期RR部分是23，所以得出的实际年份是2023）

概括为一句话就是：两个年份区间不同（当前年份与插入的年份后两位比较），插入的比当前大则减1（前两位），插入比当前小则加1。

6.OracleDatabase stores time in 24-hour format—HH:MI:SS. If no time

portion is entered, then by default the time in a date field is 00:00:00

A.M.

HR>insert into test values('23-1月 -88');

HR>select to_char(id,'YYYY-mm-dd-hh24:mi:ss') from test;

TO_CHAR(ID,'YYYY-MM

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

2088-01-23-00:00:00

7. In a time-only entry, the date portion defaults to the first day of the

current month.

HR>alter session set nls_date_format='hh:mi:ss';

HR>insert into test values('11:11:11');

HR>select to_char(id,'YYYY-mm-dd-hh:mi:ss') from test;

TO_CHAR(ID,'YYYY-MM

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

2013-06-01-11:11:11

Using Julian Days

A Julian day number is the number of dayssince January 1, 4712 BC.

HR >select to_char(sysdate-1,'j') fromdual;

TO_CHAR

-------

2456458

Return julian days of 2013-01-01

HR >select to_char(to_date('1970-01-01','YYYY-MM-DD'),'j')from dual;

TO_CHAR

-------

2440588

9.TIMESTAMP Data Type

It stores fractional seconds in addition to the information stored in the DATE data type

HR >show parameter nls_timestamp_format;

NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
nls_timestamp_format |string |DD-MON-RR HH.MI.SSXFF AM

HR >alter table test modify id timestamp;

HR >delete from test;

HR >insert into test values(sysdate);

1 row created.

HR >select * from test;

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

15-6月 -13 02.31.21.000000 下午

HR >selectto_char(id,'yyyy-mm-dd:hh:mi:ss,pm') from test;

TO_CHAR(ID,'YYYY-MM-DD:H

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

2013-06-15:02:31:21,下午

HR >select to_char(id,'yyyy-mm-dd:hh24:mi:ss,am') from test;

TO_CHAR(ID,'YYYY-MM-DD:H

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

2013-06-15:14:31:21,下午

TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE参考《Oracle Database SQL Language Reference》

10. 常用日期时间函数：

1）sysdate

HR >select sysdate from dual;

SYSDATE

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

15-6月 -13

2）to_char函数：把日期时间转换为字符串，并按照一定格式显示

HR >select to_char(sysdate,'yyy') from dual; -----显示日期的年份

TO_

---

013

HR >select to_char(sysdate,'year') from dual; ---显示全文拼写的年份

TO_CHAR(SYSDATE,'YEAR')

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

twenty thirteen

HR >select to_char(sysdate,'Q')from dual; --显示月份所在季度

HR >select to_char(sysdate,'j') from dual; --显示julian时间：公元前4712年1月1号以来的天数

TO_CHAR

-------

2456459

HR >select to_char(sysdate,'month') from dual; --显示月份名称

TO_CHA

------

6月

HR >select to_char(sysdate,'ww') from dual; --查看日期是本年中的第几周

HR >select to_char(sysdate,'w') from dual; --查看日期是本月中的的几周

HR >select to_char(sysdate,'ddd') from dual; --查看日期是本年中的第几天

TO_

---

166

HR >select to_char(sysdate,'dd') from dual; --查看日期是本月中的第几天

HR >select to_char(sysdate,'d') from dual; --查看日期是本周中的第几天

HR >select to_char(sysdate,'day') from dual; --显示星期

TO_CHAR(S

---------

星期六

HR >

select sysdate,current_timestamp from dual; ---显示相对日期的当前时间戳

SYSDATE |CURRENT_TIMESTAMP

--------------|------------------------------------------------------------------

15-6月 -13 |15-6月 -13 02.56.36.657814 下午 +08:00

3）

months_between函数

HR >select months_between(sysdate,'15-6月 -11') from dual;---返回两个日期之间的月数

MONTHS_BETWEEN(SYSDATE,'15-6月-11')

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

HR >select months_between(sysdate,'16-6月 -11') from dual;

MONTHS_BETWEEN(SYSDATE,'16-6月-11')

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

23.9879211

4）

add_months函数

HR >select add_months(sysdate,20) from dual; --当前时间基础上增加20个月

ADD_MONTHS(SYS

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

15-2月 -15

11. 实验，返回当前时间的时间戳

1）计算1970年1月1号到现在经过多少天（取整数）

select to_date(to_char(sysdate,'yyyy-mm-dd'))-to_date('1970-01-01','yyyy-mm-dd')from dual

2）计算当前时间距离午夜的时间秒数

selectto_char(sysdate,'hh24')*3600+to_char(sysdate,'mi')*60+to_char(sysdate,'ss')from dual;

3）两者秒数相加：

HR>select (a.days*86400)+b.seconds from

(select to_date(to_char(sysdate,'yyyy-mm-dd'))-to_date('1970-01-01','yyyy-mm-dd')

daysfrom dual) a,

(selectto_char(sysdate,'hh24')*3600+to_char(sysdate,'mi')*60+to_char(sysdate,'ss') seconds from dual) b;

ho date +%s

(A.DAYS*86400)+B.SECONDS

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

1371311006

HR>1371282206

HR>select 1371311006-1371282206 fromdual;

1371311006-1371282206

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

28800

HR>select 28800/60/60 from dual;

28800/60/60

-----------

计算出的时间比实际时间多8个小时，为什么会出现这种情况？