Discrepancy in time difference calculations between PHP, PostgreSQL, and website

Question

I need to calculate the time difference between two datetime (start datetime: 2023-04-06 09:17:37 and end datetime: 2022-11-17 11:24:30).

I have different results based on a SQL request (PostgreSQL) and PHP and a website. I don't know which one has the right interval.

PHP : https://onlinephp.io/c/e37a4

<?php
$datetime1 = new DateTime('2023-04-06 09:17:37');
$datetime2 = new DateTime('2022-11-17 11:24:30');
$interval = $datetime1->diff($datetime2);
$elapsed = $interval->format('%y years %m months %d days %h hours %i minutes %s seconds');
echo $elapsed;

Result :

0 years 4 months 18 days 21 hours 53 minutes 7 seconds

---

PostgreSQL : https://www.db-fiddle.com/f/va9Zify9F2Gi1T6qUFqs11/0

SELECT age('2023-04-06 09:17:37', '2022-11-17 11:24:30')

Result :

age
{"months":4,"days":18,"hours":21,"minutes":53,"seconds":7}

---

Website : https://www.timeanddate.com/date/timezoneduration.html?d1=17&m1=11&y1=2022&d2=6&m2=4&y2=2023&h1=11&i1=24&s1=30&h2=9&i2=17&s2=37

Result :

4 months, 19 days, 20 hours, 53 minutes, 7 seconds

Which of the above results is the correct time difference between the two datetimes?

Answer 1

The short answer is, they're all giving the correct result.

In PostgreSQL, the age function does not take account of daylight savings time (see the example here).

In PHP, on the sandbox you are using, the default timezone is UTC (check $datetime1->getTimezone()->getName();), and so there is no daylight savings involved in the computation.

Thus for both those cases, the time difference is 4 months 18 days 21 hours 53 minutes 7 seconds.

In the website page, you have set the location to Paris. The timezone for Paris (Central European Time) changed to summer time on March 26th, 2023 (the time went forward an hour), and so there is one hour less in the computation of the time difference, and it is 4 months 18 days 20 hours 53 minutes 7 seconds.