Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "the abbreviation of mysql time zone and the introduction of related operations". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. time zone abbreviation

UTC is the abbreviation of coordinated Universal time (Universal Time Coordinated). It is a second-based time scale prescribed and recommended by the International Radio Advisory Committee and maintained by the International time Bureau (BIH). UTC is equivalent to the mean solar time on the prime meridian (longitude 0), which used to be expressed as Greenwich mean time (GMT). Beijing time is 8 hours earlier than UTC time. Take 0000UTC on January 1, 1999 as an example, UTC time is zero and Beijing time is 8: 00 a.m. on January 1, 1999.

GMT (Greenwich Mean Time) is Greenwich mean time:

Because the earth's orbit is not circular, and its speed varies with the distance between the earth and the sun, there is a lack of uniformity when looking at the sun. The length of the apparent solar day is also affected by the inclination of the earth's rotation axis relative to the orbital plane. In order to correct the above inhomogeneity, astronomers calculate the effect of the earth's non-circular trajectory and polar tilt on looking at the sun. Mean solar time refers to the revised apparent solar time. The mean solar time on the Greenwich meridian is called Universal time (UT0), also known as Greenwich mean time (GMT). To ensure that the difference between coordinated Universal time and Universal time (UT1) is no more than 0.9 seconds, positive or negative leap seconds are added to coordinated Universal time if necessary. Therefore, there is a difference of several integer seconds between coordinated Universal time and International Atomic time (TAI). The International Central Bureau of Earth rotation (IERS) in Paris is responsible for deciding when to join the leap second.

In other words, UTC is basically equal to GMT.

CET

(English: Central European Time,CET) Central European time is one of the names of time zones one hour earlier than Universal Standard time (UTC). It is adopted by most European countries and some North African countries. The winter time is UTC+1 and the summer European Daylight time is UTC+2.

CEST

Central European Daylight time (CEST) is one of the names of time zones that are two hours earlier than Universal Standard time (UTC). It is adopted by most European countries and some North African countries in summer (UTC+2) and in winter by these countries using Central European time (UTC+1).

CST

CST can also represent the following four different time zones:

Central Standard Time (USA) UTC-6:00

Central Standard Time (Australia) UTC+9:30

China Standard Time UTC+8:00

Cuba Standard Time UTC-4:00

It can be seen that CST can simultaneously represent the standard time of the United States, Australia, China and Cuba. That is, this needs to determine which time zone is used based on the scenario used.

Online time zone conversion tool website http://www.timebie.com/timezone/centraleuropeansummerbeijing.php

Second, the time zone of MYSQL

View

Mysql > select @ @ global.time_zone,@@session.time_zone,@@global.system_time_zone +-- + | @ @ global.time_zone | @ @ session.time_zone | @ @ global.system_time_zone | +-- -+ | SYSTEM | SYSTEM | CST | +-+- +-+ 1 row in set (0.00 sec) mysql > show variables like'% time_zone%' +-+-+ | Variable_name | Value | +-+-+ | system_time_zone | CST | | time_zone | SYSTEM | +-+-+ 2 rows in set (0.00 sec) mysql > SELECT TIMEDIFF (NOW (), UTC_TIMESTAMP) +-- + | TIMEDIFF (NOW () UTC_TIMESTAMP) | +-+ | 08:00:00 | +-- + 1 row in set (0.00 sec)

SYSTEM means to use "OS time zone"; CST, where cst is "China Standard time + 8:00 zone"

You can also view it under OS, which is "East 8":

[root@mydb31] # date-RFri, 14 Dec 2018 11:08:34 + 0800 [root@mydb31] # ll / etc/localtimelrwxrwxrwx. 1 root root 35 Aug 17 13:26 / etc/localtime->.. / usr/share/zoneinfo/Asia/Shanghai [root@mydb31 ~] # cat / etc/localtime TZif / pCDTCSTTZif2 / Cp$Gg%_?&?+ / Cp$Gg%_?&?+ (? Q LMTCDTCSTCST-8)

You can see how to modify the OS time zone

Ln-sf / usr/share/zoneinfo/Asia/Shanghai / etc/localtime

2. Time zone system table of mysql

Several system tables in the mysql system library that are used to maintain the time zone, empty by default

You can use shell > mysql_tzinfo_to_sql / usr/share/zoneinfo | mysql-u root mysql to load

3. Modify the time zone of the mysql server

Method 1: modify the global variable

Set global time_zone ='+ 8 set global time_zone 0000 hours; # # modify the global time zone of mysql to Beijing time, that is, the East 8 Zone where we live

Set time_zone ='+ 8virtual; # # modify the time zone of the current session

Flush privileges; # effective immediately

Method 2: modify the time zone by modifying the my.cnf configuration file

# vim / etc/my.cnf # # add to the [mysqld] area

Default-time_zone ='+ 8VR 00'

# / etc/init.d/mysqld restart # # restart mysql to make the new time zone effective

IV. Timestamp and datetime time types

Timestamp type: the time will be automatically converted to gmt time storage time, and when taken out, it will be automatically converted to the server's time zone, so you do not need to specify a time zone (default East Zone 8)

Datetime type: there is no concept of time zone. For a database with transnational business, it is uniformly converted to gmt format when it is stored, and the specified time zone is displayed when it is taken out.

This is the end of the introduction of the abbreviation of mysql time zone and related operations. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.