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 SQream DB 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 VARCHAR(40), 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