If the column creation_date_time is a TIMESTAMP, the value is internally stored Universal Coordinated Time (UTC) but when it is retrieved it will be converted to the current session time zone, which will be the server's default time zone unless you have set the session time zone to be something different. When you assign this value, $row0['creation_date_time'] to a PHP DateTime without explicitly setting a time zone, it will use UTC.
So to be consistent and assuming you want to use a specific time zone, you should explicitly set a session time zone for your MySql connection. So a person in New York might do the following:
set session time_zone = 'America/New_York'; /* MySql */
And in PHP:
$date = new DateTime($row0['creation_date_time'], new DateTimeZone('America/New_York'));
Alternatively, if you do not want to create a DateTime with anything other than UTC, then you should at least set your MySql session time zone to UTC prior to retrieving the TIMESTAMP. You will then have consistent time zones but not necessarily the displayable value you want.
For example, if an event occurred on 7/1/20 at 5am in New York and a timestamp of this was kept in a TIMESTAMP column, internally it would be stored as '2020-07-01 01:00:00' because there is a 4 hour difference. This will be independent of the current session time zone in effect when the event occurred assuming that the value of NOW() or CURRENT_TIMESTAMP() was used to store the timestamp (if, however, you stored a literal into a TIMESTAMP column, it would be interpreted in the current session's time zone before being converted to UTC). When I retrieve the value, I would like it, however, to reflect "local" time. So, I need to set the session time zone to America/New_York prior to fetching the column if I wish for the retrieved value to be '2020-07-01 05:00:00'. If I assign this to a PHP DateTime without setting the appropriate time zone, it will display as 5am but with the UTC time zone, and that is not really correct.
I am not sure if all of the above really answered your question, so let me tell you the approach I would take in general not having knowledge or your actual circumstances:
I would not attempt to update the timestamp explicitly using a PHP initialized variable but would instead allow MySql to initialize on creation and/or updating depending on your requirements:
UPDATE my_table set creation_date_time = now(), etc.
The issue is that when you set a TIMESTAMP column with a literal value, it will be interpreted in whatever the current session time zone is and without setting it explicitly, this becomes non-deterministic unless you have control over the system time zone (applications also migrate to different ISPs and therefore time zones, at least in my country). Even though you will be setting the session time zone explicitly if you follow my advice, it is far simpler to just let MySql do the initialization.
I would then ensure that the first thing you do after connecting is to set the local session timezone:
set session_time_zone = 'Europe/Berlin';
And then your PHP code is:
$date1 = new DateTime($row0['creation_date_time'], new DateTimeZone('Europe/Berlin'));
$date1 = $date1->modify("+1 month");
$date2 = new DateTime("now", new DateTimeZone('Europe/Berlin'));
if ($date1 <= date2) {
// expired
}
UPDATE
If the current MySql server time were 'Europe/Berlin', then when you did a SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) query, I would expect a result of '02:00' not '05:30' that you did get, as evidenced by the following:
mysql> set session time_zone = 'Europe/Berlin';
Query OK, 0 rows affected (0.19 sec)
mysql> SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
+--------------------------------+
| TIMEDIFF(NOW(), UTC_TIMESTAMP) |
+--------------------------------+
| 02:00:00 |
+--------------------------------+
1 row in set (0.00 sec)
So it appears that whatever the MySql server time is, it is 03:30 hours east of Berlin. Now the timestamps you are comparing are:
From MySql:
DateTime Object ( [date] => 2020-07-25 15:31:46.000000 [timezone_type] => 3 [timezone] => Europe/Berlin )
From PHP:
DateTime Object ( [date] => 2020-07-25 12:40:33.451120 [timezone_type] => 3 [timezone] => Europe/Berlin )
Now it's a little difficult to compare these two precisely. If you created the PHP timestamp simultaneously with the MySql timestamp and the MySql timestamp was created using a MySql function such as NOW() (in which case the TIMESTAMP would be internally correct), then I would expect the MySql timestamp to be 03:30 later than the PHP timestamp (because of the timezone in effect when the TIMESTAMP is being converted back from UTC). But if you actually created the PHP timestamp about 40 minutes later, then what you show is more or less what I would expect to see. You tell me if 40 minutes is closer to the elapsed time between the two events. Assuming it is, then this also tells me that you have been initializing the TIMESTAMP column with either NOW() or its equivalent CURRENT_TIMESTAMP, which would be a very good thing because internally the timestamp would be correct and your only problem is when you retrieve it you are getting it converted to the wrong timezone. But this can be remedied by setting the session timezone immediate following connection:
set session time_zone = 'Europe/Berlin'
Update 2
If you cannot set the above timezone, you are missing timezones from your MySql installation and you need to load them (the reason you see that timezone on your PHP size is because PHP has the timezone and you are explicitly loading it into your DateTime object).
URL https://dev.mysql.com/downloads/timezones.html has files of timezones. Pick the POSIX version appropriate for your MySql release and download it to your PC.
URL https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html has the instructions for loading this file. Read this and make sure you understand what you are doing (somebody has to take responsibility). In short, you will be executing:
mysql_tzinfo_to_sql path-to-downloaded-timezones-file | mysql -u admin-user-id -p admin-password
After all of this, if you still feel like doing some light reading, see What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types, specifically the answer given by me (Booboo).
