Mysql timestamp (timestamp) usage detailed _mysql

A variant of the timestamp

The timestamp timestamp can have multiple different characteristics when it is created, such as:

1. Refresh the data column when creating a new record and modifying an existing record:

TIMESTAMP DEFAULT current_timestamp on UPDATE current_timestamp

2. Set this field to the current time when you create a new record, but do not refresh it when you modify it later:

TIMESTAMP DEFAULT Current_timestamp

3. Set this field to 0 when you create a new record and refresh it later when you modify it:

TIMESTAMP on UPDATE Current_timestamp

4. When creating a new record, set the field to the given value and refresh it later when you modify it:

TIMESTAMP DEFAULT ' Yyyy-mm-dd hh:mm:ss ' on UPDATE current_timestamp

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

Second, 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 use a shorter display size, creating 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 (TIMESTAMP)
-> TIMESTAMP (a) 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 defined column timestamp (8) while you were building the table, the timestamp column actually saved 14 bits of data (including minutes and seconds) when you were inserting and updating the data, except that when you made the query, MySQL returned you 8-digit date. If you use ALTER TABLE to widen a narrow timestamp column, the previously "hidden" information will be displayed.

2. Similarly, narrowing a timestamp column does 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 operates to store the value is Unix_timestamp (), because 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)

For example, 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;
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 null.
4. Timestamp columns other than the first 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 (),
' Date2 ' TIMESTAMP (), 
   
    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, date2 values are the current time the second instruction because there is no date1, date2 column value, 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 to Date1=date1, so the Date1 column value does not change when the data is updated and is set Date2=now (), 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;

As the TIMESTAMP returned by MySQL is listed as a digital display, you can use the Date_fromat () function to format TIMESTAMP columns, as follows:

 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 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, thetimestamp 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.

Third, when specifying the 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 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 values specified with 2 digits are vague, since 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.

PS: Here again for you to recommend a local UNIX timestamp conversion tool, with a variety of language under the time stamp operation methods, including PHP, MySQL, SQL Server, Java, such as timestamp acquisition and conversion operations skills:

Unix timestamp (timestamp) conversion tool: http://tools.jb51.net/code/unixtime