萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL 日期時間差值計算:DATEDIFF 完整用法

SQL Server DATEDIFF 基本語法

DATEDIFF(datepart, startdate, enddate)

datepart 可為:year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond 等。

常見間隔計算範例

需求語句
相差天數SELECT DATEDIFF(day, '2008-08-25', '2008-09-01')
相差秒數SELECT DATEDIFF(second, '2009-08-25 12:15:12', '2009-09-01 07:18:20')
相差分鐘數SELECT DATEDIFF(minute, '2009-08-25 12:15:12', '2009-09-01 07:18:20')
相差小時數SELECT DATEDIFF(hour, '2009-08-25 12:15:12', '2009-09-01 07:18:20')

進階:將總秒數轉換為「天 時 分 秒」格式

DECLARE @dt1 DATETIME = '2008-08-04 09:36:41',
        @dt2 DATETIME = '2008-08-02 09:33:39';
DECLARE @seconds INT = DATEDIFF(second, @dt2, @dt1);
DECLARE @days INT = @seconds / 86400;
SET @seconds = @seconds % 86400;
DECLARE @hours INT = @seconds / 3600;
SET @seconds = @seconds % 3600;
DECLARE @minutes INT = @seconds / 60;
SET @seconds = @seconds % 60;
SELECT CAST(@days AS VARCHAR) + '天' +
       CAST(@hours AS VARCHAR) + '小時' +
       CAST(@minutes AS VARCHAR) + '分' +
       CAST(@seconds AS VARCHAR) + '秒';

處理「天數+時間」字串相減(VARCHAR 型別)

利用 DATEADD 與 DATEDIFF 組合:

DECLARE @t TABLE (beginday INT, begintime VARCHAR(20), endday INT, endtime VARCHAR(20));
INSERT @t VALUES (1,'16:00',2,'12:20'), (1,'03:00',3,'19:10');

SELECT RTRIM(datediff_minute/60) + ':' + RTRIM(datediff_minute%60) AS duration
FROM (
    SELECT DATEDIFF(minute,
           DATEADD(day, beginday, CAST(begintime AS DATETIME)),
           DATEADD(day, endday, CAST(endtime AS DATETIME))
    ) AS datediff_minute
    FROM @t
) t;

Oracle/PostgreSQL 寫法差異

  • Oracle:TRUNC(SYSDATE) - TO_DATE('2009-01-04','YYYY-MM-DD') 直接相減得天數。
  • PostgreSQL:AGE(end, start) 回傳 interval,可用 EXTRACT 取各部份。
copyright © 萬盛學電腦網 all rights reserved