SQL

Calculate Time Differences Between Dates

Precisely compute the difference between two date or timestamp columns in days, months, or years, essential for age calculations and duration tracking.

SELECT
  id,
  start_date,
  end_date,
  EXTRACT(EPOCH FROM (end_date - start_date)) / (60 * 60 * 24) AS days_diff,
  EXTRACT(YEAR FROM AGE(end_date, start_date)) AS years_diff,
  CONCAT(FLOOR(EXTRACT(EPOCH FROM (end_date - start_date)) / 3600), ' hours ', 
         FLOOR(MOD(EXTRACT(EPOCH FROM (end_date - start_date)), 3600) / 60), ' minutes') AS duration
FROM events;

How it works: This SQL snippet demonstrates how to calculate time differences between two date/timestamp columns, using PostgreSQL as an example. `EXTRACT(EPOCH FROM (end_date - start_date))` provides the total difference in seconds, which can be converted to days. The `AGE()` function directly computes a precise age or duration, from which specific units like years can be extracted. Custom calculations using `FLOOR` and `MOD` are shown for detailed duration breakdowns into hours and minutes.

Calculate Time Differences Between Dates

Need help integrating this into your project?