I have a problem when the server is down and when it is finished processing, the old data in the database is time-shifted.
column information: created_at - timestamp
i use Laravel 11 + mariadb, php8.4,
in Laravel i have set Timezone of our country
i am very sure about the time because before that i still check database daily and keep the record data with the time of the timezone i set
For example, the record is at 12 noon but it is recorded as 18:00 pm.
i tried to set timezone in mariadb and reformat with laravel for timezone +7 but the time is completely wrong
=== update: i double checked my old data before down is not affected, but the new records after server up are wrong
This can, as you have discovered, be confusing.
TIMESTAMP column contents are always translated from the local timezone setting in MySQL / MariaDB to UTC when they are stored. And they are always translated back upon retrieval. An example of such a timestamp is NOW(). So you can say SELECT NOW() and you'll get the current time in whatever timezone is currently set as the server time zone.
So, if your database server had its global time zone set to Asia/Bangkok (UTC+7) for a while, then had it set to UTC for a while, you'll have TIMESTAMP values that vary by seven hours. The global time zone setting of the server might have changed when you did your disaster recovery work.
I think it's wise to have your application database's access layer always, on initialization, do
SET time_zone = <<the current user's preference for time zone>>
when you use TIMESTAMPs. That way you get a nice time-zone-aware app.