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               |
+---------------------+

示例

假設我們在 SQL 資料庫中使用 CREATE 語句建立了一個名為 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;

表 EMPLOYEE 已成功在 SQL 資料庫中建立。

+-----+--------+---------------+
| 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           |
+-----+--------+----------------+--------------+

示例

讓我們在 SQL 資料庫中使用 CREATE 語句建立另一個名為 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 函式導致溢位。兩個日期/時間例項之間的 datepart 數過多。嘗試使用精度較低的 datepart 與 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-date-functions.htm
列印頁面
上一頁
下一頁
廣告

© . All rights reserved.