Compare the date difference between MySQL and Oracle by using a create statement

Compare the date difference between MySQL and Oracle by using a create statement

Today, a developer submitted an SQL change and found a problem during deployment.
A statement is a simple create statement.
Create table 'test _ user '(
'Openid' varchar (64) not null,
'Amount 'varchar (11) DEFAULT 0,
'Create _ time' datetime DEFAULT CURRENT_TIMESTAMP,
'Update _ time' datetime DEFAULT CURRENT_TIMESTAMP,
Primary key ('openid ')
) ENGINE = InnoDB default charset = utf8;
The error message is:
ERROR 1067 (42000) at line 1: Invalid default value for 'create _ time'
First, this does not seem to be a syntax problem. The deployment environment is 5.5.
At this time, I had a virtual machine testing environment on hand. I immediately tried it and found that there was no problem in 5.6.
The information is as follows:
Query OK, 0 rows affected (0.13 sec)
So this problem caused my attention.
I did the following tests. First, current_timestamp must be available. Check the time.
Select current_timestamp, current_timestamp ();
+ --------------------- +
| Current_timestamp () |
+ --------------------- +
| 18:31:25 | 18:31:25 |
+ --------------------- +
1 row in set (0.00 sec)
Create a test table to test the problem.
Create table test (col1 datetime DEFAULT CURRENT_TIMESTAMP );
ERROR 1067 (42000): Invalid default value for 'col1'
It seems that a problem occurred when adding the default value.
> Create table test (col1 datetime DEFAULT '');
ERROR 1067 (42000): Invalid default value for 'col1'
You cannot add null values.
> Create table test (col1 datetime DEFAULT '2017-11-23 18:31:25 ');
Query OK, 0 rows affected (0.02 sec)
Add a fixed static default value.
At the same time, I have read some articles, some of which say that the default value cannot be added. This statement should not be true. It can only be said that dynamic default values cannot be added. Another saying is that the dynamic default value of default is 5.6.5.
This part is also described in the MySQL official document. Http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html
The problem still needs to be solved. How can this problem be solved. First, it is impossible to upgrade the database to 5.6 for this operation.
There are two solutions.
One is to cancel the default value, and the other is to change the field type to timestamp.
The problem arises: what are the differences and relationships between datetime and timestamp? If you remember correctly, there is another type of 'date'. What are the differences and relationships between these three data types?
We create a table that contains three fields: datetime, timestamp, and date.
Create table test (date1 datetime, date2 timestamp, date3 date );
Insert three values to see the difference.
Mysql> insert into test values (current_date, current_timestamp, current_date );
Query OK, 1 row affected (0.00 sec)
Mysql> select * from test;
+ --------------------- + ------------ +
| Date1 | date2 | date3 |
+ --------------------- + ------------ +
| 00:00:00 | 23:21:35 |
+ --------------------- + ------------ +
It can be seen that datetime and timestamp are accurate to seconds, and date is accurate to day.
What is the difference between datetime and timestamp.
The storage space of datetime and timestamp is different. datetime occupies 8 bytes and timestamp occupies 4 bytes. Therefore, the time range supported by timestamp is narrower. The range is 08:00:01 to 11:14:07, while the time range supported by datetime is much larger. 1000-01-01 00:00:00 ~ 23:59:59, 9999-12-31
Simply put, it is not appropriate to use timestamp for data before liberation, while datetime should be broader.
For a simple verification. If you add a value that exceeds the time range to the timestamp column, an error is returned.
Mysql> insert into test values ('2017-01-19 11:14:08 ', '2017-01-19 11:14:08', current_date );
ERROR 1292 (22007): Incorrect datetime value: '2017-01-19 11:14:08 'for column 'date2' at row 1
Insert the maximum value to timestamp again.
Mysql> insert into test values ('2017-01-19 11:14:08 ', '2017-01-19 11:14:07', current_date );
Mysql> select * from test;
+ --------------------- + ------------ +
| Date1 | date2 | date3 |
+ --------------------- + ------------ +
| 00:00:00 | 23:21:35 |
| 11:14:08 | 11:14:07 |
+ --------------------- + ------------ +
Mysql> insert into test values ('2017-12-31 23:59:59 ', '2017-01-19 11:14:07', current_date );
Query OK, 1 row affected (0.00 sec)
Insert the maximum value for datetime
Mysql> insert into test values ('2017-12-31 23:59:59 ', '2017-01-19 11:14:07', current_date );
Mysql> select * from test;
+ --------------------- + ------------ +
| Date1 | date2 | date3 |
+ --------------------- + ------------ +
| 00:00:00 | 23:21:35 |
| 11:14:08 | 11:14:07 |
| 9999-12-31 23:59:59 | 11:14:07 |
+ --------------------- + ------------ +
In addition, the difference between datetime and timestamp is that timestamp is set to current_timestamp by default for Insert.
For example, if the values of datetime, timestamp, and date are left blank, the result is as follows:
Mysql> insert into test (date1) values ('2017-01-19 11:14:06 ');
Mysql> select * from test;
+ --------------------- + ------------ +
| Date1 | date2 | date3 |
+ --------------------- + ------------ +
| 00:00:00 | 23:21:35 |
| 11:14:08 | 11:14:07 |
| 9999-12-31 23:59:59 | 11:14:07 |
| 11:14:06 | 23:30:22 | NULL |
+ --------------------- + ------------ +
4 rows in set (0.00 sec)
If only the input value of timestamp is null for datetime and date, the effect is significantly different.
Insert into test (date2) values ('2017-01-19 11:14:06 ');
Mysql> select * from test;
+ --------------------- + ------------ +
| Date1 | date2 | date3 |
+ --------------------- + ------------ +
| 00:00:00 | 23:21:35 |
| 11:14:08 | 11:14:07 |
| 9999-12-31 23:59:59 | 11:14:07 |
| 11:14:06 | 23:30:22 | NULL |
| NULL | 11:14:06 | NULL |
+ --------------------- + ------------ +
This type of problem is another case in Oracle. in oracle, the timestamp type is more accurate than date.
To perform a simple test
SQL> create table test (date1 date, date2 timestamp );
Table created.
SQL> col name format a20
In fact, you can view the timestamp at this time to see the type of timestamp (6.
SQL> desc test
Name Null? Type
-----------------------------------------------------------------------------
DATE1 DATE
DATE2 TIMESTAMP (6)
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';
SQL> insert into test values (sysdate, sysdate );
SQL> col date2 format a30
DATE1 DATE2
-------------------------------------------------
23:03:35 23-NOV-15 11.03.35.000000 PM
If systimestamp is inserted, the result may be different.
SQL> insert into test values (systimestamp, systimestamp );
1 row created.
SQL> select * from test;
DATE1 DATE2
-------------------------------------------------
23:03:35 23-NOV-15 11.03.35.000000 PM
2015-11-23 23:05:08 23-NOV-15 11.05.08.378586 PM

In fact, there are many time types in oracle, such as timestamp with timezone, which support a larger range.
If the above question is in oracle, can we support dynamic default values? Certainly, because we seem to be used to this.
You can use the following method to specify.
SQL> alter table test modify (date1 default sysdate );
Table altered.
SQL> alter table test modify (date2 default policimestamp );
Table altered.
Through these small tests, we also found that the time type is quite different between mysql and oracle. Maybe, in terms of data type division, mysql has more types, there are many data types, and oracle seems to be completely replaced by a Number. In fact, there have been many internal improvements. Through comparison, we can find a lot of interesting things.