MySQL Now () function details

MySQL currently does not support column Default as a function. If the Default value of a column is the current update date and time, you can use the TIMESTAMP column type.

The following describes the TIMESTAMP column type in detail:

TIMESTAMP column type

The TIMESTAMP value can start from 1970 to 2037, and is precise to one second. Its value is displayed as a number.

The format of the TIMESTAMP value display size is as follows:

+ --------------- + ---------------- +

| 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 "complete" 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 the defined column length is 0 or greater than 14, and the column length is forcibly defined as 14.

The column length ranges from 1 ~ The dimensions of odd values in the range of 13 are forced to be the next larger even number.


Define Field Length Force Field Length

TIMESTAMP (0)-> TIMESTAMP (14)

TIMESTAMP (15)-> TIMESTAMP (14)

TIMESTAMP (1)-> TIMESTAMP (2)

TIMESTAMP (5)-> TIMESTAMP (6)

All TIMESTAMP columns have the same storage size,

Use the complete precision (14 bits) of the specified period time value to store valid values regardless of the display size.

An invalid date will be forcibly stored as 0

There are several meanings:

1. Although you have defined the column TIMESTAMP (8) when creating a table, the TIMESTAMP column actually stores 14 bits of data (including year, month, day, hour, minute, and second) When you insert and update data ), however, during your query, MySQL returns 8-bit year, month, and day data. If you use alter table to broaden a narrow TIMESTAMP column, previously "concealed" information will be displayed.

2. Similarly, narrowing down a TIMESTAMP column will not cause loss of information, except when the upper value is displayed, less information is displayed.

3. Although the TIMESTAMP value is stored as a complete precision, the only function for directly operating the stored value is UNIX_TIMESTAMP (); because the value of the TIMESTAMP column returned by MySQL is the value retrieved after formatting, this means that you may not be able to use some functions to operate the TIMESTAMP column (such as HOUR () or SECOND (), unless the related part of the TIMESTAMP value is included in the formatted value. For example, if a TIMESTAMP column is defined as TIMESTAMP (10) or higher, the HH part of the TIMESTAMP column is displayed. Therefore, HOUR () is used for shorter TIMESTAMP values () an unpredictable result is generated.

4. the invalid TIMESTAMP value is converted to the "zero" value (00000000000000) of the appropriate type ). (DATETIME, DATE, and so on) You can use the following statement for verification:

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.

The first TIMESTAMP column is automatically updated 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 other columns change the value. (Note that setting an UPDATE column as its existing value does not cause the TIMESTAMP column to be updated, because if you set a column as its current value, MySQL ignores the changes for efficiency .)

3. You explicitly set the TIMESTAMP column as NULL.

4. The TIMESTAMP column except the first one 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 value of date1 and date2 is the current time because date1 and date2 are set to NULL.

The second command does not set the values of the date1 and date2 columns. The first TIMESTAMP column date1 is updated to the current time,

The two TIMESTAMP columns, date2, change to "00000000000000" because the date is invalid"

UPDATE test SET id = 3 WHERE id = 1;

+ ---- + ---------------- +

| Id | date1 | date2 |

+ ---- + ---------------- +

| 3 | 20021114094009 | 20021114093723 |

| 2 | 20021114093724 | 00000000000000 |

+ ---- + ---------------- +

-> This command does not explicitly set the column value of date2, 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 command sets date1 = date1, so the value of the date1 column does not change when updating data.

Because date2 = NOW () is set, the value of the date2 column is updated to the current time when data is updated.

This command is equivalent to UPDATE test SET id = 1, date1 = date1, date2 = null where id = 3;

Because the TIMESTAMP column returned by MySQL is displayed as a number,

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 | 09:40:09 | 09:43:20 |

| 2 | 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 |

+ ---- + ------------- +

To some extent, you can assign a value of the date type to an object of different date types.

Note that the value may change or cause loss of information:

1. If you assign a DATE value to a DATETIME or TIMESTAMP object, the time part of the result 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 part of the result value is deleted because the DATE type does not store the time information.

3. Although DATETIME, DATE, and TIMESTAMP values can all be specified in the same format set,

However, all types do not have the same value range.

For example, the TIMESTAMP value cannot be earlier than or later than 1970,

This means that a DATE, such as '2017-01-01 ', is valid when treated as a DATETIME or DATE value,

But it is not a correct TIMESTAMP value! And if such an object is assigned to the TIMESTAMP column, it is converted to 0. When specifying a date value, consider some defects:

1. the loose format that can be specified as a string value can be spoofed. For example, because the separator ":" is used, the value '10: 11: 12' may look like a time value, but if it is used in a date, the context is interpreted as the year as '2017-11-12 '. The value '10: 45: 15' is changed to '2017-00-00 'Because '45' is not a valid month.

2. The year value specified by a two-digit number is vague, because the century is unknown. MySQL uses the following rules to explain the two-digit year value: the year value in the range of 2000-69 is changed to 2069. The annual value in the range of 70-99 is changed to 1970-1999.