Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL TIMESTAMP and DATETIME     - Ubuntu Slingscold (Linux)

- Log4j configuration file Explanation (Linux)

- Installed in the desktop version of Ubuntu Unity Tweak Tool (Linux)

- C ++, overloading, cover, hide (Programming)

- Joseph Central Java implementation (Programming)

- HomeKit User Interface Guidelines (Linux)

- UNIX file permissions in the "set user ID bit" (Linux)

- Use Epoll develop high-performance application server on Linux (Server)

- Build a Linux development environment under STC89C52RC (Linux)

- Linux centralized log server rsyslog (Server)

- Android custom controls create the simplest skid menu in the history (Programming)

- Summary Linux bond of multi-interface load balancing (Linux)

- Mysql binlog resolve the garbage problem decryption (Database)

- Linux system on how to use rsync to synchronize data (Server)

- How to override the plain text files and directories soft connection in linux (Linux)

- Formatting Java floating-point types (Programming)

- Java rewrite equals method (Programming)

- Job achieve automation in Ubuntu 14.04 - Using Cron (Linux)

- jQuery update the content and method of use 3.0 (Programming)

- How to manage start-up applications in Ubuntu (Linux)

 
         
  MySQL TIMESTAMP and DATETIME
     
  Add Date : 2017-01-08      
         
         
         
  First, how MySQL said the current time?

In fact, the expression still find many, are summarized as follows:

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP ()

NOW ()

LOCALTIME

LOCALTIME ()

LOCALTIMESTAMP

LOCALTIMESTAMP ()

Second, on the comparisons TIMESTAMP and DATETIME

A complete date in the following format: YYYY-MM-DD HH: MM: SS [.fraction], it can be divided into two parts: date and time portion portion, wherein, date portion corresponding format "YYYY-MM-DD" , time section corresponding to the format "HH: MM: SS [.fraction]". For the date field, the date portion only supports, if you insert the contents of the time portion, it will discard the contents of the section and prompt a warning.

mysql> create table test (id int, hiredate date);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values (1, '20151208000000');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (1, '20151208104400');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warning;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1
mysql> select * from test;
+ ------ + ------------ +
| Id | hiredate |
+ ------ + ------------ +
| 1 | 2015-12-08 |
| 1 | 2015-12-08 |
+ ------ + ------------ +
2 rows in set (0.00 sec)
Note: The first reason is that the warning did not suggest that it's time parts are 0

TIMESTAMP and DATETIME the same point:

1> Both can be used to represent the YYYY-MM-DD HH: MM: SS [.fraction] type of date.

TIMESTAMP and DATETIME differences:

1> both are stored differently

For TIMESTAMP, it's time to insert the client conversion from the current time zone is UTC (Coordinated Universal Time) for storage. When the query, which in turn is converted to the client's current time zone to return.

For DATETIME, without any change, as it is basically the input and output.

Next, we verify

First, create two test table, use a timestamp format, use a datetime format.

mysql> create table test (id int, hiredate timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values (1, '20151208000000');
Query OK, 1 row affected (0.00 sec)

mysql> create table test1 (id int, hiredate datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values (1, '20151208000000');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+ ------ + --------------------- +
| Id | hiredate |
+ ------ + --------------------- +
| 1 | 2015-12-08 00:00:00 |
+ ------ + --------------------- +
1 row in set (0.01 sec)

mysql> select * from test1;
+ ------ + --------------------- +
| Id | hiredate |
+ ------ + --------------------- +
| 1 | 2015-12-08 00:00:00 |
+ ------ + --------------------- +
1 row in set (0.00 sec)
Both the output is the same.

Second, modify the time zone of the current session

 
mysql> show variables like '% time_zone%';
+ ------------------ + -------- +
| Variable_name | Value |
+ ------------------ + -------- +
| System_time_zone | CST |
| Time_zone | SYSTEM |
+ ------------------ + -------- +
2 rows in set (0.00 sec)

mysql> set time_zone = '+ 0:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+ ------ + --------------------- +
| Id | hiredate |
+ ------ + --------------------- +
| 1 | 2015-12-07 16:00:00 |
+ ------ + --------------------- +
1 row in set (0.00 sec)

mysql> select * from test1;
+ ------ + --------------------- +
| Id | hiredate |
+ ------ + --------------------- +
| 1 | 2015-12-08 00:00:00 |
+ ------ + --------------------- +
1 row in set (0.01 sec)
He said "CST" refers to the host where the MySQL system time, is an abbreviation for China standard time, China Standard Time UT + 8: 00

By results can be seen, test returned eight hours ahead of time, and the time test1 will remain unchanged. This fully verify the difference between the two.

2> can store both the time is not the same

Time timestamp can be stored as follows: '1970-01-0100: 00: 01.000000' to '2038-01-1903: 14: 07.999999'.

Datetime time can be stored as follows: '1000-01-0100: 00: 00.000000' to '9999-12-3123: 59: 59.999999'.

 

Summary: TIMESTAMP and DATETIME in addition to storage and storage range is not the same, not much difference. Of course, for business across time zones, TIMESTAMP more appropriate.

 

Third, the automatic initialization and updating on TIMESTAMP and DATETIME of

First, we look at the following operations

mysql> create table test (id int, hiredate timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test (id) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+ ------ + --------------------- +
| Id | hiredate |
+ ------ + --------------------- +
| 1 | 2015-12-08 14:34:46 |
+ ------ + --------------------- +
1 row in set (0.00 sec)

mysql> show create table test \ G
*************************** 1. row ******************** *******
       Table: test
Create Table: CREATE TABLE `test` (
  `Id` int (11) DEFAULT NULL,
  `Hiredate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set (0.00 sec)

Appears to be not a bit strange, I do not have to insert hiredate fields, its value is automatically changed to the current value, and create a table, I also did not define "show create table test \ G" displayed in the results "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP".

In fact, this feature is automatically initialized and automatically update (Automatic Initialization and Updating).

Automatic initialization means is that if there is no explicit assignment of the field (such as in the example above hiredate field) is automatically set to the current system time.

Automatic updates means that if you modify the other fields, the value of this field will automatically be updated to the current system time.

It "explicit_defaults_for_timestamp" parameters.

By default, this parameter is OFF, as follows:

mysql> show variables like '% explicit_defaults_for_timestamp%';
+ --------------------------------- + ------- +
| Variable_name | Value |
+ --------------------------------- + ------- +
| Explicit_defaults_for_timestamp | OFF |
+ --------------------------------- + ------- +
1 row in set (0.00 sec)
Here we look at the official description of the file:

By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.

In many cases, this is not what we want, how to disable it?

1. The "explicit_defaults_for_timestamp" value is set to ON.

2. "explicit_defaults_for_timestamp" value is still OFF, there are two ways to disable

     1> that the column specify a default value with the DEFAULT clause

     2> NULL attribute specified for that column.

As follows:

mysql> create table test1 (id int, hiredate timestamp null);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test1 \ G
*************************** 1. row ******************** *******
       Table: test1
Create Table: CREATE TABLE `test1` (
  `Id` int (11) DEFAULT NULL,
  `Hiredate` timestamp NULL DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set (0.00 sec)

mysql> create table test2 (id int, hiredate timestamp default 0);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test2 \ G
*************************** 1. row ******************** *******
       Table: test2
Create Table: CREATE TABLE `test2` (
  `Id` int (11) DEFAULT NULL,
  `Hiredate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set (0.00 sec)
Before MySQL 5.6.5 version, Automatic Initialization and Updating only applies to TIMESTAMP, and a table, allows up to a TIMESTAMP field using this feature. MySQL 5.6.5 from the beginning, Automatic Initialization and Updating applies to TIMESTAMP and DATETIME, and do not limit the number.
     
         
         
         
  More:      
 
- New features of Java 9 HTTP2 and REPL (Programming)
- Linux crontab use (Linux)
- Lsblk command lists using Linux block device information (Linux)
- Three kinds of binary tree traversal recursive and iterative solution (Programming)
- About Linux backdoor (Linux)
- JBPM6 Tutorial - Fast Fun JBPM table (Linux)
- The difference between Objective-C language nil, Nil, NULL, NSNull (Programming)
- Use the Find command to help you find those files that need to be cleaned (Linux)
- Oracle set and remove columns unavailable (Database)
- Ubuntu 14.04 Solution login interface infinite loop (Linux)
- CentOS 6 compiling httpd-2.4.10 (Server)
- Lua study notes under ubuntu (Linux)
- Present Situation and Development Trend of firewall products (Linux)
- WinSCP to transfer text files will automatically convert the format (Linux)
- MongoDB 2.6 deployment replica set + partitions (Database)
- To create a secure network firewall with iptables Under Linux (Linux)
- Hadoop 2.5 Pseudo distribution installation (Server)
- To batch create users under Linux (Linux)
- Linux server security settings to close unused ports (Linux)
- CentOS7 install NTFS-3G driver (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.