【7月更文挑战第7天】
序言
背景说明
Oracle 数据库提供了丰富的内置函数，涵盖数值处理、字符串操作、日期和时间处理、逻辑判断、集合处理、数据分析、数据类型转换等多个方面。下面就随着我一起来学习下这个内置函数吧，有解释不到之处，还望批评指正。
Oracle 数据库提供了一系列强大的日期与时间函数，用于处理和操作日期和时间。在处理过程中，日期和时间是分不开的内置函数，两者结合其他函数处理日期和时间相关的查询、计算、格式化，为我们在工作、学习提供了便利。
示例环境
本篇示例是基于Oracle DB 19c EE （19.17.0.0.0）版本操作，所操作的环境依旧是oracle提供的在线测试环境。如果有不同之处，请指出。
1 常用日期/时间函数
【定义】
SYSDATE：返回服务器当前的日期和时间（是否包含时间取决于使用场景和其他函数）。这个值是由数据库服务器的系统时钟确定的，每次查询时都会实时获取。默认返回格式DD-MM-YY。
CURRENT_DATE：返回当前日期（不含时间部分）。返回格式DD-MM-YY，类似SYSDATE。
CURRENT_TIMESTAMP：返回当前日期和时间，包括时区信息。返回格式DD-MM-YY HH24:MI:SS.FF。
LOCALTIMESTAMP：返回当前日期和时间，不包括时区信息。返回格式DD-MM-YY HH24:MI:SS.FF。
【示例】
获取当前日期（没有其他函数，不做任何修饰的返回）
select SYSDATE, TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') AS "Now", CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP from dual;
例如我们在物理设计模型中没有设计这些函数，可以在代码中使用这些命令返回当前的日期/时间来作为业务的时间点或者可以作为数据的默认时间。关于格式（format models）的定义可以参考：https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Format-Models.html#GUID-E118F121-A0E1-4784-A685-D35CE64B4557
2 日期/时间格式化/解析类函数
上面也讲过，对于日期函数，我们多数都是在解析中或者转换中，以符合我们的业务展示需求。下面就是日期时间函数的格式化和转换过程。
TO_CHAR函数
【定义】
- TO_CHAR(bfile|blob)
- TO_CHAR(character)
- TO_CHAR(datetime)
- TO_CHAR(number)
对于TO_CHAR函数来说，转换日期时间函数有日期时间和数字，本章节只说明针对日期时间函数，可以有的格式有
TO_CHAR(date, format_model): 将日期/时间转换为字符串（简称DT转S），转换过程中主要取决于format_model。
select TO_CHAR(SYSDATE, 'yyyy-mm-dd') AS "Now1", -- 默认获取到年月日 TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') AS "Now2", -- 默认获取到年月日 时分秒 TO_CHAR(SYSDATE, 'month') AS "Now3", -- 格式还可以使用英文单词，例如month，返回april（不区分大小写，但是需要写对） TO_CHAR(SYSDATE, 'YEAR') AS "Now3" -- 格式还可以使用英文单词，例如YEAR，返回TWENTY TWENTY-FOUR（不区分大小写，但是需要写对） from dual;
TO_DATE函数
TO_DATE(string, format_model): 将字符串转换为日期/时间（简称S转DT），转换过程中主要取决于format_model。
select TO_DATE('2024-04-29', 'yyyy-mm-dd') AS "DT1", --TO_DATE('2024-04-29 08:08:08', 'yyyy-mm-dd') AS "DT2", TO_DATE('2024-04-29', 'yyyy-mm-dd hh24:mi:ss') AS "DT3", TO_DATE('2024-04-29 01:02:03', 'yyyy-mm-dd hh:mi:ss') AS "DT4" from dual;
注意⚠️
此函数时转为日期，多余的会舍弃掉。
当我们使用转换函数时，可以由小转大，例如DT3；
但是不能使用小转到，接收不了，例如DT2，此时会报错：ORA-01830: date format picture ends before converting entire input string
TO_TIMESTAMP函数
【定义格式】
TO_TIMESTAMP(string1 [, format_model] [[, nls_language]])：将字符串转换为带时区的时间戳，转换过程中主要取决于format_model。
- string1：要转换的原始字符串，包含日期和时间信息。
- format_model（可选）：定义字符串中日期和时间元素的格式模型。如果省略，Oracle会尝试使用默认的日期格式进行解析，但这可能导致错误，特别是当字符串格式与数据库的默认格式不匹配时。
- nls_language（可选）：用于控制日期时间元素的翻译，比如月份和星期的名称。如果省略，将使用会话的当前语言设置。
【示例】
select TO_TIMESTAMP('2024-04-29', 'yyyy-mm-dd') AS "DT1", --TO_TIMESTAMP('2024-04-29 08:08:08', 'yyyy-mm-dd') AS "DT2", TO_TIMESTAMP('2024-04-29', 'yyyy-mm-dd hh24:mi:ss') AS "DT3", TO_TIMESTAMP('2024-04-29 01:02:03', 'yyyy-mm-dd hh:mi:ss') AS "DT4" from dual;
注意⚠️
此函数时转为时间，不足的会补充。其中包含时区信息（TO_TIMESTAMP_TZ）的很少使用，这里不在赘述。
当我们使用转换函数时，即使我们写了日期格式，也会补充时间格式，例如DT1；
当我们使用转换函数时，使用的格式不足支撑数据则会报错，例如DT2；
当我们使用转换函数时，使用的数据值不满足格式时，会补充时间格式，例如DT3；
当我们使用转换函数时，正常情况下例如DT4；
3 日期运算类函数
这里函数在SQL计算中很常见，例如查询3个月以前的数据，查询上周的数据等等。下面就是一些常用的函数ADD_MONTHS、LAST_DAY、NEXT_DAY、MONTHS_BETWEEN。
ADD_MONTHS函数
【定义】
ADD_MONTHS(date, months): 加减指定月份数。
【示例】
在当前月份加上3个月或者减去4个月后的的日期值。
select SYSDATE, -- 当前日期：2024-04-29 ADD_MONTHS(SYSDATE, 3), -- 当前日期加上3个月后：2024-07 ADD_MONTHS(SYSDATE, -4) -- 当前日期减去4个月后：2023-12 from dual;
LAST_DAY函数
常用于具体的算法，例如想要获取指定日期所在月份的最后一天。
LAST_DAY(date): 返回指定日期所在月份的最后一天。
select SYSDATE, LAST_DAY(SYSDATE) from dual;
NEXT_DAY函数
【定义】
NEXT_DAY(date, day_of_week): 返回指定日期后下一个指定星期几的日期，返回值为日期。
【示例】
select SYSDATE, -- 今天2024-04-29，周一 NEXT_DAY(SYSDATE, 1), -- 返回下一个星期天，也就是2024-05-05周日（星期日 = 1 星期一 = 2 星期二 = 3 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7） NEXT_DAY(SYSDATE, 6), -- 返回下一个星期五，也就是2024-05-03周五（星期日 = 1 星期一 = 2 星期二 = 3 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7） NEXT_DAY(SYSDATE, 'FRIDAY') from dual;
其中，day_of_week的值从星期天开始，也可以使用英文单词（FRIDAY），如下
星期日 = 1 星期一 = 2 星期二 = 3 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7
MONTHS_BETWEEN函数
个人认为这个函数使用的频率不是很高，使用比较高的场景应该是计算两个日期之间的天数，可惜没有类似功能的函数，不过还比较好，可以使用日期的加减法来计算，后续会有这块内容讲述。
【定义】
MONTHS_BETWEEN(date1, date2[, round_mode]): 计算两个日期之间的月数。
【描述】
MONTHS_BETWEEN返回日期date1和date2之间的月数。月份和月份的最后一天由参数NLS_CALENDAR定义。如果date1晚于date2，则结果为正。如果date1早于date2，则结果为负。如果date1和date2是一个月中的同一天或两个月的最后几天，则结果始终为整数。否则，Oracle数据库根据31天的月份计算结果的小数部分，并考虑时间分量date1和date2的差异。
【示例】
见名识义，例如我想获取2023-11-26 到 2024-04-29 之间的月份。
4 提取特定日期/时间函数
提取函数同截取函数差不多，都是获取到业务需要的部分，然后进入到其他计算范畴。
【定义】
EXTRACT(component FROM datetime): 提取日期/时间的特定部分（如YEAR、MONTH、DAY等）。
【示意图】
【示例】
我想获取2024-04-29中的各个数据，则可以使用下面的方式
select SYSDATE, EXTRACT(year FROM TO_TIMESTAMP('2024-04-29 01:02:03', 'yyyy-mm-dd hh:mi:ss')) "Year", EXTRACT(month FROM TO_TIMESTAMP('2024-04-29 01:02:03', 'yyyy-mm-dd hh:mi:ss')) "Month", EXTRACT(day FROM TO_TIMESTAMP('2024-04-29 01:02:03', 'yyyy-mm-dd hh:mi:ss')) "Day", EXTRACT(hour FROM TO_TIMESTAMP('2024-04-29 01:02:03', 'yyyy-mm-dd hh:mi:ss')) "Hour", EXTRACT(minute FROM TO_TIMESTAMP('2024-04-29 01:02:03', 'yyyy-mm-dd hh:mi:ss')) "Minute", EXTRACT(second FROM TO_TIMESTAMP('2024-04-29 01:02:03', 'yyyy-mm-dd hh:mi:ss')) "Second" from dual;
5 日期函数加/减法
日期函数还可以进行加减法运算，话不多说，直接贴代码。例如我想返回当前日期加上2天后的日期，想返回当前日期减去3天后的日期。
select sysdate, -- 当前日期 SYSDATE, -- 当前日期 -- 当前日期加上2天 sysdate+2, -- 当前日期加上3天 sysdate-3 from dual;
总结
本篇主要学习日期和时间相关的内置函数，在我们使用过程中有任何问题都可以在站内联系我。有些内置函数可以在SELECT语句中用于处理数据，同时也可以在WHERE等语句中使用。今天了解到的函数在处理数据库中的字符串数据时非常有用，特别是当需要转换、去除、清理或格式化数据时。具体是如何使用，都要考虑使用场景以及性能方面的问题，后续会再出一些相关的博文。
[引用参考]
