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