Shulou(Shulou.com)06/02 Report--

Today, I would like to talk to you about the MYSQL and PostgreSQL time zone issues, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something from this article.

Sometimes when you get used to using one thing, you are less likely to think about it, and you don't want to go there when something goes wrong. So the question of the time of MYSQL may belong to this list.

What are the problems with setting the time zone?

1 cross-regional companies

If it is a company across time zones, the transmission of the same record will have the following questions about the expression of time

1.1 do I express it in my local time or in the time of the place where the data comes from?

1.2 if my data is migrated to servers in other regions, can the time change?

We seldom consider such a problem because our company's business may only be in the same region at the same time, so this kind of problem is less considered. If it is a cross-regional company like Beijing and Urumqi, I think they should consider such a problem.

Let's do an experiment.

Server 1 represents UTC + 8 in Beijing

Server 2 represents UTC + 2 in Stockholm

We establish a replication relationship on the server. China is the main server and the slave library is in Stockholm.

There are two points to pay attention to when setting up a table.

1 whether you need time must be the time where your server is located

2 whether your time needs to change with different time zones

CREATE TABLE `titles` (

`emp_ no` int (11) NOT NULL AUTO_INCREMENT

`title`varchar (50) NOT NULL

`from_ date` datetime DEFAULT NULL

`to_ date` datetime DEFAULT NULL

`time_ s` timestamp NULL DEFAULT NULL

PRIMARY KEY (`emp_ no`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4

Let's insert some data and see what happens.

Main library

Slave library

Time from the library

1 date of type datetime, the data entered will not be changed

2 the date type of timestamp changes according to the time zone of different servers.

In addition, when using JAVA, if the server is CST, there will be problems when using JAVA JDBC for time insertion, which itself has nothing to do with the problem of JAVA and the CST of MYSQL and LINUX servers. Configuration needs to be done in JAVA to avoid problems.

At the same time, generally speaking, MYSQL is basically time_zone is system, and the time zone of the MySQL server is set to system, then the operating system time will affect the time value used by the MySQL server. Make sure your operating system is using the latest time zone information. The MYSQL time itself should be consistent with the server time, but if there are special needs, time_zone can also be enforced.

Command

Set global time_zone ='+ 08 00'

The time of MYSQL itself can be set dynamically.

Or set it directly in MY.CNF

Default-time_zone ='+ 8VR 00'

Then restart the server

The point is not to set up or not, but to unify your server.

Postgresql adjusts and views the time zone

1 View the settings of the current server

2 to view the time zones currently supported by POSTGRESQL, we choose Shanghai

3 set the current time zone

4 but the setting will be invalid when PSQL is turned off after setting, so the system will be set up, and PRC needs to be set.

5 restart POSTGRESQL after modification, the TIME ZONE of the time has been changed

After reading the above, do you have any further understanding of MYSQL and PostgreSQL time zone issues? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.