Oracle实践｜Oracle内置函数之日期与时间函数

原创

Aion

发布于 2024-04-29 21:39:47

2595

发布于 2024-04-29 21:39:47

文章被收录于专栏：开发运维工程师开发运维工程师

序言

背景说明

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等语句中使用。今天了解到的函数在处理数据库中的字符串数据时非常有用，特别是当需要转换、去除、清理或格式化数据时。具体是如何使用，都要考虑使用场景以及性能方面的问题，后续会再出一些相关的博文。

[引用参考]