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（月份和日期前面不会通过补0的方式补够2位，如：2022-2-3），

　　将其转换成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 查询指定时间段的数据

2022年2月9日15:08:45

说明：不同数据库工具，对于日期类型的显示格式是不一样的。

PLSQL：默认的，显示格式为YYYY/M/D HH24:MI:SS

可通过首选项进行修改

NAVICATE：默认的显示格式为yyyy-MM-dd HH:mm:ss

可通过：工具--》选项--》记录，修改成自己想要展示的日期效果

ORACLE官方客户端：默认的显示格式如下

可通过SQL来更改本地会话的日期展示结果

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

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

　　

　　注意：

　　年月日需加上双引号；

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

格式化：yyyy,mm,dd,hh,mi,ss，在Oracle当中不区分大小写，结果都一样。

3.字符串转日期

　　方法一：

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

　　转换格式不区分大小写；

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

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

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

　　方式一：标准格式

　　年月日

说明：将年月日转换成日期类型，实际转换结果为：1997/01/01 00:00:00；

证明如下：

　　年月日时分秒

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

　　

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

2021年12月24日11:12:10

方式四：将时间转换成日期

小时转日期

时分转日期

时分秒转日期

分钟转日期

秒转日期

我们可以看到，无论是单独将：小时、小时+分钟、小时+分钟+秒数，转换成日期的时候，都会被统一转换成：

系统当前月的第一天+时分秒（时分秒，会被自动填充），具体转换内容看图片更合适。

年份转日期

月份转日期

天数转日期

年月转日期

　　方法二：

　　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]

由最上面的TO_DATE('dateStr', format)介绍得知：

to_date('2018-08-05','yyyy-mm-dd')，实际代表的日期为：2018年8月5日0时0分0秒。

　　方式一：转换日期前往后推一天+1并用小于号；

   --方式一：[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

说明：不用担心天数+1导致的跨月问题。

　　方式二：转换日期后+1并用小于号；（推荐使用）

   --方式二：[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　　

　　

2021年12月24日14:53:51

方式四：将字段转成字符串。

SELECT REGTIME
  FROM VIRTUAL_CARD
 WHERE TO_CHAR(REGTIME, 'yyyy-mm-dd') >= '2019-08-05' AND
       TO_CHAR(REGTIME, 'yyyy-mm-dd') <= '2019-08-06'
 ORDER BY REGTIME;

2021年12月24日15:28:32

4.3查询指定月数数据

查询2019年7月，8月两个月的数据

由最上面的TO_DATE('dateStr', format)介绍得知：

to_date('2019-07','yyyy-mm')，实际代表的日期为：2019年7月1日0时0分0秒。

方式1：转换成日期后往后推一个月；

--[2019/07/01 00:00:00,2019/09/01 00:00:00)
SELECT REGTIME
  FROM VIRTUAL_CARD
 WHERE REGTIME >= TO_DATE('2019-07', 'yyyy-mm')
   AND REGTIME < TO_DATE('2019-08', 'yyyy-mm') + interval '1' month
 ORDER BY REGTIME;

说明：不用考虑月数+1导致的跨年问题。

方式2：转换成日期前，往后推一个月。

--[2019/07/01 00:00:00,2019/09/01 00:00:00)
SELECT REGTIME
  FROM VIRTUAL_CARD
 WHERE REGTIME >= TO_DATE('2019-07', 'yyyy-mm')
   AND REGTIME < TO_DATE('2019-09', 'yyyy-mm')
 ORDER BY REGTIME;

方式3：to_char()。

SELECT REGTIME
  FROM VIRTUAL_CARD
 WHERE TO_CHAR(REGTIME, 'yyyy-mm') >= '2019-07' AND
       TO_CHAR(REGTIME, 'yyyy-mm') <= '2019-08'
 ORDER BY REGTIME;

4.4查询指定年数数据

查询2017，2018两年的数据

由最上面的TO_DATE('dateStr', format)介绍得知：

to_date('2019','yyyy')，实际代表的日期为：2019年+系统当前月+1日0时0分0秒；

由于系统时间是在一直变化的，这样，将年转换成日期后，就会导致：中间的月份不断变化，根本无法进行固定运算；

所以，这里有且只有一种查询办法：

利用to_char()函数将日期字段转成字符串。

--唯一方式
SELECT REGTIME
  FROM VIRTUAL_CARD
 WHERE TO_CHAR(REGTIME, 'yyyy') >= '2017' AND
       TO_CHAR(REGTIME, 'yyyy') <= '2018'
 ORDER BY REGTIME;

 

5.date类型进行加减运算

5.1 关键词：interval

语法：加减符号+[间隔倍数 * ] interval+'间隔数字'+间隔单位。

获取前1秒时间

select sysdate,sysdate - interval '1' second 1秒前 from dual

获取前1分钟时间

select sysdate,sysdate - interval '1' minute 1分钟前 from dual

 

获取前1小时时间

select sysdate,sysdate - interval '1' hour 1小时前 from dual

获取前1天时间

select sysdate,sysdate - interval '1' day 1天前 from dual

获取前1月时间

方式1：使用interval；

select sysdate,sysdate - interval '1' month 1月前 from dual

方式2：使用add_months()。

Description of add_months.eps follows

获取前1年时间

方式1：使用interval；

select sysdate,sysdate - interval '1' year 1年前 from dual

方式2：使用add_months()。

获取七天前的3种方式

方式一：

方式二：

方式三：

5.2函数add_months()

语法：add_months(日期类型,整数)；

第2个参数虽然支持小数，但是，实际运算结果也只计算整数部分；

第2个参数可以为正数，表示时间往后推，也可以为负数，表示时间往前推。

二、timestamp

1.格式

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

2022年2月9日16:26:24

timestamp的另外两种变形：

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH TIME ZONE

三、相互转换

　　1.日期转时间戳

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

2023年6月9日09:38:53

2.关于sysdate的补充说明

2018年写的文章《oracle对系统日期sysdate进行格式化: to date(sysdate, 'yyyy-MM-dd')》。

在新版本的Oracle当中已经无法执行了。

其实，也可以理解，sysdate本身就是日期date类型，无法再使用to_date()方法也是情理之中。

 

写在最后

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

posted @ 2018-07-19 17:11  Marydon  阅读(40098)  评论(0)  编辑  收藏  举报