Brazilian Time Zone in MySQL

Posted on

Question :

I have a MySQL database table named registros .

In it, I have the following structure:
id_registro , nome_registro , data_registro .

In data_registro I’m using current_timestamp (), fetching the time from the server at the time of adding. The question is: This time is not the same as Brazil, because it is on a US server.

How do I do that when adding to the database, insert the Brazilian time? (4 hours difference).


Answer :

Change the global time zone variable:

SET @@global.time_zone = '+3:00';

The variable time_zone can be set directly on the MySQL client console.
Check your current time with the NOW () function before you start playing:


Then change the global time zone variable and disconnect from the server:

SET @@global.time_zone = '+3:00';

You need to go out and log back into your MySQL session to see the effects.
Once you restart your MySQL session, check the current time again:


If you have the zone database of zone names properly configured, you can refer to the zones by their names. Here’s an example:

SET time_zone='America/Sao_Paulo';

SELECT @@time_zone;

To be able to refer to a time zone zone name, such as ‘Brazil / DeNoronha’, you must have the timezones name database correctly configured. If you have questions about this, or you are not able to use the names of the spindle zones, read this article .
To see all the valid spindles for Brazil, for example, use the following query:

SELECT * FROM mysql.time_zone_name WHERE Name LIKE '%Brazil%';

See more details in this answer .


Add date_default_timezone_set('America/Sao_Paulo'); at the start of your PHP code, so the timezone will be correctly configured. Remember that you have to add in the page that does insert in MySQL.


Leave a Reply

Your email address will not be published. Required fields are marked *