Parsing MySQL TIMESTAMP (timestamp) and datetime differences Comparison

Introduction to timestamp basic knowledge

Variant of the timestamp
1,timestamp DEFAULT current_timestamp on UPDATE current_timestamp refreshes this data column when new records are created and existing records are modified

2,timestamp default Current_timestamp Set this field to the current time when creating a new record, but when you modify it later, it is no longer refreshed

3,timestamp on UPDATE current_timestamp set this field to 0 when creating a new record and refresh it when you modify it later

4,timestamp default ' Yyyy-mm-dd hh:mm:ss ' on UPDATE current_timestamp to set this field to a given value when creating a new record, refresh it later when modified

MySQL does not currently support column default for the form of functions, such as to achieve the default value of your column for the current update date and time function, you can use the timestamp column type below to specify the timestamp column type

*timestamp Column Type *
The timestamp value can be from the beginning of 1970 to 2037, with a precision of one second, with a value displayed as a number.
The timestamp value displays the format of the dimension as shown in the following table:
：
+---------------+----------------+
| Column Type | Display Format |
| TIMESTAMP (14) | YYYYMMDDHHMMSS |
| TIMESTAMP (12) | Yymmddhhmmss |
| TIMESTAMP (10) | YYMMDDHHMM |
| TIMESTAMP (8) | YYYYMMDD |
| TIMESTAMP (6) | YYMMDD |
| TIMESTAMP (4) | Yymm |
| TIMESTAMP (2) | YY |
+---------------+----------------+
The "full" timestamp format is 14-bit, but timestamp columns can also create the most common display sizes with a shorter display size of 6, 8, 12, and 14.
You can specify an arbitrary display size when creating a table, but defining a column length of 0 or greater than 14 is enforced as column length 14.
The column length is coerced to the next larger even number in the 1~13 range from the odd numeric dimension.

* Listed as: *
Define field length force field length
TIMESTAMP (0)-> TIMESTAMP (14)
TIMESTAMP (->) TIMESTAMP (14)
TIMESTAMP (1)-> TIMESTAMP (2)
TIMESTAMP (5)-> TIMESTAMP (6)

All timestamp columns have the same storage size, using the full precision (14-bit) of the specified period time value to store the valid values regardless of the display size. Illegal date, will be forced to 0 storage
* This has several implications: *
1, although you have defined the table timestamp (8), but in the data you insert and update when the timestamp column actually saved 14 bits of data (including time and date), but in your query when the MySQL return to you is 8-digit date of the year. If you use ALTER TABLE to widen a narrow timestamp column, the previously "hidden" information will be displayed.
2. Similarly, narrowing a timestamp column will not result in loss of information, except when the sense value is displayed, less information is displayed.
3, although the TIMESTAMP value is stored as a complete precision, the only function that directly manipulate the stored value is Unix_timestamp (); Since MySQL returns the column value of the TIMESTAMP column as the value of the retrieved format, This means that you may not be able to use certain functions to manipulate timestamp columns (such as hour () or second ()) unless the relevant part of the timestamp value is included in the formatted value. For example, when a timestamp column is defined as timestamp (10), the HH portion of the timestamp column is displayed, so using hour () on a shorter timestamp value produces an unpredictable result.
4, the illegal timestamp value is transformed to the appropriate type of "0" value (00000000000000). (Datetime,date and vice versa)
* You can use the following statements to verify: *
CREATE TABLE Test (' ID ' INT (3) UNSIGNED auto_increment, ' date1 '
TIMESTAMP (8) PRIMARY KEY (' id '));
INSERT into Test SET id = 1;
SELECT * from Test;
+----+----------------+
| ID | Date1 |
+----+----------------+
| 1 | 20021114 |
+----+----------------+
ALTER TABLE test Change ' date1 ' Date1 ' TIMESTAMP (14);
SELECT * from Test;
+----+----------------+
| ID | Date1 |
+----+----------------+
| 1 | 20021114093723 |
+----+----------------+

You can use the timestamp column type to automatically mark the INSERT or update operation with the current date and time. If you have multiple timestamp columns, only the first one is automatically updated. Automatic Updates the first timestamp column occurs under any of the following conditions:
1, the column value is not explicitly specified in an INSERT or load DATA infile statement.
2, the column value is not explicitly specified in an UPDATE statement and some other columns change the value. (Note that an update setting lists the values it already has, which will not cause the timestamp column to be updated because if you set a list of its current values, MySQL ignores the changes for efficiency.)
3, you explicitly set the timestamp column to null.
4, timestamp columns other than the first can be set to the current date and time, as long as the column is set to null or now ().
CREATE TABLE Test (
' ID ' INT (3) UNSIGNED auto_increment,
' date1 ' TIMESTAMP,
' Date2 ' TIMESTAMP (14),
PRIMARY KEY (' id ')
);

INSERT into Test (ID, date1, date2) VALUES (1, NULL, NULL);
INSERT into Test SET id= 2;
+----+----------------+----------------+
| id | date1 | date2 |
+----+----------------+-------------- --+
| 1 | 20021114093723 | 20021114093723 |
| 2 | 20021114093724 | 00000000000000 |
+----+----------------+----------------+
-> The first instruction is set date1, Date2 is null, so date1, Date2 value is current time the second instruction has not set DATE1, date2 column values, the first timestamp column date1 to update to the current time, and two timestamp columns date2 become "00000000000000" because the date is not valid
UPDATE Test SET id= 3 WHERE id=1;
+----+----------------+----------------+
| id | date1 | date2 |
+----+----------------+-------------- --+
| 3 | 20021114094009 | 20021114093723 |
| 2 | 20021114093724 | 00000000000000 |
+----+----------------+----------------+
-> This directive does not explicitly set Date2 column values, so the first timestamp column date1 is updated to the current time

UPDATE test SET id= 1,date1=date1,date2=now () WHERE id=3;
+----+----------------+----------------+
| id | date1 | date2 |
+----+----------------+-------------- --+
| 1 | 20021114094009 | 20021114094320 |
| 2 | 20021114093724 | 00000000000000 |
+----+----------------+----------------+
-> This instruction is set date1=date1, so the Date1 column value does not change when the data is updated because Date2=now () is set. So the Date2 column value is updated to the current time when the data is updated this instruction is equivalent to update test SET id= 1,date1=date1,date2=null WHERE id=3;
because the TIMESTAMP returned by MySQL is listed as a digital display, you can use the Date_fromat () function to format TIMESTAMP columns
SELECT id,date_format (date1, '%y-%m-%d%h:%i: %s ') as Date1,
Date_format (date2, '%y-%m-%d%h:%i:%s ') as date2 from test;
+----+---------------------+--------- ------------+
| id | date1 | date2 |
+----+---------------------+---------------------+
| 1 | 2002-11-14 09:40:09 | 2002-11-14 09:43:20 |
| 2 | 20 02-11-14 09:37:24 | 0000-00-00 00:00:00 |
+----+---------------------+---------------------+

SELECT Id,date_format (date1, '%y-%m-%d ') as Date1,
Date_format (Date2, '%y-%m-%d ') as date2 from test;

+----+-------------+-------------+
| ID | Date1 | Date2 |
+----+-------------+-------------+
| 1 | 2002-11-14 | 2002-11-14 |
| 2 | 2002-11-14 | 0000-00-00 |
+----+-------------+-------------+

In a way, you can assign a value of a date type to an object of a different date type. However, it is particularly noted that the value may have some change or loss of information:

1. If you assign a date value to a datetime or timestamp object, the time portion of the resulting value is set to ' 00:00:00 ' because the date value does not contain time information.
2. If you assign a datetime or timestamp value to a Date object, the time portion of the resulting value is deleted because the date type does not store time information.
3. Although datetime, date, and timestamp values can all be specified with the same format set, all types do not have the same range of values.
For example, the timestamp value cannot be as early as 1970 or 2037 nights, which means that a date, such as ' 1968-01-01 ', is legal when it is a datetime or date value, but it is not a correct timestamp value! And if you assign such an object to the timestamp column, it will be transformed to 0.

* When specifying date values, beware of certain defects: *

1. A loose format that allows you to specify values as a string can be spoofed. For example, because the ":" Delimiter is used, the value ' 10:11:12 ' may look like a time value, but if used in a date, the context will be interpreted as the year ' 2010-11-12 '. The value ' 10:45:15 ' will be transformed to ' 0000-00-00 ' because ' 45 ' is not a valid month.

2, the year value specified with 2 digits is vague, because the century is unknown. MySQL interprets 2-digit year values using the following rules: The year value in the 00-69 range is transformed to 2000-2069. The year value of the range 70-99 is transformed to 1970-1999.

Comparison of timestamp and datetime

Same

Show

The timestamp column displays the same format as the datetime column. In other words, the display width is fixed to 19 characters and the format is Yyyy-mm-dd HH:MM:SS.

Different

Range

datetime retrieves and displays datetime values in the ' yyyy-mm-dd HH:MM:SS ' format. The range of support is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 ' timestamp value cannot be earlier than 1970 or later than 2037

Stores

TIMESTAMP

1.4-byte storage (time stamp value was stored in 4 bytes)

2. Value is saved in UTC (It stores the number of milliseconds)

3. Time zone conversion, storage time for the current time zone conversion, retrieval and then converted back to the current time zone.

Datetime

1.8 bytes Storage (8 bytes storage)

2. Actual format storage (Just stores what you have stored and retrieves the same thing for you which have.)

3. Time zone Independent (It has nothing to deal with the TIMEZONE and conversion.)

Example comparison

Now I'm going to do a time zone on their influence.

1. Inserts a data insert INTO ' T8 ' values First (now (), now ());

2. Change the client time zone (East 9, Japan time zone).

3. Display the inserted data again, changed, timestamp type of data increased by 1 hours

Next, discuss some of the other properties of timestamp

1.null is empty

Timestamp default is "Non-null" (not null by default), if you are defining "TS timestamp default NULL" illegal. can be specified as NULL null, "TS TIMESTAMP null", at which point the default value can be changed in the Add statement.

TS2 TIMESTAMP NULL default 0,TS3 TIMESTAMP null default Current_timestamp

Default (only one column in a table can select one of the following)

Default Current_timestamp default Current_timestamp on UPDATE current_timestamp
On UPDATE Current_timestamp
On UPDATE See 2

Timestamp simple example in MySQL

The timestamp type in MySQL can set default values, just like any other type.
1, Automatic Update and insert to the current time:
Table:
/*DDL Information for-test.t1*/
---------------------------------

Table Create Table
------  -------------------------------------------------------------------------------------
T1 CREATE TABLE ' T1 ' (
' P_c ' int (one) is not NULL,
' P_time ' timestamp not NULL DEFAULT current_timestamp on UPDATE current_timestamp
) Engine=innodb DEFAULT charset=gb2312

Data:

1 2007-10-08 11:53:35
2 2007-10-08 11:54:00

INSERT into T1 (p_c) Select 3;
Update T1 Set P_c = 2 where p_c = 2;

Data:

1 2007-10-08 11:53:35
2 2007-10-08 12:00:37
3 2007-10-08 12:00:37

2, automatic insert to the current time, but not automatic update.

Table:
/*DDL Information for-test.t1*/
---------------------------------

Table Create Table
------  ---------------------------------------------------------
T1 CREATE TABLE ' T1 ' (
' P_c ' int (one) is not NULL,
' P_time ' timestamp not NULL DEFAULT current_timestamp
) Engine=innodb DEFAULT charset=gb2312

Data:
INSERT into T1 (p_c) Select 4;
Update T1 Set P_c = 3 where p_c = 3;

1 2007-10-08 11:53:35
2 2007-10-08 12:00:37
3 2007-10-08 12:00:37
4 2007-10-08 12:05:19

3, a table can not have two fields the default value is the current time, otherwise there will be an error. But the others can.
Table:
/*DDL Information for-test.t1*/
---------------------------------

Table Create Table
------  ---------------------------------------------------------------
T1 CREATE TABLE ' T1 ' (
' P_c ' int (one) is not NULL,
' P_time ' timestamp not NULL DEFAULT Current_timestamp,
' P_timew2 ' timestamp not NULL DEFAULT ' 0000-00-00 00:00:00 '
) Engine=innodb DEFAULT charset=gb2312

Data:
1    2007-10-08 11:53:35    0000-00-00 00:00:00
2    2007-10-08 12:00:37    0000-00-00 00:00:00
3    2007-10-08 12:00:37     0000-00-00 00:00:00
4    2007-10-08 12:05:19    0000-00-00 00:00:00