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

Editor to share with you what is the difference between timestamp and datetime in MySQL 5.6, I believe most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

MySQL converts timestamp values according to the current time zone and processes them according to the current time zone when querying.

Mysql > create table test2 (an int (20))

Query OK, 0 rows affected (0.07 sec)

Mysql > insert into test2 values (1466929145)

Query OK, 1 row affected (0.00 sec)

Mysql > select * from test2

+-+

| | a |

+-+

| | 1466929145 |

+-+

1 row in set (0.00 sec)

Mysql > select from_unixtime (a) from test2

+-+

| | from_unixtime (a) |

+-+

| | 2016-06-26 08:19:05 |

+-+

1 row in set (0.00 sec)

Mysql > insert into test2 values (14669291450)

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > select from_unixtime (a) from test2

+-+

| | from_unixtime (a) |

+-+

| | 2016-06-26 08:19:05 |

| | 2038-01-19 03:14:07 |

+-+

2 rows in set (0.00 sec)

Mysql > insert into test2 values (14669291450)

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > show warnings

+-- +

| | Level | Code | Message | |

+-- +

| | Warning | 1264 | Out of range value for column 'a' at row 1 | |

+-- +

1 row in set (0.00 sec)

Mysql > select from_unixtime (a) from test2

+-+

| | from_unixtime (a) |

+-+

| | 2016-06-26 08:19:05 |

| | 2038-01-19 03:14:07 |

| | 2038-01-19 03:14:07 |

+-+

3 rows in set (0.00 sec)

View current time zone

Mysql > show variables like 'time_zone'

+-+ +

| | Variable_name | Value |

+-+ +

| | time_zone | + 00:00 |

+-+ +

1 row in set (0.00 sec)

After changing the session time zone parameters, you can see that the query time has changed.

Mysql > set session time_zone='+01:00'

Query OK, 0 rows affected (0.00 sec)

Mysql > select from_unixtime (a) from test2

+-+

| | from_unixtime (a) |

+-+

| | 2016-06-26 09:19:05 |

| | 2038-01-19 04:14:07 |

| | 2038-01-19 04:14:07 |

+-+

3 rows in set (0.00 sec)

Datetime is not affected by time zone

Mysql > create table test3 (a datetime)

Query OK, 0 rows affected (0.10 sec)

Mysql > select * from test3

Empty set (0.00 sec)

Mysql > insert into test3 values (now ())

Query OK, 1 row affected (0.00 sec)

Mysql > commit

Query OK, 0 rows affected (0.02 sec)

Mysql > select * from test3

+-+

| | a |

+-+

| | 2017-02-13 10:02:20 |

+-+

1 row in set (0.00 sec)

Mysql > set session time_zone='+10:00'

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like 'time_zone'

+-+ +

| | Variable_name | Value |

+-+ +

| | time_zone | + 10:00 |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from test3

+-+

| | a |

+-+

| | 2017-02-13 10:02:20 |

+-+

1 row in set (0.00 sec)

The above is all the content of the article "what is the difference between timestamp and datetime in MySQL 5.6". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.