SQL DATEDIFF_BIG() 函数

SQL的 DATEDIFF_BIG() 函数与DATEDIFF()函数非常相似，但它用于计算指定开始日期和结束日期之间的差异（作为有符号的大整数值）。

要使用此函数，我们需要提供三个参数：用于测量间隔类型（如年、季度、月、小时、分钟等），指定测量期间开始的起始日期或时间，以及指定测量期间结束的结束日期或时间。

此函数以64位的bigint数据类型返回结果，可存储的值最大为9,223,372,036,854,775,807。而DATEDIFF()函数只能存储最大值为2,147,483,647的值。

语法

以下是SQL DATEDIFF_BIG() 函数的语法：

DATEDIFF_BIG(datepart, startdate, enddate)

参数

这个函数接受三个参数。分别如下所述：

datepart − 这指定要返回的日期或时间段的类型。可能的值包括：
- year，yyyy，yy = 年
- quarter，qq，q = 季度
- month，mm，m = 月
- dayofyear，dy，y = 年的第几天
- day，dd，d = 天
- week，ww，wk = 周
- weekday，dw，w = 工作日
- hour，hh = 小时
- minute，mi，n = 分钟
- second，ss，s = 秒
- millisecond，ms = 毫秒
startdate，enddate − 这两个参数指定我们要比较的两个日期。

示例

在以下示例中，我们使用以下查询来计算两个日期之间的差异（以年为单位）：

SQL> SELECT DATEDIFF_BIG(YEAR, '2023/03/15', '2033/02/15') AS DATE_DIFF_IN_YEARS;

输出

当我们执行上述查询时，得到的输出如下：

+--------------------+
| DATE_DIFF_IN_YEARS |
+--------------------+
| 10                 |
+--------------------+

示例

在这里，我们使用以下查询计算两个日期值之间的差异，以季度为单位。

SQL> SELECT DATEDIFF_BIG(QUARTER, '2023/03/15', '2024/02/15') AS DATE_DIFF_IN_QUARTER;

输出

执行上述查询后，输出结果如下所示−

+----------------------+
| DATE_DIFF_IN_QUARTER |
+----------------------+
| 4                    |
+----------------------+

示例

我们可以使用下面的查询来计算两个指定时间之间的差异，以秒为单位。

注意 - CURRENT_TIMESTAMP 函数检索当前日期和时间，并可用作函数的参数。

SQL> SELECT DATEDIFF_BIG(SECOND, '2023/02/15 07:30:00:000', CURRENT_TIMESTAMP) AS TIME_DIFF_IN_SECONDS;

输出

上述查询的输出如下所示-

+----------------------+
| TIME_DIFF_IN_SECONDS |
+----------------------+
| 368610               |
+----------------------+

示例

在这个例子中，我们计算了两个指定时间之间的差异，单位为分钟 –

注意 – GETDATE() 函数获取当前日期和时间，也可以作为函数的参数使用。

SQL> SELECT DATEDIFF_BIG(MINUTE, '2023/02/01 07:30', GETDATE()) AS TIME_DIFF_IN_MINUTE

输出

如果我们执行上述查询，结果如下所示：

+---------------------+
| TIME_DIFF_IN_MINUTE |
+---------------------+
| 26304               |
+---------------------+

示例

假设我们使用以下查询中的CREATE语句在SQL数据库中创建了一个名为EMPLOYEE的表 –

SQL> CREATE TABLE EMPLOYEE(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, DATE_OF_BIRTH VARCHAR (20));

现在，让我们使用INSERT语句将一些记录插入到EMPLOYEE表中，如下面的查询所示−

SQL> INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(1, 'Dhruv', '2000-12-05');
INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(2, 'Arjun', '2000-03-01');
INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(3, 'Dev', '2001-03-15');
INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(4, 'Riya', '2003-12-05');
INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(5, 'Aarohi', '2000-05-02');
INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(6, 'Lisa', '1999-11-25');
INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(7, 'Roy', '2001-05-30');

我们可以使用以下查询验证表是否已创建：

SQL> SELECT * FROM EMPLOYEE;

在SQL数据库中，表EMPLOYEE已成功创建。

+-----+--------+---------------+
| ID  | NAME   | DATE_OF_BIRTH |
+-----+--------+---------------+
| 1   | Dhruv  | 2000-12-05    |
| 2   | Arjun  | 2000-03-01    |
| 3   | Dev    | 2001-03-15    |
| 4   | Riya   | 2003-12-05    |
| 5   | Aarohi | 2000-05-02    |
| 6   | Lisa   | 1999-11-25    |
| 7   | Roy    | 2001-05-30    |
+-----+--------+---------------+

这里，我们使用以下查询来计算每个员工的年龄：

SQL> SELECT ID, NAME, DATE_OF_BIRTH, DATEDIFF_BIG(YEAR, DATE_OF_BIRTH, GETDATE()) AS AGE_IN_YEARS FROM EMPLOYEE;

输出

当执行以上查询时，输出结果如下：

+-----+--------+----------------+--------------+
| ID  | NAME   | DATE_OF_BIRTH  | AGE_IN_YEARS |   
+-----+--------+----------------+--------------+
| 1   | Dhruv  | 2000-12-05     | 23           |
| 2   | Arjun  | 2000-03-01     | 23           |
| 3   | Dev    | 2001-03-15     | 22           |
| 4   | Riya   | 2003-12-05     | 20           |
| 5   | Aarohi | 2000-05-02     | 23           |
| 6   | Lisa   | 1999-11-25     | 24           |
| 7   | Roy    | 2001-05-30     | 22           |
+-----+--------+----------------+--------------+

示例

让我们使用CREATE语句在SQL数据库中创建另一个名为OTT的表格，如下所示的查询：

SQL> CREATE TABLE OTT(ID INT NOT NULL, SUBSCRIBER_NAME VARCHAR (200) NOT NULL, MEMBERSHIP VARCHAR (200), SUBCRIPTION_DATE DATE NOT NULL);

现在，让我们使用INSERT语句将一些记录插入到OTT表中，如下面的查询所示 −

SQL> INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(1, 'Dhruv', 'Silver', '2022-12-05');
INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(2, 'Arjun','Platinum', '2021-03-01');
INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(3, 'Dev','Silver', '2021-03-15');
INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(4, 'Riya','Gold', '2022-12-05');
INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(5, 'Aarohi','Platinum', '2020-05-02');
INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(6, 'Lisa','Platinum', '2022-11-25');
INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(7, 'Roy','Gold', '2021-05-30');

我们可以使用以下查询来验证是否创建了OTT表 –

SQL> SELECT * FROM OTT;

表格 OTT 在 SQL 数据库中成功创建。

+-----+-----------------+------------+------------------+
| ID  | SUBSCRIBER_NAME | MEMBERSHIP | SUBSCRIPTION_DATE|   
+-----+-----------------+------------+------------------+
| 1   | Dhruv           | Silver     | 2022-12-05       |
| 2   | Arjun           | Platinum   | 2021-03-01       |
| 3   | Dev             | Silver     | 2021-03-15       |
| 4   | Riya            | Gold       | 2022-12-05       |
| 5   | Aarohi          | Platinum   | 2020-05-02       |
| 6   | Lisa            | Platinum   | 2022-11-25       |
| 7   | Roy             | Gold       | 2021-05-30       |
+-----+-----------------+------------+------------------+

我们可以使用以下查询来显示订阅计划完成的剩余天数：

SQL> SELECT SUBSCRIBER_NAME, SUBCRIPTION_DATE, DATEDIFF_BIG(DAY, SUBCRIPTION_DATE, CURRENT_TIMESTAMP) AS REMAINING_DAYS FROM OTT;

输出

如果我们执行上述查询，结果如下所示−

+-----------------+-------------------+-----------------+
| SUBSCRIBER_NAME | SUBSCRIPTION_DATE | REMAINING_DAYS  |   
+-----------------+-------------------+-----------------+
| Dhruv           | 2022-12-05        | 72              |
| Arjun           | 2021-03-01        | 716             |
| Dev             | 2021-03-15        | 702             |
| Riya            | 2022-12-05        | 72              |
| Aarohi          | 2020-05-02        | 1019            |
| Lisa            | 2022-11-25        | 82              |
| Roy             | 2021-05-30        | 626             |
+-----------------+-------------------+-----------------+

DATEDIFF()和DATEDIFF_BIG()之间的区别

在SQL中，DATEDIFF()和DATEDIFF_BIG()函数用于计算两个日期之间的差值。它们之间的主要区别是它们返回的结果的数据类型。

DATEDIFF() 函数返回一个32位的整数数据类型，可以存储的值最大为2,147,483,647。如果函数的结果超过整数数据类型所能存储的最大值，则返回溢出错误。
DATEDIFF_BIG() 函数返回一个64位的 bigint 数据类型，可以存储的值最大为9,223,372,036,854,775,807。当函数的结果超过整数数据类型所能存储的最大值时，可以使用此函数。

让我们用适当的示例来了解上述两个函数之间的区别 −

示例

在下面的示例中，我们试图使用 DATEDIFF()函数 来计算两个日期值之间的秒数差值。

SQL> SELECT DATEDIFF(SECOND, '0001-01-01 00:00:00', '9999-01-02 00:00:00') AS RESULT

错误

在这里，DATEDIFF()函数的结果出现错误，因为它超过了整数数据类型可以存储的最大值。

datediff函数导致溢出。两个日期/时间实例之间的日期部分的数量过大。尝试使用较不精确的日期部分来使用datediff。

示例

在下面的示例中，我们尝试使用 DATEDIFF_BIG() 函数来计算相同的两个日期值之间的秒数差异。。

SQL> SELECT DATEDIFF_BIG(SECOND, '0001-01-01 00:00:00', '9999-01-02 00:00:00') AS RESULT

输出

在这里，DATEDIFF_BIG()函数可以处理更大的结果而不会出错。

+--------------+
| RESULT       |
+--------------+
| 315506448000 |
+--------------+

SQL DATEDIFF_BIG() 函数

SQL DATEDIFF_BIG() 函数

语法

参数

示例

输出

示例

输出

示例

输出

示例

输出

示例

输出

示例

输出

DATEDIFF()和DATEDIFF_BIG()之间的区别

示例

错误

示例

输出

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程

SQL 精品教程

回顶部