How to calculate the difference between two dates in SQL

Problem

You need to find out how to calculate the difference between two dates in SQL.

Sample Data

For the purpose of this tutorial, we will use a hypothetical table named orders. This table has two columns: order_id (integer) and order_date (date).

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date DATE
);

INSERT INTO orders (order_id, order_date) 
VALUES 
  (1, '2021-01-01'),
  (2, '2021-02-01'),
  (3, '2021-03-01'),
  (4, '2021-04-01'),
  (5, '2021-05-01');

The task is to calculate the number of days between the order dates of order_id 1 and order_id 2.

MySQL Solution

SELECT 
  DATEDIFF((SELECT order_date FROM orders WHERE order_id = 2), 
  (SELECT order_date FROM orders WHERE order_id = 1)) AS days_diff;

Explanation (MySQL)

In MySQL, the DATEDIFF() function is used to find the difference between two dates. It returns the difference in days from the second date to the first date. In the provided solution, two subqueries are used to get the order_date of the two orders, and then the DATEDIFF() function is applied to these dates.

PostgreSQL Solution

SELECT 
  (SELECT order_date FROM orders WHERE order_id = 2) - 
  (SELECT order_date FROM orders WHERE order_id = 1) AS days_diff;

Explanation (PostgreSQL)

In PostgreSQL, the difference between two dates can be calculated by simply subtracting one date from another. This operation returns the number of days between the two dates. Similar to the MySQL solution, two subqueries are used to get the order_date of the two orders, and then these dates are subtracted from each other.