8.表：日期类型

日期类型

• DATE：表示的日期范围可以是公元前4712年1月1日至公元9999年12月31日

  • DATE类型在数据库中的存储固定为7个字节，格式为：

    • 第1个字节：世纪+100

    • 第2个字节：年

    • 第3个字节：月

    • 第4个字节：天

    • 第5个字节：小时+1

    • 第6个字节：分+1

    • 第7个字节：秒+1

• TIMESTAMP：与DATE的区别是不仅可以保存日期和时间，还能保存小数秒，最高精度可以到ns（纳秒）

  • 数据库内部用7或者11个字节存储，精度为0，用7字节存储，与DATE功能相同，精度大于0则用11字节存储

  • 格式为：

    • 第1-7个字节：和DATE相同

    • 第8-11字节：纳秒，采用4个字节存储，内部运算类型为整型

日期的计算

• 日期可以与一个数字进行加减法，这相当于加减指定的天数

• 两个日期可以进行减法，差为相差的天数

日期关键字

• SYSDATE：其本质是一个Oracle的内部函数，返回当前的系统日期和时间，精确到秒

  • 默认显示格式是：DD-MON-RR（日-月-年）

• SYSTIMESTAMP：内部函数，返回当前系统日期和时间，精确到毫秒

日期转换函数：TO_DATE(char,fmt,nlsparams)

• 将字符串按照定制格式转换为日期类型

• char：要转换的字符串

• fmt：格式，可以不设置，fmt格式字符串中出现汉字或其他非符号字符时需要引用双引号

• nlsparams：指定日期语言，可以不设置

• 常用的日期格式：

  

• RR解析年份的问题：当使用RR解析某个年份，该年份，数据需要判断它是属于哪个世纪的，YY则直接解析成本世纪

  • 如：TO_DATE(‘96-08-07’,‘RR-MM-DD’),RR解析96年，遇到96年应该指的是1896、1996还是2096年的问题，数据库会根据以下做判断：

  

日期转换函数：TO_CHAR(date,fmt,nlsparams)

• 将日期类型转换为字符串类型

• 将日期类型数据date按照fmt格式输出字符串，nlsparams用于指定日期语言

• fmt、nlsparams可以不进行设置

• fmt格式字符串中出现汉字或其他非符号字符时需要引用双引号

日期计算函数：LAST_DAY(date)

• 返回日期date所在月的最后一天的日期，时间是当前系统时间

日期计算函数：ADD_MONTHS(date,i)

• 返回日期date加上i个月后的日期值

• 参数i可以是任何数字，大部分时候取正整数

• 如果i是小数，将会被截取整数后再参与运算

• 如果i是负数，则获得的是减去i个月后的日期值

日期计算函数：MONTHS_BETWEEN(date1,date2)

• 计算date1和date2两个日期之间间隔了多少个月

• 实际运算是date1-date2，如果时间date2比date1晚，会得到负值

• 除非两个日期间隔是整数月，否则会得到带小数位的结果

日期计算函数：NEXT_DAY(date,char)

• 返回给定日期的第二天开始一周之内的指定周几的日期和时间，周几由参数char来决定的

• 在中文环境下，直接使用“星期三”这种形式，英文环境下使用“WEDNESDAY”这种形式，也可以直接用数字1-7表示周日-周六

• 如：date的日期显示为周三，char=6（周五），那么结果就是本周五的日期和时间，若char=2，那么就是下周一的日期和时间

• NEXT_DAY不是明天

日期计算函数：GREATEST、LEAST

• GREASTEST(参数1,参数2…)、LEAST(参数1,参数2…)

• 也被称作比较函数，可以有多个参数值，返回结果是参数列表中最大或最小值

• 参数类型必须一致

• 在比较之前，在参数列表中第二个以后的参数会被隐含的转换为第一个参数的数据类型，所以如果可以转换，则继续比较，如果不能转换将会报错

日期计算函数：EXTRACT(date FROM datetime)：

• 从参数中提取参数date指定的数据，比如提出年，月，日

演示：日期操作

• 显示日期

  SELECT SYSDATE FROM dual;

  SELECT SYSTIMESTAMP FROM dual;

• 日期的计算

  • 查看每个员工至今入职多少天
    SELECT ename,(SYSDATE-hiredate) FROM emp;

  • 查看自己出生至今活了多少天
    SELECT (SYSDATE-TO_DATE(‘1997-11-10’,‘YYYY-MM-DD’)) FROM dual;

• TO_DATE函数

  SELECT TO_DATE(‘2008-08-08 20:08:08’,‘YYYY-MM-DD HH24:MI:SS’) FROM dual;

  SELECT TO_DATE(‘2008年08月08日’,‘YYYY"年"MM"月"DD"日"’) FROM dual;

• TO_CHAR

  SELECT TO_CHAR(SYSDATE,‘YYYY-MM-DD HH24:MI:SS’) FROM dual;

  • RR解析年份的问题
    SELECT TO_CHAR(TO_DATE(‘96-07-12’,‘RR-MM-DD’),‘YYYY-MM-DD’) FROM dual;
    SELECT TO_CHAR(TO_DATE(‘96-07-12’,‘YY-MM-DD’),‘YYYY-MM-DD’) FROM dual;

• LAST_DAY

  SELECT TO_CHAR(LAST_DAY(SYSDATE),‘YYYY-MM-DD HH24:MI:SS’) FROM dual;

• ADD_MONTHS

  • 查看每个员工入职20周年的纪念日
    SELECT ADD_MONTHS(hiredate,20*12) FROM emp;

• MONTHS_BETWEEN

  • 查看每个员工至今入职了多少个月
    SELECT MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;

• NEXT_DAY

  SELECT NEXT_DAY(SYSDATE,6) FROM dual;

  SELECT NEXT_DAY(SYSDATE,2) FROM dual;

• LEAST、GREATEST

  SELECT LEAST(SYSDATE,TO_DATE(‘2008-08-05’,‘YYYY-MM-DD’)) FROM dual;

  SELECT GREATEST(SYSDATE,TO_DATE(‘2008-08-05’,‘YYYY-MM-DD’)) FROM dual;

  SELECT GREATEST(1,2,3,4) FROM dual;

• EXTRACT

  SELECT EXTRACT(HOUR FROM SYSDATE) FROM dual;

  • 查看1980年入职的员工
    SELECT * FROM emp WHERE EXTRACT(YEAR FROM hiredate)=1980;