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

MYSQL A question about using character functions to filter data

Problem description: structure: test has two fiel...

Getting Started: A brief introduction to HTML's basic tags and attributes

HTML is made up of tags and attributes, which are...

Centos7 install mysql5.6.29 shell script

This article shares the shell script of mysql5.6....

How to install JDK8 on Windows

1. Download: http://www.oracle.com/technetwork/ja...

Detailed explanation of the error when using Element-ui NavMenu submenu to generate recursively

When the submenu of the navigation bar is generat...

Example code for Html layered box-shadow effect

First, let’s take a look at the picture: Today we...

Introduction to CSS foreground and background automatic color matching technology (demo)

1. Color matching effect preview As shown in the ...

Common JavaScript memory errors and solutions

Table of contents 1. Timer monitoring 2. Event mo...

Solve the problem of docker log mounting

The key is that the local server does not have wr...

How to create components in React

Table of contents Preface Component Introduction ...

Detailed explanation of the definition and usage of MySQL stored functions (custom functions)

Stored Functions What is a stored function: It en...

How to use the vue timeline component

This article example shares the specific implemen...

Simple setup of VMware ESXi6.7 (with pictures and text)

1. Introduction to VMware vSphere VMware vSphere ...

A brief understanding of MySQL SELECT execution order

The complete syntax of the SELECT statement is: (...

Detailed explanation of several examples of insert and batch statements in MySQL

Table of contents Preface 1.insert ignore into 2....