Why MySQL can ignore time zone issues when using timestamp?

Why MySQL can ignore time zone issues when using timestamp?

I have always wondered why the MySQL database timestamp can ignore the time zone issue.
I have been using the Laravel framework in my business, and the built-in Migration also uses timestamp type fields, so I don't care too much about it.

start

View the current database time zone

mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | +08:00 |
+------------------+--------+
2 rows in set (0.30 sec)

View table structure

mysql> desc timestamp_test;
+--------------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| created_time | datetime | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
+--------------+-----------+------+-----+---------+----------------+
3 rows in set (0.26 sec)

Inserting Data

mysql> insert into timestamp_test(created_time, created_at) values('2020-12-09 08:00:00', '2020-12-09 08:00:00');
Query OK, 1 row affected (0.22 sec)


mysql> select * from timestamp_test;
+----+---------------------+---------------------+
| id | created_time | created_at |
+----+---------------------+---------------------+
| 1 | 2020-12-09 08:00:00 | 2020-12-09 08:00:00 |
+----+---------------------+---------------------+
1 row in set (0.06 sec)

This time seems to be correct, so let's try to change the time zone and insert the data again.

mysql> SET time_zone = "+00:00";
Query OK, 0 rows affected (0.03 sec)

mysql> insert into timestamp_test(created_time, created_at) values('2020-12-09 08:00:00', '2020-12-09 08:00:00');
Query OK, 1 row affected (0.03 sec)

mysql> SET time_zone = "+08:00";
Query OK, 0 rows affected (0.04 sec)

Now check the data again. The two inserted SQL are the same, but the query results are different. The difference in created_at between the two data is exactly the time difference of the time zone.

mysql> select * from timestamp_test;
+----+---------------------+---------------------+
| id | created_time | created_at |
+----+---------------------+---------------------+
| 1 | 2020-12-09 08:00:00 | 2020-12-09 08:00:00 |
| 2 | 2020-12-09 08:00:00 | 2020-12-09 16:00:00 |
+----+---------------------+---------------------+
2 rows in set (0.06 sec)

Let's take a look at the timestamp actually stored. Then we change the time zone and find that the field time has changed, but the original timestamp data has not changed.

mysql> select *, unix_timestamp(created_at) from timestamp_test;
+----+---------------------+---------------------+----------------------------+
| id | created_time | created_at | unix_timestamp(created_at) |
+----+---------------------+---------------------+----------------------------+
| 1 | 2020-12-09 08:00:00 | 2020-12-09 08:00:00 | 1607472000 |
| 2 | 2020-12-09 08:00:00 | 2020-12-09 16:00:00 | 1607500800 |
+----+---------------------+---------------------+----------------------------+
2 rows in set (0.06 sec)

mysql> SET time_zone = "+00:00";
Query OK, 0 rows affected (0.09 sec)

mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | +00:00 |
+------------------+--------+
2 rows in set (0.08 sec)

mysql> select *, unix_timestamp(created_at) from timestamp_test;
+----+---------------------+---------------------+----------------------------+
| id | created_time | created_at | unix_timestamp(created_at) |
+----+---------------------+---------------------+----------------------------+
| 1 | 2020-12-09 08:00:00 | 2020-12-09 00:00:00 | 1607472000 |
| 2 | 2020-12-09 08:00:00 | 2020-12-09 08:00:00 | 1607500800 |
+----+---------------------+---------------------+----------------------------+
2 rows in set (0.18 sec)

Because MySQL implicitly converts all of this for us, we don't have to worry about time zone issues

The database actually saves UTC timestamps. When writing, it is first converted to UTC time according to the Session time zone. When reading, it is converted to the current time zone according to the Session time zone. These conversions are transparent.

  • Suppose we store a piece of data 2020-12-09 08:00:00 in the positive eighth zone
  • We retrieved this data in the eighth zone, and the time is still 2020-12-09 08:00:00
  • At this time, we have a server in the zero time zone, connect to MySQL , and set the time zone of the current connection to +00:00 . Then check the database record and the data found is: 2020-12-09 00:00:00 , which corresponds to the time of the zero time zone. In this way, we don't have to consider the time zone issue.

The above is the details of why MySQL timestamp can ignore the time zone issue. For more information about MySQL timestamp ignoring the time zone, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • MySQL slow query pitfalls
  • Solving the abnormality of mysql datetime query
  • SQL method for calculating timestamp difference
  • Pitfalls and solutions encountered in MySQL timestamp comparison query

<<:  Using HTML web page examples to explain the meaning of the head area code

>>:  Docker uses the nsenter tool to enter the container

Recommend

Detailed explanation of eight methods to achieve CSS page bottom fixed

When we are writing a page, we often encounter a ...

CSS XTHML writing standards and common problems summary (page optimization)

Project Documentation Directory Div+CSS Naming Sta...

W3C Tutorial (8): W3C XML Schema Activities

XML Schema is an XML-based alternative to DTD. XM...

Detailed explanation of MySQL basic operations (Part 2)

Preface This article contains 1. Several major co...

Vue.js implements the nine-grid image display module

I used Vue.js to make a nine-grid image display m...

React + Threejs + Swiper complete code to achieve panoramic effect

Let’s take a look at the panoramic view effect: D...

How to modify the mysql table partitioning program

How to modify the mysql table partitioning progra...

How to open external network access rights for mysql

As shown below: Mainly execute authorization comm...

Detailed process of drawing three-dimensional arrow lines using three.js

Demand: This demand is an urgent need! In a subwa...

Nginx reverse proxy configuration removes prefix

When using nginx as a reverse proxy, you can simp...

Learn about JavaScript closure functions in one article

Table of contents Variable Scope The concept of c...

How to retrieve password for mysql 8.0.22 on Mac

Mac latest version of MySQL 8.0.22 password recov...

Detailed analysis of replication in Mysql

1.MySQL replication concept It means transferring...

The whole process of implementing the summary pop-up window with Vue+Element UI

Scenario: An inspection document has n inspection...

Why is the MySQL auto-increment primary key not continuous?

Table of contents 1. Introduction 2. Self-increme...