DATEDIFF¶

Calculates the difference between to DATE or DATETIME expressions, in terms of a specific date part.

Note

Results are given in integers, rather than INTERVAL, which Blue does not support.

Syntax¶

DATEDIFF( interval, date_expr1, date_expr2 ) --> INT

interval ::=
     YEAR | YYYY | YY
   | QUARTER | QQ | Q
   | MONTH | MM | M
   | DAYOFYEAR | DY | Y
   | DAY | DD | D
   | WEEK | WK | WW
   | HOUR | HH
   | MINUTE | MI | N
   | SECOND | SS | S
   | MILLISECOND | MS

Arguments¶

Parameter	Description
`interval`	An interval representing a date part. See the table below or the syntax reference above for valid date parts
`date_expr1`, `date_expr2`	A `DATE` or `DATETIME` expression. The function calculates `date_expr2 - date_expr1`.

Valid date parts¶

Date part	Shorthand	Definition
`YEAR`	`YYYY`, `YY`	Year (0 - 9999)
`QUARTER`	`QQ`, `Q`	Quarter (1-4)
`MONTH`	`MM`, `M`	Month (1-12)
`DAY`	`DD`, `D`, `DAYOFYEAR`, `DY`, `Y`	Days (1-365)
`WEEK`	`WK`, `WW`	Week of the year (1-52)
`HOUR`	`HH`	Hour (0-23)
`MINUTE`	`MI`, `N`	Minute (0-59)
`SECOND`	`SS`, `S`	Seconds (0-59)
`MILLISECOND`	`MS`	Milliseconds (0-999)

Returns¶

An integer representing the number of date part units (e.g. years, days, months, hours, etc.) between date_expr2 and date_expr1.

Notes¶

Only the selected date part is used to calculate the difference. For example, the difference between '1997-12-31' and '1997-01-01' in years is 0, even though they are 364 days apart.
Negative values means that date_expr1 is after date_expr2.

Examples¶

For these examples, consider the following table and contents:

CREATE TABLE cool_dates(name TEXT, d DATE, dt DATETIME);

INSERT INTO cool_dates VALUES ('Marty McFly goes back to this time','1955-11-05','1955-11-05 01:21:00.000')
    , ('Marty McFly came from this time', '1985-10-26', '1985-10-26 01:22:00.000')
    , ('Vesuvius erupts', '79-08-24', '79-08-24 13:00:00.000')
    , ('1997 begins', '1997-01-01', '1997-01-01')
    , ('1997 ends', '1997-12-31','1997-12-31 23:59:59.999');

Find out how far past events are¶

In years¶

master=> SELECT d AS original_date, DATEDIFF(YEAR, CURRENT_DATE, d) AS "was ... years ago" FROM cool_dates;
original_date | was ... years ago
--------------+------------------
1955-11-05    |               -64
1985-10-26    |               -34
0079-08-24    |             -1940
1997-01-01    |               -22
1997-12-31    |               -22

In days¶

master=> SELECT d AS original_date, DATEDIFF(DAY, CURRENT_DATE, d) AS "was ... days ago" FROM cool_dates;
original_date | was ... days ago
--------------+-----------------
1955-11-05    |           -23408
1985-10-26    |           -12460
0079-08-24    |          -708675
1997-01-01    |            -8375
1997-12-31    |            -8011

In hours¶

Note

Use CURRENT_TIMESTAMP instead of CURRENT_DATE, to include the current time as well as date.
In this example, we use dt which is a DATETIME column

master=> SELECT CURRENT_TIMESTAMP as "Now", dt AS "Original datetime", DATEDIFF(HOUR, CURRENT_TIMESTAMP, dt) AS "was ... hours ago" FROM cool_dates;
Now                 | Original datetime   | was ... hours ago
--------------------+---------------------+------------------
2019-12-07 22:35:50 | 1955-11-05 01:21:00 |           -561813
2019-12-07 22:35:50 | 1985-10-26 01:22:00 |           -299061
2019-12-07 22:35:50 | 0079-08-24 13:00:00 |         -17008209
2019-12-07 22:35:50 | 1997-01-01 00:00:00 |           -201022
2019-12-07 22:35:50 | 1997-12-31 23:59:59 |           -192263