时间系列文章目录导航

准备工作：创建一个测试表：

create table DATE_ITEM_STAMP
(
  C1 DATE,
  C2 TIMESTAMP(9)
);

1.date: 最普通的，精确到秒

正确的语法格式：

//SQL:默认date支持年月日
insert into DATE_ITEM_STAMP(c1) values(date'2010-2-12');
//数据库时间存储函数：to_date()
insert into DATE_ITEM_STAMP(c1) values(to_date('2021-06-09','YYYY-MM-DD HH24:MI:SS'));
insert into DATE_ITEM_STAMP(c1) values(to_date('2021-06-09 10:20:30','YYYY-MM-DD HH24:MI:SS'));

错误的语法格式：

--错误：默认支持年月日
insert into DATE_ITEM_STAMP(c1) values(date'2021-06-09 10:20:30');
--错误：字符串必须和patten匹配
insert into DATE_ITEM_STAMP(c1) values(to_date('2021-06-09 10:20:30','YYYY-MM-DD'));

date类型：表示年月日时分秒

⑴其展示格式既可以为：YYYY/MM/DD，也可以为YYYY/MM/DD HH24:MI:SS

⑵其存储格式只有一种：YYYY/MM/DD HH24:MI:SS

⑶即，date类型的值在数据库图形界面中可以有不同的显示格式(具体的显示格式由工具设置决定)，但其实际储存的格式只有一种

⑷当你只存年月日时，date实际存储的是：年月日0时0分0秒，在plsql中，其展示格式为：YYYY/MM/DD

这是ORACLE最常用的日期类型，它可以保存日期和时间，常用日期处理都可以采用这种类型。DATE表示的日期范围可以是公元前4712年1月1日至公元9999年12月31日，date类型在数据库中的存储固定为7个字节，格式为： 第1字节：世纪+100 第2字节：年 第3字节：月 第4字节：天 第5字节：小时+1 第6字节：分+1 第7字节：秒+1

2.timestamp：精确到毫秒

错误语法格式：

--错误：默认支持年月日 小时 分 秒
insert into DATE_ITEM_STAMP(c2) values(timestamp'2021-06-09');

正确的语法格式：

--正确：
--默认默认支持年月日 小时 分 秒
insert into DATE_ITEM_STAMP(c2) values(timestamp'2021-06-09 10:20:30');
insert into DATE_ITEM_STAMP(c2) values(to_timestamp('2021-06-08','YYYY-MM-DD HH24:MI:SS'));
insert into DATE_ITEM_STAMP(c2) values(to_timestamp('2021-06-07 10:20:30','YYYY-MM-DD HH24:MI:SS'));
insert into DATE_ITEM_STAMP(c2) values(to_timestamp('2021-06-06 10:20:30.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into DATE_ITEM_STAMP(c2) values(to_timestamp('2021-06-05 10:20:30.123456789','YYYY-MM-DD HH24:MI:SS.FF9'));

这也是ORACLE常用的日期类型，它与date的区别是不仅可以保存日期和时间，还能保存小数秒，小数位数可以指定为0-9，默认为6位，所以最高精度可以到ns(纳秒)，数据库内部用7或者11个字节存储，如果精度为0，则用7字节存储，与date类型功能相同，如果精度大于0则用11字节存储。

timestamp with local timezone：写入的时间可以带时区，但是统一转换为dbtimezone，读取的时候转换为sessiontimezone，不带时区; to_timestamp_tz timestamp with timezone：写入的时间可以带时区，数据库也是已写的时区存储，读取的时候也可以读出写入的时区。

3.DATE与TIMESTAMP类型内部存储验证

写入数据：

insert into DATE_ITEM_STAMP(c1,c2) values(
       to_date('2021-06-09','YYYY-MM-DD HH24:MI:SS'),
       to_timestamp('2021-06-09 10:20:30','YYYY-MM-DD HH24:MI:SS.FF9')
);
insert into DATE_ITEM_STAMP(c1,c2) values(
       to_date('2021-06-08 10:20:30','YYYY-MM-DD HH24:MI:SS'),
       to_timestamp('2021-06-08 10:20:30.123456','YYYY-MM-DD HH24:MI:SS.FF6')
);
insert into DATE_ITEM_STAMP(c1,c2) values(
       to_date('2021-06-07 10:20:30','YYYY-MM-DD HH24:MI:SS'),
       to_timestamp('2021-06-07 10:20:30.123456789','YYYY-MM-DD HH24:MI:SS.FF9')
);
--查询存储结构
select c1,dump(c1,16) c1_d,c2,dump(c2,16) c2_d from DATE_ITEM_STAMP;

可以证明上面的结论：DATA 7个字节，TIMESTAMP7到11个字节存储，一个汉字在Oracle数据库里占多少字节跟数据库的字符集有关，UTF８时，长度为三。

4.日期的常用操作

4.1 取当前时间：

-- oracle sql:
-- sysdate--返回当前系统日期和时间，精确到秒
-- systimestamp--返回当前系统日期和时间，精确到毫秒
SELECT SYSDATE "当前时间(DATE)",SYSTIMESTAMP "当前时间(TIMESTAMP)" from dual;

结果：

4.2 时间运算

-- oracle sql:
-- 日期型数据可以与数值加减得到新的日期，加减数值单位为天
-- sysdate+1--取明天的当前时间
-- sysdate-1/24--取当前时间的前一个小时
SELECT sysdate-1 "增加减少天数",SYSTIMESTAMP-1/24 "增加减少小时" from dual;

结果：

4.3 如何求两个日期的间隔时间

-- oracle sql:
-- 可以直接把两个日期相减，返回的单位为天，小时及分秒会换算成小数
select date'2021-06-11'-SYSDATE "日期差" from dual;

结果：

4.4 如何将日期转字符

-- oracle sql:
select to_char(sysdate,'YYYY-MM-DD') "日期->字符串(YYYY-MM-DD)", to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "格式日期(YYYY-MM-DD HH24:MI:SS)" from dual;

结果：

4.5 如何将字符转日期

-- oracle sql:
SELECT to_date('2021-06-09 15:01:54','YYYY-MM-DD HH24:MI:SS') "字符串转DATE日期",to_timestamp('2021-06-09 11:00:00.123456','YYYY-MM-DD HH:MI:SS.FF6') "字符串转TIMESTAMP日期" from dual;