DATEDIFF and DATEDIFF_BIG Functions - SQL Server to PostgreSQL Migration

In SQL Server, the DATEDIFF and DATEDIFF_BIG functions get the datetime difference in specified units. In PostgreSQL, you can use EXTRACT function with various INTERVAL expressions.

It is very important to note that DATEDIFF does not return datetime difference in full units, so the conversion to PostgreSQL with exactly the same results requires some adjustments, see examples below.

In SQL Server, DATEDIFF returns INTEGER while DATEDIFF_BIG returns BIGINT.

To get the datetime difference in days:

SQL Server:

  -- Get difference in days
  SELECT DATEDIFF(dd, '2022-09-01', '2022-09-06'); 
  /* 5 */
 
  -- Get difference in days, but note that there is just 1 hour between datetimes!
  SELECT DATEDIFF(day, '2024-10-31 23:23', '2024-11-01 00:23');
  /* 1 */
 
  -- 1 year, 1 day, 1 hour difference (2024 is a leap year)
  SELECT DATEDIFF(day, '2023-10-30 23:23', '2024-11-01 00:23');
  /* 368 */

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 */
 
  -- Just 1 hour between two timestamps, and unlike DATEDIFF, 0 is returned as difference is in full days!
  SELECT EXTRACT(DAY FROM '2024-11-01 00:23'::TIMESTAMP - '2024-10-31 23:23'::TIMESTAMP); 
  /* 0 */
 
  -- Adding DATE_TRUNC helps get the same result as DATEDIFF
  SELECT EXTRACT(DAY FROM DATE_TRUNC('DAY', '2024-11-01 00:23'::TIMESTAMP) - 
                                            DATE_TRUNC('DAY', '2024-10-31 23:23'::TIMESTAMP));
  /* 1 */
 
  -- 1 year, 1 day, 1 hour difference (2024 is a leap year)
  SELECT EXTRACT(DAY FROM DATE_TRUNC('DAY', '2024-11-01 00:23'::TIMESTAMP) - 
                                            DATE_TRUNC('DAY', '2023-10-30 23:23'::TIMESTAMP));
  /* 368 */

In PostgreSQL, if you subtract one timestamp value from another using the minus (-) operator, you will get an INTERVAL value in the form 'ddd days hh:mi:ss.ff', so EXTRACT DAY still can be used even if the interval exceeds 1 month.

Note that SQL Server does not return the number of full years between two datetimes, it calculates the difference between the year parts only.

SQL Server:

  -- Get difference in years (1 year 1 day is actual difference between 2 dates below)
  SELECT DATEDIFF(year, '2022-12-31', '2024-01-01');
  /* 2 */

PostgreSQL:

  -- Get difference in years (note that the order of datetime values is different now!)
  SELECT EXTRACT(YEAR FROM AGE('2024-01-01', '2022-12-31')); 
  /* 1 */
 
  -- Trying to use the minus (-) operators always returns 0, see notes below
  SELECT EXTRACT(YEAR FROM '2024-01-01'::TIMESTAMP - '2022-12-31'::TIMESTAMP); 
  /* 0 */

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

PostgreSQL:

  -- Minus operator returns timestamp difference in days
  SELECT '2024-01-01'::TIMESTAMP - '2022-10-31'::TIMESTAMP; 
  /* 427 days */
 
  -- While AGE uses years, months and days
  SELECT AGE('2024-01-01', '2022-10-31');
  /* 1 year 2 mons 1 day */

To get the same result as SQL Server's DATEDIFF you can get the year parts from the timestamps using the DATE_PART function and subtract them as follows:

PostgreSQL:

  -- Extracting and then substracting years from two timestamps 
  SELECT DATE_PART('YEAR', '2024-01-01'::TIMESTAMP) - DATE_PART('YEAR', '2022-10-31'::TIMESTAMP);
  /* 2 */

Note that SQL Server does not return the number of full months between two datetimes, it calculates the difference between the month and year parts only.

SQL Server:

  -- Get difference in months (1 day is actual difference between 2 dates below)
  SELECT DATEDIFF(month, '2023-12-31', '2024-01-01');
  /* 1 */
 
  -- 1 year and 1 day is actual difference between 2 dates below
  SELECT DATEDIFF(month, '2022-12-31', '2024-01-01');
  /* 13 */

To get the same result as SQL Server's DATEDIFF you can take the difference in years, multiply by 12 and add the difference between month parts that can be negative:

PostgreSQL:

  -- First get difference in years and multiple by 12
  -- Then either add or subtract months (negative value)
  SELECT (DATE_PART('YEAR', '2024-01-01'::DATE) - DATE_PART('YEAR', '2022-12-31'::DATE)) * 12 +
              (DATE_PART('MONTH', '2024-01-01'::DATE) - DATE_PART('MONTH', '2022-12-31'::DATE));
  /* 13 */

In the example above, there is 1 year and 1 day difference between two timestamps. Years expression gives (2024 - 2022) * 12 = 24 and month expression gives 1 - 12 = -11, so we get 24 + (-11) = 13 months similar to SQL Server DATEDIFF.

Note that SQL Server does not return the number of full weeks between two datetimes, it calculates the difference between the week numbers only and the result depends on the starting day of the week as well (Sunday or Monday).

So the difference in weeks is not the difference in days divided by 7 in SQL Server.

SQL Server:

  -- Sunday is the first day (US English default)
  SELECT @@DATEFIRST;
  /* 7 */
 
  -- 1 day difference between dates, but first is Saturday, second is Sunday (new week started)
  SELECT DATEDIFF(week, '2024-11-02', '2024-11-03');
  /* 1 */
 
  -- Still 1 day difference but dates are on the same week, first is Sunday, second is Monday (the same week)
  SELECT DATEDIFF(week, '2024-11-03', '2024-11-04');
  /* 0 */

In SQL Server, January 1st of any year is always week 1 (it is not always correct according to the ISO standard), and there are some edge cases when you can get unexpected results when the week numbers are different, for example:

SQL Server:

  -- Week 53 for December 31st, 2023 and Week 1 for January 1st, 2024
  SELECT DATEPART(week, '2023-12-31'), DATEPART(week, '2024-01-01')
  /*  53   1 */ 
 
  -- But difference in weeks is still 0 because dates are on the same week (first is Sunday, second is Monday)
  SELECT DATEDIFF(week, '2023-12-31', '2024-01-01');
  /* 0 */                
 
  -- Week 53 for December 31st, 2022 and Week 1 for January 1st, 2023
  SELECT DATEPART(week, '2022-12-31'), DATEPART(week, '2023-01-01')
  /*  53   1 */        
 
  -- Difference in weeks is now 1 because first is Saturday, second is Sunday (next week)
  SELECT DATEDIFF(week, '2022-12-31', '2023-01-01');
  /* 1 */

In SQL Server, December 31st is almost always 53th week except years 1972, 2000, 2028 and so on (every 28th year) where December 31st is 54th week.

PostgreSQL uses the ISO week numbers only where the largest week number can be either 52 or 53, and January 1st can be 1st, 52nd or 53rd week.

PostgreSQL:

  -- January 1st, 2021 is week 53
  SELECT DATE_PART('WEEK', '2021-01-01'::DATE);
  /* 53 */
 
  -- January 1st, 2022 is week 52
  SELECT DATE_PART('WEEK', '2022-01-01'::DATE);
  /* 52 */  
 
  -- January 1st, 2024 is week 1
  SELECT DATE_PART('WEEK', '2024-01-01'::DATE);
  /* 1 */

For this reason, it is hard to get the same result as SQL Server's DATEDIFF(week) using a reasonable expression and without using a user-defined function that covers all edge cases.

To get a quite close but not exactly the same for all possible date combinations, and to take into account multi-year intervals as well as intervals that cross the calendar year, you can use the following expression in PostgreSQL:

PostgreSQL:

  -- Get number of weeks between 2023-07-31 and 2024-07-24
  SELECT (DATE_PART('YEAR', '2024-07-24'::DATE) - DATE_PART('YEAR', '2023-07-31'::DATE)) * 52 +
              (DATE_PART('WEEK', '2024-07-24'::DATE) - DATE_PART('WEEK', '2023-07-31'::DATE));
  /* 51 */

In the example above, years expression gives (2024 - 2023) * 52 = 52 and week expression gives 30 - 31 = -1, so we get 52 + (-1) = 51 weeks.

Note that SQL Server does not return the number of full hours between two datetimes, it calculates the difference between the hour parts only.

SQL Server:

  -- Get difference in hours (2 minutes is actual difference between 2 datetimes below)
  SELECT DATEDIFF(hour, '2024-11-02 13:59', '2024-11-02 14:01');
  /* 1 */

To get the same result as SQL Server's DATEDIFF you can use the following expression:

PostgreSQL:

  -- Get difference in hours 
  SELECT TRUNC(EXTRACT(EPOCH FROM DATE_TRUNC('HOUR', '2024-11-02 14:01'::TIMESTAMP) - 
                                                            DATE_TRUNC('HOUR', '2024-11-02 13:59'::TIMESTAMP))/3600);
  /* 1 */

First we truncate datetimes to hours precision, get the interval between two truncated datetimes in seconds and then get hours dividing by 3600.

Note that SQL Server does not return the number of full minutes between two datetimes, it calculates the difference between the minute parts only.

SQL Server:

  -- Get difference in minutes (2 seconds is actual difference between 2 datetimes below)
  SELECT DATEDIFF(minute, '2024-11-02 13:10:59', '2024-11-02 13:11:01');
  /* 1 */

To get the same result as SQL Server's DATEDIFF you can use the following expression:

PostgreSQL:

  -- Get difference in minutes 
  SELECT TRUNC(EXTRACT(EPOCH FROM DATE_TRUNC('MINUTE', '2024-11-02 13:11:01'::TIMESTAMP) - 
                                                            DATE_TRUNC('MINUTE', '2024-11-02 13:10:59'::TIMESTAMP))/60);
  /* 1 */

First we truncate datetimes to minutes precision, get the interval between two truncated datetimes in seconds and then get minutes dividing by 60.

Note that SQL Server does not return the number of full seconds between two datetimes, it calculates the difference between the second parts only.

SQL Server:

  -- Get difference in seconds (200 milliseconds is actual difference between 2 datetimes below)
  SELECT DATEDIFF(second, '2024-11-02 13:11:11.900', '2024-11-02 13:11:12.100');
  /* 1 */

To get the same result as SQL Server's DATEDIFF you can use the following expression:

PostgreSQL:

  -- Get difference in seconds 
  SELECT TRUNC(EXTRACT(EPOCH FROM DATE_TRUNC('SECOND', '2024-11-02 13:11:12.100'::TIMESTAMP) - 
                                                            DATE_TRUNC('SECOND', '2024-11-02 13:11:11.900'::TIMESTAMP)));
  /* 1 */

First we truncate datetimes to seconds precision, get the interval between two truncated datetimes in seconds and truncate the value to get the result as an integer number.

In SQL Server you can get the difference between two datetimes in milliseconds.

SQL Server:

  -- Get difference in milliseconds 
  SELECT DATEDIFF(ms, '2024-11-02 13:11:11.900', '2024-11-02 13:11:13.100');
  /* 1200 */

To get the same result as SQL Server's DATEDIFF you can use the following expression:

PostgreSQL:

  -- Get difference in milliseconds 
  SELECT TRUNC(EXTRACT(EPOCH FROM '2024-11-02 13:11:13.100'::TIMESTAMP - 
                                                            '2024-11-02 13:11:11.900'::TIMESTAMP) * 1000);
  /* 1200 */

First we get the difference in seconds and microseconds as a number with the fractional part, multiple by 1000 to get milliseconds in the integer part, and truncate the remaining fraction.

For more information, see SQL Server to PostgreSQL Migration.