SQL DATEDIFF — Now & Then

Relational databases store information in table cells — in columns that describe aspects of an item and rows which tie together the columns. SQL elapsed-time calculations for DATE, DATETIME, and TIMESTAMP data types are done with DATEDIFF function, with the following syntax:

DATEDIFF( date_expression_1, date_expression_2 );

Any valid date or date-time values are acceptable. Only the date portion of DATETIME or TIMESTAMP values are used, regardless of any time portion..

For background context, please check out our SQL Dates & Times blog entry.

DATEDIFF Excludes The Ending Date

DATEDIFF returns the exclusive relationship of date_expression_1 to date_expression_2 (that is to say, not including the ending day). To be as clear on this oft-confusing point, the number of days between the first and third of January is:

SELECT
   DATEDIFF( '2021-01-03', '2021-01-01' )
   AS 'Days From 1 and 3 January' ;

DATEDIFF Between Dates

To get to Cinco de Mayo from New Year’s Day:

SELECT
  DATEDIFF( '2021-05-05', '2021-01-01' )
  AS "From New Year’s Day to Cinco de Mayo" ;

It’s 124 days from New Year’s Day until Cinco de Mayo begins.

SELECT
  DATEDIFF( '2021-01-01', '2021-05-05' )
  AS "From Cinco de Mayo to New Year’s Day" ;

The negative number shows that New Year’s Day is 124 days before Cinco de Mayo.

DATEDIFF Ignores Time Information — Use TIMEDIFF

As its name suggests, DATEDIFF ignores any time portion supplied. The related function, TIMEDIFF, honors time portions, returning a sting with the difference, as shown here:

SELECT
  DATEDIFF('2000:01:01 00:00:01', '2000:01:01 00:00:00')
    AS 'DATEDIFF ignores time',
  TIMEDIFF('2000:01:01 00:00:01', '2000:01:01 00:00:00')
    AS 'TIMEDIFF honors time';

Note that TIMEDIFF doesn’t calculate negative (before) times; instead it reports a misleading “bad time format” error if argument 2 is later than argument 1.

Using DATEDIFF To Shape Results

In addition to using DATEDIFF to output elapsed times, it’s useful for restricting rows returned from a SQL Select. In the following example DATEDIFF is used to filter a user’s orders to those which took more than ten days between ordering and shipment.

SELECT order, order_time, ship_time
FROM purchases
WHERE DATEDIFF(order_time, ship_time) > 10;  

Use TIMESTAMPDIFF For Specific Units of Time

DATEDIFF and TIMEDIFF are great if their output formats are what’s needed. TIMESTAMPDIFF is suited for specific units of elapsed time. The following example shows how to get a difference in each of the supported units:

SET @start = '2023-01-01 00:00:00';
SET @stop = '2023-12-31 23:59:59' ;
SELECT
  timestampdiff(MICROSECOND, @start, @stop) AS 'microseconds',
  timestampdiff(SECOND, @start, @stop) AS 'seconds',
  timestampdiff(MINUTE, @start, @stop) AS 'minutes',
  timestampdiff(HOUR, @start, @stop) AS 'hours',
  timestampdiff(DAY, @start, @stop) AS 'days',
  timestampdiff(WEEK, @start, @stop) AS 'weeks',
  timestampdiff(MONTH, @start, @stop) AS 'months',
  timestampdiff(QUARTER, @start, @stop) AS 'quarters',
  timestampdiff(YEAR, @start, @stop) AS 'years';

So Many Date and Time Functions

Because time is such a central part of our experience, there are many date and time functions available. The feature set and syntax vary by databases, so becoming familiar with the appropriate documentation is vital. This is the very lengthy MySQL Date and Time manual page.

Conclusion

We’ve expanded upon our SQL Dates & Times blog entry with examples of calculating the difference between dates and times with DATEDIFF, TIMEDIFF,  and TIMESTAMPDIFF, along with a quick aside on how to use these functions to shape the results set from a SQL Select statement.

Start Learning

To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.