DATEDIFF Function - SQL Server to PostgreSQL Migration

In SQL Server, you can use DATEDIFF function to get the datetime difference in specified units. In PostgreSQL, you have to use various INTERVAL expressions.

SQL Server:

  -- Get difference in days
  SELECT DATEDIFF(dd, '2022-09-01', '2022-09-06'); 
  # 5

PostgreSQL:

  -- Get difference in days (note that the order of datetime values is different now!)
  SELECT EXTRACT(DAY FROM '2022-09-06'::TIMESTAMP - '2022-09-01'::TIMESTAMP); 
  # 5

To get the datetime difference in years:

SQL Server:

  -- Get difference in years
  SELECT DATEDIFF(year, '2022-09-01', '2023-10-31'); 
  # 1

PostgreSQL:

  -- Get difference in years (note that the order of datetime values is different now!)
  SELECT EXTRACT(YEAR FROM AGE('2023-10-31', '2022-09-01')); 
  # 1

Note that we cannnot use the minus operator in PostgreSQL as it always returns the difference in days so we cannot extract YEAR part:

PostgreSQL:

  -- Minus operator returns timestamp difference in days
  SELECT '2023-10-31'::TIMESTAMP - '2022-09-01'::TIMESTAMP; 
  # 425 days
 
  -- While AGE uses years, months and days
  SELECT AGE('2023-10-31', '2022-09-01');
  #  1 year 1 mon 30 days

For more information, see PostgreSQL DATEDIFF - How to and SQL Server to PostgreSQL Migration.