Timestamp column type (how to set the default value of the column is now ())

Detailed timestamp column type details (how to set the default value of the column is now ())
MySQL does not currently support the form of column default as a function,
If the default value of one of your columns is the function of the current update date and time,
You can use the timestamp column type
The following is a detailed description of 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 the timestamp column can also be created with a shorter display size
The most common display sizes are 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.

Columns such 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,
Use the full precision (14-bit) of the specified period time value to store a valid value 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 when you do data insertion and update timestamp column
In fact, 14 bits of data are saved (including minutes and seconds),
Only when you make the query MySQL returned to you is the 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 complete precision, the only function that directly manipulate the stored value is Unix_timestamp ();
Because MySQL returns the column value of the timestamp column is the value that was retrieved after the 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 portion 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.
Therefore, the use of Hour () on a shorter timestamp value can produce 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 sets a value that is listed as it already exists,
This will not cause the timestamp column to be updated,
Because if you set a column to its current value, MySQL ignores the changes for efficiency. ）
3, you explicitly set the timestamp column null.
4, except the first timestamp column can also 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 (14),
' 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 values are the current time
The second instruction does not set DATE1, date2 column values, the first timestamp column date1 to update to the current time,
and two timestamp columns date2 become "00000000000000" due to the illegal date


UPDATE Test SET id= 3 WHERE id=1;
+----+----------------+----------------+
| ID | Date1 | Date2 |
+----+----------------+----------------+
| 3 | 20021114094009 | 20021114093723 |
| 2 | 20021114093724 | 00000000000000 |
+----+----------------+----------------+
-> This instruction does not explicitly set Date2 column values, so the first timestamp column date1 will be 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 date1 column values do not change when the data is updated
The Date2 column value is updated to the current time when the data is updated because the Date2=now () is set
This instruction is equivalent to the UPDATE test SET id= 1,date1=date1,date2=null WHERE id=3;

Because the TIMESTAMP returned by MySQL is listed as a digital display form,
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 | 2002-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 result value is set to ' 00:00:00 ',
Because time information is not included in the date value.

2. If you assign a datetime or timestamp value to a Date object,
The time portion of the result 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,
But all types do not have the same range of values.
For example, the timestamp value cannot be as early as 1970 or 2037 nights,
This means that a date such as ' 1968-01-01 ', when it is valid as a datetime or date value,
But it's 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:
In the year 00-69 the value of the range is transformed to 2000-2069.
The year value of the range 70-99 is transformed to 1970-1999.