oracle date日期类型 精析

 

一、date

1.date、sysdate格式说明

　　展示

　　date类型，展示格式既可以为：YYYY/MM/DD，也可以为YYYY/MM/DD HH24:MI:SS；

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

　　展示格式一：

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

　　将其转换成YYYY/MM/DD HH24:MI:SS时，返回的是YYYY/MM/DD 00:00:00。

　　展示格式二：

　　当date实际存储的是年月日时分秒时，在plsql中，其展示格式为：YYYY/MM/DD HH24:MI:SS；

　　系统时间sysdate，在plsql中，其展示格式为：YYYY/MM/DD HH24:MI:SS。

　　说明：与插入时的格式无关

　　证实

　　TESTDATE表结构

　　插入sql的日期格式及结果展示

　　补充：

　　当插入年月日时分秒且时分秒为：00:00:00，在plsql中是不显示时分秒的。

　　要想显示出来，需用to_char()

　　若是还是对推荐有疑问，请看下面的 4.2 查询指定时间段的数据

2.日期转字符串

　　TO_CHAR(date,format)

　　方式一：只带一个参数

select to_char(t.schedule_date) from CONSULT_SCHEDULE t
--28-APR-17　　

　　方式二：带2个参数

　　转换格式不区分大小写；

　　小时格式化：不带24时，默认为12小时制，24小时格式转换时需要指明；

　　分钟格式化：可以使用mi或mm，建议使用MI。

　　说明：

　　格式化成yyyy年mm月dd日

--年月日
--需要填充3个空格，不然‘日’显示不出来
SELECT TO_CHAR(SYSDATE,'yyyy"年"MM"月"dd"日"   ') 系统日期 FROM DUAL

　　

　　注意：

　　年月日需加上双引号；

　　别名不能加单引号、双引号。

3.字符串转日期

　　方法一：

　　TO_DATE('dateStr', format)，规则如下：

　　转换格式不区分大小写；

　　小时格式化：不带24时，默认为12小时制，24小时格式转换时需要指明；

　　分钟格式化：必须使用mi，不能使用mm，否则会报错；

　　dateStr的长度<=format的长度，都可以实现类型转换：

　　方式一：标准格式

　　年月日

　　说明：

　　年月日时分秒

　　方式二：字符串只到分，会自动添加上:00秒。

　　

　　方式三：字符串也是只到日

　　方法二：

　　DATE'YYYY-MM-DD'，并且只能是这一种格式。

 

　　另外，TO_DATE(date)，该函数也可以只传一个参数，但是这个参数必须是date类型，

表示的含义是：将任何格式的日期类均转换成YYYY/MM/DD的格式。

 

4.根据日期查询数据

　　说明：这里着重介绍的是日期字段带有时分秒的

　　4.1 查询某一天的数据

　　测试环境：

　　VIRTUAL_CARD表中共有5条记录；

　　字段REGTIME的字段类型为date。

　　

　　情景模拟：查询2018年7月18日的数据记录

　　格式要求：日期转变成YYYY-MM-DD HH24:MI:SS格式的字符串

　　方式一：查询条件使用字符串实现

--查询条件按字符串进行
SELECT TO_CHAR(T.REGTIME, 'YYYY-MM-DD HH24:MI:SS') REGTIME
  FROM VIRTUAL_CARD T
 WHERE TO_CHAR(T.REGTIME, 'yyyy-mm-dd') = '2018-07-18'

　　方式二：查询条件使用日期实现

　　方法一：TRUNC()

--查询条件按日期进行1
SELECT TO_CHAR(T.REGTIME, 'YYYY-MM-DD HH24:MI:SS') REGTIME
  FROM VIRTUAL_CARD T
 WHERE TRUNC(T.REGTIME) = TO_DATE('2018-07-18', 'YYYY-MM-DD')

　　方法二：TO_DATE()

--查询条件按日期进行2
SELECT TO_CHAR(T.REGTIME, 'YYYY-MM-DD HH24:MI:SS') REGTIME
  FROM VIRTUAL_CARD T
 WHERE TO_DATE(T.REGTIME) = TO_DATE('2018-07-18', 'YYYY-MM-DD')

　　方式三：利用TO_DATE()特性

SELECT TO_CHAR(T.REGTIME, 'YYYY-MM-DD HH24:MI:SS') REGTIME
  FROM VIRTUAL_CARD T
 WHERE T.REGTIME >= TO_DATE('2018-07-18', 'YYYY-MM-DD')
   AND T.REGTIME < TO_DATE('2018-07-19', 'YYYY-MM-DD')　　

　　方法四：TO_CHAR()-->TO_DATE() 不建议使用

SELECT TO_CHAR(T.REGTIME, 'YYYY-MM-DD HH24:MI:SS') REGTIME
  FROM VIRTUAL_CARD T
 WHERE TO_DATE(TO_CHAR(T.REGTIME, 'YYYY-MM-DD'), 'YYYY-MM-DD') =
       TO_DATE('2018-07-18', 'YYYY-MM-DD')

　　查询结果展示

　　updateTime--2018年8月15日10点21分

　　4.2 查询指定时间段的数据

　　表数据展示

　　场景：查询从2018-08-05至2018-08-06的数据

　　错误实现方式：

--错误实现：between and
SELECT REGTIME
  FROM VIRTUAL_CARD
 WHERE REGTIME BETWEEN TO_DATE('2018-08-05', 'yyyy-mm-dd') AND
       TO_DATE('2018-08-07', 'yyyy-mm-dd')
 ORDER BY REGTIME

　　查询结果：[2018/08/05 00:00:00,2018/08/07 00:00:00]

　　方式一：

--方式一：[2018/08/05 00:00:00,2018/08/06 23:59:59]
SELECT REGTIME
  FROM VIRTUAL_CARD
 WHERE REGTIME >= TO_DATE('2018-08-05', 'yyyy-mm-dd') AND
       REGTIME < TO_DATE('2018-08-07', 'yyyy-mm-dd')
 ORDER BY REGTIME

　　方式二：推荐使用

--方式二：[2018/08/05 00:00:00,2018/08/07 00:00:00)
SELECT REGTIME
  FROM VIRTUAL_CARD
 WHERE REGTIME >= TO_DATE('2018-08-05', 'yyyy-mm-dd') AND
       REGTIME < TO_DATE('2018-08-06', 'yyyy-mm-dd') + 1 --2018-08-07
 ORDER BY REGTIME　　

　　方式三：

--方式三
SELECT REGTIME
  FROM VIRTUAL_CARD
 WHERE REGTIME >= TO_DATE('2018-08-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
       REGTIME <= TO_DATE('2018-08-06 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
 ORDER BY REGTIME　　

　　

5.date类型进行加减运算

二、timestamp

1.格式

--查询系统时间戳
SELECT SYSTIMESTAMP FROM DUAL　　

三、相互转换

　　1.日期转时间戳

--系统时间转时间戳
SELECT CAST(SYSDATE AS TIMESTAMP) DATE_TO_TIMESTAMP FROM DUAL

　　

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

 相关推荐：

• ORACLE-SQL（三）
• oracle 获取指定日期的第一天和最后一天