Oracle Database 11g SQL 开发指南学习笔记：日期、时间的存储和处理

1、日期、时间

--1.修改日期的语句
alter session set nls_date_language ='american';

--2.修改日期格式
alter session set nls_date_format ='dd-mon-yyyy';


--3.插入日期数据
insert into customers(customer_id,first_name,last_name,dob,phone)
values(6,'Fred','Brown','05-FEB-1968','800-555-1215');


--按照ANSI标准日期格式来插入日期，可以在非oracle数据库上运行
insert into customers(customer_id,first_name,last_name,dob,phone)
values(8,'Steven','Purple',date '1972-10-25','800-555-1215');


select *
from customers;


--4.日期的转换
--把时间值转化为字符串
select customer_id,
       to_char(dob,'month dd,yyyy')
from customers;

select customer_id,
       to_char(dob,'MONTH DD,YYYY, HH24:MI:SS')
from customers;

alter session set nls_date_language ='simplified chinese';

select to_char(to_date('05-2月-1968'),'MONTH,DD,YYYY')
from dual;


--把字符串转化为时间值 
select to_date('7月 4,2007','month dd,yyyy'),
       to_date('7.4.07','mm.dd.yy'),
       
       to_date('05-6月-07 18:36:25','dd-mon-yy HH24:MI:SS'),
       to_date('05-6-11 19:20:56','dd-mm-yy HH24:MI:SS'),
       
       to_char(to_date('06-11月-13 21:26:59'，'dd-mon-yy HH24:MI:SS'),'HH24:MI:SS')
from dual;


--5.时间值函数
--加上n个月
select add_months('05-3月-2012',12),
       add_months('05-3月-2013',-2)
from dual;


--两个日期之间相差的月数，参数1-参数2
select months_between('25-3月-2008','25-3月-2007'), --返回正的整数
       
       months_between('25-3月-2007','01-3月-2008')  --返回负的小数
from dual;


--计算某个日期的下一个day的日期
alter session set nls_date_language = 'simplified chinese';

select next_day('04-9月-2013','星期三') 
from dual;


--注意，必须要修改会话的日期语言，否则会报错
alter session set nls_date_language = 'american';

select next_day('05-jan-2012','wednesday')
from dual;


--取得日期所在月的最后一天
select last_day('05-3月-12')
from dual;


--返回数据库服务器的操作系统中设置的当前时间值
select sysdate
from dual;


--日期时间的四舍五入,粒度可以是：年，月，日，时，分，秒

select round('23-9月 -2013','YYYY')  --这样会报错，因为'23-9月-2013'不是一个日期值
from dual;

--会四舍五入到2014年
select round(to_date('23-9月 -2013','dd-mon-yyyy'),'YYYY')  --必须要这样
from dual;

--会四舍五入到2013-06-01，因为23号已经超过半个月了
select round(to_date('23-5月 -2013','dd-mon-yyyy'),'mm')  
from dual;

--会四舍五入：23-5月 -2013 23:00:00，因为49分已经超过半小时了。
--这里必须要用to_char，否则只会显示年月日，不会显示小时分钟秒
select TO_CHAR(round(to_date('23-5月 -2013 22:49:30','dd-mon-yyyy HH24:MI:SS'),'HH24'),
               'dd-mon-yyyy HH24:MI:SS')
from dual;


--日期时间的截取,不会四舍五入，粒度可以是：年，月，日，时，分，秒
--01-1月 -2013
select trunc(to_date('23-9月 -2013','dd-mon-yyyy'),'YYYY')  --必须要这样
from dual;

--01-5月 -2013
select trunc(to_date('23-5月 -2013','dd-mon-yyyy'),'mm')  
from dual;

--23-5月 -2013 22:00:00
--这里必须要用to_char，否则只会显示年月日，不会显示小时分钟秒
select TO_CHAR(trunc(to_date('23-5月 -2013 22:49:30','dd-mon-yyyy HH24:MI:SS'),'HH24'),
               'dd-mon-yyyy HH24:MI:SS')
from dual;

 

2、时区

/*
utc时间 + 时差 = 本地时间
*/

--1.数据库的时区就是数据库时区
--数据库时区是由数据库参数time_zone控制，可以在spfile.ora和init.ora中修改time_zone参数
--也可以通过alter database set time_zone来修改。

--返回+00:00，说明数据库使用操作系统设置的时区。
select dbtimezone
from dual;

--取得数据库时区的日期
select sysdate
from dual;


--2.数据库会话的时区就是会话时区,可以通过alter session set time_zone 来修改，
--可以设置为local也就是操作系统所用的时区，或者dbtimezone也就是数据库时区
select sessiontimezone
from dual;


--查看会话时区中的当前日期
select current_date
from dual;


--3.获取时区的差值,与utc时间的差值
select tz_offset('Asia/Shanghai'),   --- +08:00  
       tz_offset('PST')              --- -07:00
from dual;


--4.获取时区名
select *
from v$timezone_names
where tzname in ('PST','EST','Asia/Shanghai')
order by tzabbrev;


--5.将时间值从一个时区转换为另一个时区
select new_time(to_date('25-9月-2013 19:45','dd-mon-yyyy HH24:MI:ss'),
                'pst',
                'est'),  
      
       --要显示时间值，必须像要用to_char函数，返回：25-9月 -2013 22:45:00
       to_char（new_time(to_date('25-9月-2013 19:45','dd-mon-yyyy HH24:MI:ss'),
                                'pst',
                                'est'),
                         'dd-mon-yyyy HH24:MI:SS')                
from dual;

3、时间戳

/*
Oracle 9i数据库引入了一个新的特性：时间戳。可以用来存储世纪、4位年、月、日、时、分、秒。
与date类型相比：可以存储秒的小数位、可以存储时区。
*/
--1.timestamp类型
create table t_timestamp(
  vid integer,
  vvv varchar2(20),
  made_on timestamp(4)  --秒的精度为4
);


--由于只能存放4位，而这里有9位，所以会四舍五入
insert into t_timestamp 
values(1,'abc',timestamp '2013-09-25 16:08:19.123456789');


--显示为 25-9月 -13 04.08.19.123500000 下午
select *
from t_timestamp;


--2.timestamp with time zone类型
create table t_timestamp_with_time_zone(
  vid integer,
  vv varchar2(20),
  made_on timestamp(4) with time zone   --带有时区信息
);

--必须要加上timestamp，日期格式是标准的ANSI格式，否则会报错
insert into t_timestamp_with_time_zone
values(1,'abc',timestamp '2013-09-25 16:08:19.123456789 +08:00');

insert into t_timestamp_with_time_zone
values(1,'abc',timestamp '2013-09-25 16:08:19.123456789 PST');


--插入的数据时区是什么，返回的就是什么
--返回 25-9月 -13 04.08.19.123500000 下午 +08:00
--返回 25-9月 -13 04.08.19.123500000 下午 PST
select *
from t_timestamp_with_time_zone;


--3.timestamp with local time zone类型
--存储时：把时间戳转化为，数据库的时区设置。
--查询时：规格化为当前会话中的时区设置，格式中不包含时区。
create table timestamp_with_local_time_zone(
  vid integer,
  vv varchar2(20),
  made_on timestamp(4) with local time zone
);

--插入的这个时间是est时间，转化为utc时间就是'2013-09-25 21:08:19'
insert into timestamp_with_local_time_zone
values(1,'abc',timestamp '2013-09-25 16:08:19 est'); --est是-05:00


-- '2013-09-25 21:08:19'再加上+8:00就是 26-9月 -2013 05:08:19 ASIA/SHANGHAI
select to_char(made_on,'DD-MON-YYYY HH24:MI:SS TZR')
from timestamp_with_local_time_zone;


select dbtimezone,        -- +00:00
       sessiontimezone,   -- Asia/Shanghai
       tz_offset(sessiontimezone) -- +08:00 
from dual;


--修改为est时区
alter session set time_zone = 'est'

-- 25-9月 -2013 16:08:19 EST
--从以上可以知道，在存储数据时按照数据库时区存储，也就是在字段中不保留时区的信息
--在查询时，再把按数据库时区存储的日期，按照会话时区进行转换显示
--所以with local time zone与with time zone相比，少了时区信息，因为总是在当前的时区里
select to_char(made_on,'DD-MON-YYYY HH24:MI:SS TZR')
from timestamp_with_local_time_zone;


alter session set time_zone = 'Asia/Shanghai';



--4.时间戳函数

select current_timestamp,  --with time zone类型，当前的日期时间和会话时区
       
       systimestamp,       --with time zone类型，数据库日期时间和数据库时区
       
       localtimestamp      --with local time zone类型，会话的当前日期时间
from dual;


select
       --把字符串转化为timestamp类型：25-9月 -13 01.16.31.123400000 上午
       to_timestamp('2013-09-25 01:16:31.1234','yyyy-mm-dd hh24:mi:ss.ff'),
       
       --把字符串转化为with time zone类型：25-9月 -13 01.16.31.123400000 上午 PST
       to_timestamp_tz('2013-09-25 01:16:31.1234 pst','yyyy-mm-dd hh24:mi:ss.ff tzr'),
       
       --把字符串转化为timestamp with local time zone类型：25-9月 -13 12.00.00.000000000 上午
       cast（'25-9月-2013' as timestamp with local time zone）,
       
       --把timestamp转化为timestamp with time zone类型： 25-9月 -13 01.16.31.123400000 上午 +08:00
       from_tz(timestamp '2013-09-25 01:16:31.1234','+8:00')
from dual;


--把timestamp with time zone转化为timestamp类型的utc日期和时间、时区
select 
       -- 26-9月 -13 12.34.50.000000000 上午，其实是26号早上0点
       sys_extract_utc(timestamp '2013-9-25 19:34:50 est'),
       
       -- 2013-09-26 00:34:50
       to_char(sys_extract_utc(timestamp '2013-9-25 19:34:50 est'),'yyyy-mm-dd hh24:mi:ss')
from dual;


--从日期中提取
select --必须要调用to_date
       extract(year from to_date('2013-09-25 20:12:50','yyyy-mm-dd hh24:mi:ss')),       
       extract(month from to_date('2013-09-25 20:12:50','yyyy-mm-dd hh24:mi:ss')),       
       extract(day from to_date('2013-09-25 20:12:50','yyyy-mm-dd hh24:mi:ss')),
       
       --调用to_timestamp，不能调用to_date，否则会报错
       extract(hour from to_timestamp('2013-09-25 20:12:50','yyyy-mm-dd hh24:mi:ss')),     
       extract(minute from to_timestamp('2013-09-25 20:12:50','yyyy-mm-dd hh24:mi:ss')),       
       extract(second from to_timestamp('2013-09-25 20:12:50','yyyy-mm-dd hh24:mi:ss')),
       
       --调用to_timestamp_tz
       extract(timezone_hour from to_timestamp_tz('2013-09-25 20:12:50 +8:00','yyyy-mm-dd hh24:mi:ss tzh:tzm')),
       extract(timezone_minute from to_timestamp_tz('2013-09-25 20:12:50 +8:00','yyyy-mm-dd hh24:mi:ss tzh:tzm')),
       extract(timezone_region from to_timestamp_tz('2013-09-25 20:12:50 EST','yyyy-mm-dd hh24:mi:ss tzr')),
       extract(timezone_abbr from to_timestamp_tz('2013-09-25 20:12:50 est','yyyy-mm-dd hh24:mi:ss tzr'))
from dual;

4、时间间隔

/*
oracle 9i引入了一个新特性，可以用来存储时间间隔：

interval year（x） to month：x是精度，是0-9之间的整数，默认为2，指定年的精度。
interval year to month的字面值格式：interval '[+|-] [y] [-m]' [year(精度)] [to month]

interval day(x) to second(y)：x是0-9，默认为2，指定天的精度。y是0-9，默认是6，指定秒的小数部分的精度
interval '[+|-] [d] [h:m:s]' [day(precision)]  [to [hour] | [minute] | [second(precision)]]

时间间隔可正，可负。
*/
--1.interval year to month
create table t_interval_year(
  vid int,
  duration interval year(3) to month
);

delete from t_interval_year;

insert into t_interval_year values(1,interval '1' year);     --1年

insert into t_interval_year values(1,interval '123' year(3));       --123年

insert into t_interval_year values(1,interval '-123' year(3));       --负123年

insert into t_interval_year values(1,interval '11' month);   --11个月

insert into t_interval_year values(1,interval '14' month);   --14个月，也就是1年2个月

insert into t_interval_year values(1,interval '-14' month);   --负14个月，也就是负1年2个月

insert into t_interval_year values(1,interval '1-6' year to month); --1年6个月

insert into t_interval_year values(1,interval '0-8' year to month); --8个月

insert into t_interval_year values(1,interval '-1-5' year to month); --负1年5个月

insert into t_interval_year values(1,interval '-0-8' year to  month);   --负8个月

--由于是4位，超过了精度3位，所以报错了
insert into t_interval_year values(1,interval '1234' year(3));  

select *
from t_interval_year;


--2.interval day to second
create table t_day_second(
  vid int,
  duration interval day(3) to second(4)
);

insert into t_day_second values(1,interval '3' day);

insert into t_day_second values(1,interval '2' hour);

insert into t_day_second values(1,interval '25' minute);

insert into t_day_second values(1,interval '45' second);

insert into t_day_second values(1,interval '3 2' day to hour);

insert into t_day_second values(1,interval '3 2:25' day to minute);

insert into t_day_second values(1,interval '3 2:25:45' day to second);

--虽然表定义中day的精度是3，但day的默认精度是2，所以这里的day必须带精度
insert into t_day_second values(1,interval '123 2:25:45.12' day(3) to second);

insert into t_day_second values(1,interval '3 2:00:45' day to second);   --3天2小时0分45秒

insert into t_day_second values(1,interval '-3 2:00:45' day to second);  --负3天2小时0分45秒

--day的精度太低，会报错
insert into t_day_second values(1,interval '1234 2:25:45' day(3) to second);

--虽然在列的定义中day的精度是4，但是默认的精度是2，所以i必须指定day(3)
insert into t_day_second values(1,interval '123 2:25:45.123' day(3) to second); 



--3.时间间隔的函数

--把数字转化为时间间隔
select --把数据转化为interval day to second时间间隔类型
       numtodsinterval(1.5,'day'),   -- 1 12:0:0.0       
       numtodsinterval(3.25,'hour'), -- 0 3:15:0.0       
       numtodsinterval(5,'minute'),  -- 0 0:5:0.0       
       numtodsinterval(10.123456789,'second'),  -- 10.123456789
       
       --把数据转化为interval year to month时间间隔类型
       numtoyminterval(1.5,'year'),     -- 1-6
       numtoyminterval(3.25,'month'),   -- 0-3 由于interval year to month无法存储0.25，所以四舍五入
       numtoyminterval(3.6,'month')     -- 0-4 同上
from dual;


--把字符串转化为时间间隔类型
select --把字符串转化为interval day to second，可以用两种格式
       to_dsinterval('100 05:00:00'),           -- 100天5小时，SQL格式
       to_dsinterval('P100DT05H'),              -- 100天5小时，ISO格式
       to_dsinterval('P100DT00H05M20.123456S'), -- 100 0:5:20.123456000
       
       --把字符串转化为interval year to month
       TO_YMINTERVAL('100-11'),              -- 100年11个月
       --TO_YMINTERVAL('100-12'),            -- 会报错：无效的月份，也就是月应该小于12
       
       --to_yminterval('0-12'),              -- 会报错：无效的月份，原因同上
       to_yminterval('-0-11')                -- -0-11，负11个月
from dual;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/653220/viewspace-1982360/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/653220/viewspace-1982360/