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;