MySQL Set default time is now ()

Variants of the timestamp
1,timestamp DEFAULT current_timestamp on UPDATE current_timestamp
Refreshes the data column when new records are created and existing records are modified

2,timestamp DEFAULT Current_timestamp
This field is set to the current time when a new record is created, but it is not refreshed when you modify it later

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

4,timestamp DEFAULT ' Yyyy-mm-dd hh:mm:ss ' on UPDATE current_timestamp
Set this field to the given value when you create a new record, and refresh it later when you modify it

MySQL currently does not support column default as a function form, such as reaching
The default value for your column is the current update date and time feature, and you can use the timestamp column type below to
Detailed description of the timestamp column type

*timestamp Column Type *
The timestamp value can be from the beginning of one 1970 to 2037, with a precision of one second and its value as
Digital display.
The format of the timestamp value display dimension is 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 bits, 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 will
is forced to be defined as column length 14.
The odd numeric dimensions of the column length in the range from 1~13 are coerced to the next larger even number.

* 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,
Use the full precision (14-bit) of the specified time-period value to store a valid value regardless of the display size.
Illegal date, will be forced to 0 storage

* This has several meanings: *
1, although you define the table when the column timestamp (8), but when you do data insertion and update
The timestamp column actually holds 14 bits of data (including date and time of day and seconds), except when you check
When you inquire, MySQL returns you with 8-digit date data. If you use ALTER TABLE to widen a narrow
The timestamp column, previously "hidden" information will be displayed.
2. Similarly, narrowing a timestamp column does not result in information loss, except in the sense that the value is displayed,
Less information is displayed.
3, although the timestamp value is stored as full precision, the only function that directly operates the stored value is
Unix_timestamp (); Because MySQL returns the column value of the TIMESTAMP column is retrieved after the formatted
Value, which means that you may not be able to use some functions to manipulate timestamp columns (for example, hour () or second
()) Unless the relevant part of the timestamp value is included in the formatted value. For example, a timestamp
The HH portion of the timestamp column is displayed only if the column is defined as timestamp (10), so the
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 with the current date and time
The operation.
If you have more than one timestamp column, only the first one is automatically updated.
The automatic update of the first timestamp column occurs under any of the following conditions:

1. Column values are not explicitly specified in an INSERT or load DATA infile statement.
2. Column values are not explicitly specified in an UPDATE statement and some other columns change values. (Note
An update setting is listed as a value that it already has, which will not cause the timestamp column to be updated, as
If you set a column as its current value, MySQL ignores the change for efficiency. ）
3. You explicitly set the timestamp column to be null.
4, except the first timestamp column can also be set to the current date and time, as long as the column is set
Is 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 the date1 and Date2 values are the current time
The second instruction is not set DATE1, date2 column value, the first timestamp column date1 is updated to
Current time,
and two timestamp columns Date2 changed to "00000000000000" due to the illegal date

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 will be
Update to current time

UPDATE Test SET id= 1,date1=date1,date2=now () WHERE id=3;
+----+----------------+----------------+
| ID | Date1 | Date2 |
+----+----------------+----------------+
| 1 | 20021114094009 | 20021114094320 |
| 2 | 20021114093724 | 00000000000000 |
+----+----------------+----------------+
This directive is set date1=date1, so date1 column values do not change when updating data
Because the Date2=now () is set, the Date2 column value is updated to the current time when the data is updated
This directive 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,
You can use the Date_fromat () function to format the TIMESTAMP column

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 important to note that there may be some change in value or loss of information:

1. If you assign a date value to a datetime or timestamp object, the time portion of the result value
The minute 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 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 more than 1970 or 2037 nights,
This means that a date such as ' 1968-01-01 ', when used as a datetime or date value, is
Legal,
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 a date value, beware of certain defects: *

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

2, the year value specified in 2 digits is ambiguous, because century is unknown. MySQL uses the following rules
Explains the 2-bit-year value: The year value in the 00-69 range is transformed to 2000-2069. The year value in the range 70-99 is changed
Change to 1970-1999.

MySQL Set default time is now ()