I am using CakePHP 4.4 on a server with PHP 8.1. And the database is MariaDB 10.5
The data type of some of the fields in the database is TIMESTAMP. My Application timezone is set to ‘America/Toronto’ and the locale is en_CA.
In the database the TIMESTAMP fields are storing time 4 hours behind the actual application (EDT) time. Can anyone help me understand why this is happening? How can I fix this? How can I store the EDT time in the database as it is?
You generally don’t actually want to store EDT time in the database. You want to store UTC time there (which will be 4 hours ahead of EDT, e.g. if you want 10am EDT, it’ll be 2pm UTC). Then, make sure that your application timezone is set correctly (seems like it is), and that anything else you use to connect to the database (e.g command line tools, etc.) is also set to use the same timezone, and everything should be transparent for you. But then when you decide you need to display “local time” for people in other timezones, you just set the timezone for their connection, and tada it’s all correct without any extra math on your part.
Thank you @Zuluru . My Application timezone is set to ‘America/Toronto’ and the database server timezone is set to 'America/New_York.
From the MariaDB docs, the TIMESTAMP datatype converts the time to UTC when inserting a new row into the database. But in my case, when inserting a new row the time stored in the TIMESTAMP field is 4 hours behind my current time. For example, if I insert a new row at 10 AM EDT, the time stored in database is 6 AM EDT.
Are you inserting as a string, or as something like a FrozenTime object? If the latter, can you show the debug output of that object before you save it?
Also, how are you “seeing” the 6am value in the database? In a view of your application, or a command-line query?
I am inserting it as a FrozenTime object.
Below is the debug output before I save it:
Cake\I18n\FrozenTime Object ( [date] => 2023-06-08 10:11:23.224384 [timezone_type] => 3 [timezone] => America/Toronto )
I am seeing the 6am value (EDT-4 hours) in the database only. In the view of the application, I see the EDT value. I am using the nice() method in view to display the datetime.
When you say “in the database only”, you mean you’re connecting to it with command line tools or phpMyAdmin or the like? That connection might have its own idea of what the timezone is?
I insert this data into the database from an application page. i.e., I submit a page, and then this time get inserted into the database. I checked the database server time, it is set to ‘America/new_York’
Not sure if you’re not understanding my questions, or I’m not understanding your answers. If you connect directly with mysql [database name] on the command line and do:
SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone, now(), [your table.timestamp column];
what result do you get?
Sorry if I am not clear. When I run the query SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone, now(), I get
When I include the last part in the query, I get an error.
[your table.timestamp column] should be like users.created or articles.publish_at or whatever the column in question is for you. You haven’t said anywhere what the table or column name in question is, so I used a placeholder.
Thank you, I got that. But including that part in the query gives me an error
If I run SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone, now(), [users.created]; I get error
Not [users.created], just users.created. The [] was to indicate it’s a placeholder, not part of the syntax.
Right, that is also giving me an error.
The error is : Unknown table 'users' in field list
Right, where was my head when I wrote that? It’s been quite a week.
select [name of timestamp field] from [name of table with timestamp field] where id = [id of a record that you know the actual creation time of];
Thank you, and sorry for the late reply. I figured out what the problem was.
In config/app.php Datasources settings, I had to comment out
'timezone' => 'UTC' so that it is set in php.ini