Brazilian Time Zone in Mysql

Asked

Viewed 21,084 times

6

I have a Mysql database table called registros.

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

In data_registro i am using the current_timestamp(), searching the server time when adding. The question is: This time is not the same as Brazil, because it is on a US server.

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

mysql sql

  • Show me what you tried to do...

  • I have nothing, because the system time is correct, if I give echo date("d/m/Y H:i:s") works normal, but my doubt is about SQL itself, how would I do? I wanted to change something inside php admin or php.ini to make this change automatically, so I didn’t need to manipulate the data when inserting.

  • You need to explain this one better to searching the server time to add. The question is: This time is not the same as Brazil, because it is on a US server

  • data_record could be used now(), for example.

  • At the time of insertion, I pass the empty field, so that it enters the mysql current_timestamp() already

  • mysql doesn’t know the time zone from here, so it puts the time differently

  • And how I manipulate it?

  • SUBDATE(now(), INTERVAL 3 hour put this

  • Where would I put it? I don’t quite understand..

  • where you put it on the bench, put it

  • Strange you talk about "Brazil time", Brazil has tracks of the territory with 4 different time zones: UTC - 5 hours, UTC - 4 hours, UTC - 3 hours (Brasilia time), UTC - 2 hours.

Show 6 more comments

2 answers

6


Change the global time zone variable:

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

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

SELECT NOW();

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

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

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

SELECT NOW();

If you have the appropriate time zone name database configured, you can refer to the zones by their names. Take an example:

SET time_zone='America/Sao_Paulo';

SELECT @@time_zone;

In order to be able to refer to a time zone name, such as Brazil/Denoronha, you need to be with the timezone name database correctly configured. If you have questions about this subject, or are unable to use zone names, read this article. To view all valid spindles for Brazil, for example, use the following query:

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

See more details in that reply.

  • 1

    For me to do this, I need super global access right? root access

  • Yes

  • @But if the client does not have such a privilege, as he does?

  • 1

    Then I guess you have to ask to run this command on the server.. I think.. @Talesbreno

2

Add date_default_timezone_set('America/Sao_Paulo'); at the beginning of your PHP code, timezone will be correctly configured. Remembering that you have to add on the page that makes the insert in Mysql.

  • This changes the date in PHP?

  • @GOKUSSJ4 no.. that you put in the file . php that has the INSERT INTO table... This code you add in the same PHP file

  • Cool, I didn’t know you could do it that way either, +1

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.