date类型_关于Oracle数据库date类型重要说明

新的一年愿贪吃不胖

       开门见山，对于oracle date类型，无论是oracle开发人员还是DBA都不陌生，今天我们要聊的主题是date类型在oracle数据库中的重要性，以及相关PL/SQL代码应该如何检索日期类型的相关命题。文档内容相对基础，请您耐心看完。

01

为什么不建议用varchar2变长字符存储日期类型？

       有的读者会问：为什么不能将DATE存储在VARCHAR2数据类型中，当我们尝试将日期存储为VARCHAR2时，该日期中的每个数字或字母都将成为字符串中的字符，并且存储在自己的字节中，这是一种不太友好的行为。

       DUMP返回一个VARCHAR2值，其中包含数据类型代码、字节长度和表达式的内部表示。返回的结果总是在数据库字符集中，截图内容返回十进制的计数格式，默认情况下，返回值不包含字符集信息。要检索expr的字符集名称，请向前面的任何格式值添加1000。例如，return_fmt(1008)以八进制形式返回结果，并提供expr的字符集名称。

      这里我们可以清晰的看到date类型返回八进制的计数格式，并且根本不需要太多字节存储，和上面varchar2变长字符有着明显存储容量上的差距

       存储为字符串的日期是不能够按照日期类型形式进行排序的，那么为什么不能够按照日期类型排序呢，我们还是一样，来看看字节数量

      我们仔细关注每个数字的ascii码变化顺序，从第七位开始，按照第一个字符的ascii码顺序，然后在由第二个ascii码进行组内排序，并且变长字符的排序需要在逐个字符上递归的完成，这一点在日常sql检索过程中，是非常耗时的。

         通过上图仔细观察字符串递归顺序，我们不难发现问题所在。

阶段性总结

      通过简单的dump信息，我们验证了，为什么不建议用变长字符存储日期类型，第一，浪费存储空间，第二影响排序效果，开发人员如果还是这么做，作为DBA，我们很有必要重新设计表结构了

02

1

PL/SQL date适用场景

• 日期也是一种比字符串或数字复杂得多的数据类型。它有多个部分(年、月、日、小时、分、秒、毫秒等等)，关于有效日期的组成有许多规则。这里600团队提供了在PL/SQL程序中开始处理日期所需的相关信息

• PL/SQL中的日期、时间戳和间隔大多数应用程序需要存储和操作日期和时间。与字符串和数字不同，日期非常复杂:不仅是高度格式化的数据，还有许多用于确定有效值和有效计算的规则(闰日和年份、夏令时更改、国家和公司假期、日期范围，等等)。

• PL/SQL提供了一组真实的日期和时间数据类型，这些数据类型以标准的内部格式存储日期和时间信息，并且它们还有一组用于操作日期和时间的内置函数

• 有三种格式处理日期和时间

1. DATE类型—此数据类型存储解析为秒的日期和时间。它不包括时区。DATE是Oracle应用程序中用于处理日期的最古老和最常用的数据类型

2. TIMESTAMP-Time Stamps类似日期,但有两个关键特征:(1)可以存储和操纵时间解决精确到1000000000秒(9位小数精度),和(2)
   可以关联时区和时间戳、和Oracle数据库将时区时操纵时间戳

3. INTERVAL-日期和时间戳记录时间中的特定点，而INTERVAL记录并计算时间持续时间。可以以年、月或日、秒为单位指定时间
   间隔

下面我们针对这三种类型提供一份示例代码

2

PLSQL date工作原理及其注意事项

       2.1  通常，我们可以使用时间戳来代替日期。与日期数据类型一样，不包含亚秒精度的时间戳占用7字节的存储空间。当时间戳确实包含亚秒数据时，它将占用11字节的存储空间。

       使用带有时区的时间间隔和时间戳可能非常复杂;相对而言，很少有开发人员需要这些更高级的特性。本文主要讨论核心日期和间戳类型，以及最常用的内置函数。

• 如果需要将时间跟踪到几分之一秒，需要使用一种时间戳类型。

• 如果需要跟踪数据输入的会话时区，需要使用带有时区的时间戳

• 如果希望数据库在数据库和会话时区之间自动转换时间，就需要使用带有本地时区的时间戳

• 当需要与引入任何时间戳数据类型之前编写的现有应用程序保持兼容性时，使用DATE

• 在PL/SQL代码中使用与底层数据库表相对应或至少兼容的数据类型。例如，在将表中的时间戳值读入日期变量之前，请三思，因为我们可能会丢失信息(在本例中是小数秒，也可能是时区)

• 获取当前日期和时间。PL/SQL开发人员通常需要检索和处理当前日期和时间。大多数开发人员使用经典的SYSDATE函数，但是Oracle数据库现在提供了几个函数来提供这种信息的变体，如下面的图表显示内容

例：调用SYSDATE和SYSTIMESTAMP以及返回的值

      因为我已经将日期和时间戳传递给了BMS_OUTPUT。使用数据库或会话的默认格式掩码(由国家语言设置NLS_DATE_FORMAT参数指定)，Oracle数据库隐式地将它们转换为字符串。Oracle数据库的默认安装将默认日期格式设置为DD-MON-YYYY。默认的时间戳格式包括日期偏移量和时区偏移量

注意：可以执行日期运算:我从SYSDATE返回的值中减去SYSTIMESTAMP返回的值。结果是一个非常接近(但不完全等于)零的区间

  2.2  将日期转换为字符串，并将字符串转换为日期。对于数字，使用TO_CHAR函数的另一个版本将日期或时间戳转换为字符串。与数字一样，Oracle数据库提供了大量的格式元素，可以帮助我们调整字符串，使其显示在程序需要的地方。

        以下是一些例子:

1

 使用TO_CHAR时不带格式掩码

          使用TO_CHAR时不带格式掩码。如果不包含格式掩码，TO_CHAR返回的字符串将与Oracle数据库执行隐式转换时返回的字符串
相同:

2

使用TO_CHAR显示日期中的日期和月份的全名

注意：

用于显示这些名称的语言是由LS_DATE_LANGUAGE设置确定的，该设置也可以指定为TO_CHAR调用中的第三个参数

3

使用EXTRACT提取并返回日期的指定元素的值

        可以使用EXTRACT提取并返回日期的指定元素的值，例如EXTRACT (YEAR FROM SYSDATE)，EXTRACT (DAY FROM

SYSDATE)

• 下面的函数可以用来计算一个人的年龄，假设这个人的正确出生日期是函数唯一的参数

• 从一月的最后一天开始，提前一个月。从不同的日期开始，回溯一个月。从二月的最后一天开始，往回走一个月。下面显示了对ADD_MONTHS函数的三个不同调用以及结果

答

题

时

刻

下面我们来看一个oracle数据库开发面试的面试题

01

Oracle数据库提供了一个函数，用于返回每月最后一天的日期。但是，它没有提供返回第一天日期的函数。下面哪个选项可以用
来做这个

1. CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
   RETURN DATE
   IS
   BEGIN
   RETURN TRUNC (date_in);
   END;
   /

2. CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
   RETURN DATE
   IS
   BEGIN
   RETURN TRUNC (date_in, 'MM');
   END;
   /

3. CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
   RETURN DATE
   IS
   BEGIN
   RETURN TRUNC (date_in, 'MONTH');
   END;
   /

4. CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
   RETURN DATE
   IS
   BEGIN
   RETURN TO_DATE (TO_CHAR (date_in, 'YYYY-MM')
   || '-01', 'YYYY-MM-DD');
   END;

   /

02

下面哪个匿名块提供异常部分，以便在执行该匿名块之后，日期和时间2012-01-01 00:00:01将显示出来

DECLARE
c_format CONSTANT VARCHAR2 (22) := 'YYYY-MM-DD HH24:MI:SS' ;
l_new_year DATE := TO_DATE ('2012-01-02 00:00:01', c_format);

1. BEGIN
   DBMS_OUTPUT.put_line (TO_CHAR (l_new_year - 24, c_format));
   END;

2. BEGIN
   DBMS_OUTPUT.put_line (TO_CHAR (l_new_year - 1, c_format));
   END;

3. BEGIN
   DBMS_OUTPUT.put_line (TO_CHAR (l_new_year- 24 * 60 * 60, c_format));
   END;

4. BEGIN
   DBMS_OUTPUT.put_line (TO_CHAR (TRUNC (l_new_year)- 1+ 1 / (24 * 60 * 60), c_format));
   END;

面试题答案

其实很简单，第一个题：如果传递给函数的数字是整数，则plch__and_floor函数总是返回1或0:0，否则返回1，第二道题，1,2两个答案都是OK的。亲，你答对了吗？

03

总结

• 今天通过一些基础的工作原理，解释了为什么日期存储不选择变长字符串方式，以及字符串方式存储日期格式的诸多不利

• 通过PL/SQL的工作原理及其简单的案例，为大家分享PL.SQL中如何使用日期函数

• 最后，如果您对PL/SQL编程感兴趣，如果您苦苦挣扎不知道高效的sql怎么写，可以加入我们，我会尽自己最大努力，让你
  成为PLSQL编程的高手。

好消息！好消息！

    3月中旬开始，600团队开启线上免费公开课，届时请关注600公众号，我们会在公众号注明具体公开课程下载地址，课程完全免费 (划重点)

? 福利时间到！

为表达600团队的真诚，下面为大家

展示学习大纲，可要仔细阅读哦

主题包括但不限于如下

1、结合作者在国网和石化以及五矿做开发DBA实体开发案例和群内其他成员案例；

2、如何编写高效的SQL语句，以及PLSQL代码；

3、如何分析awr报告，包括定位问题，诊断问题，如何看懂hanganalyze systemstate errorstack oradebug工具使用等等。

    ? 同时如果公开课反响不错，后期我们也会开放备份恢复，数据库性能优化和sql优化包括工作原理解析等实际生产环境遇到的案例。后期mysql和postgresql包括voltdb geode greenplum以及hadoop如果大家感兴趣，我们都可以在公开课内容体现，暂时以oracle为主，谢谢大家。

风里雨里，我们在600群里等你！！！

最终解释权归600自媒体所有