How to change the server time zone without affecting already inserted data?

Question

I have a PHP project with Mysql database. The problem is the time related data is showing wrong according to my requirements.

I want the timezone should be set to "Time Zone Port louis,Mauritius GMT+04:00".

How to update the timezone so that every data in the database shows the Mauritius timezone? And where to update the timezone?

And if I change the timezone, does the already inserted data automatically updated? Or else how to update the timezones of every record in the database?

Answer 1

you have 3 solutions:

• you can change the server configuration if you have a dedicated server.
• change yourself at the level of your applictaion with date_default_timezone_set
• change yourself at the global level of your php configuration in php.ini

Answer 2

It is generally a good idea to keep your server in UTC, so that security logs align between computers.

When storing timestamps in a database, it is recommended to use a Timestamp field that includes a timezone. This means that the timezone used by the server is irrelevant.

Also, please note that time conversions are often performed by the SQL Client based upon the locale of the computer that is running the SQL Client. This can often lead to confusion because the time seen in output within the SQL client does not actually match the times stored in columns. (A good way to test this is to cast the field into a Text field, to see the 'raw' value in the field without it being converted by the SQL client.)

I worked in a company that used to set their server to the timezone where the company was located, but as the company grew bigger and they had servers in multiple locations, it became a problem. Thus, it can be better to keep all computers in UTC and specifically convert times when they are being displayed.