MySQL Tutorial/Data Types/timestamp

mysql> SELECT MICROSECOND("2010-12:09.019473");

+-------------------------------------------+
| MICROSECOND("2010-12-10 14:12:09.019473") |
+-------------------------------------------+
|                                     19473 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql>

The timestamp data type stores the time that a row was last changed

TIMESTAMP column will be set to the current date and time whenever that row is updated or inserted in the table.

TIMESTAMP can only handle dates in the range 1970 through 2037.

TIMESTAMP data can be held with the maximum resolution of one second.

TIMESTAMP can have a number of external formats.

These can be made up of any even number of digits from 2 to 14.

TIMESTAMP fields are set to the current time if you do not write anything to them.

MySQL sets a TIMESTAMP column to the current time if NULL (or NOW()) is written to it.

Illegal values will be discarded and replaced with the "zero" value for this field type.

MySQL is flexible in the formats accepting for the TIMESTAMP.

For example, it will accept 2000-05-12, 2000+05+12, and 20000512.

If the field is left out when doing an insert or update, it will only update the first TIMESTAMP field in the table.

Subsequent TIMESTAMP fields will contain the zero value if not set explicitly.

If you have a DATE value and want to write it to a DATETIME or TIMESTAMP column, MySQL will insert 00:00:00 for the time portion.

The format varies according to the length.

For example to store the same information as DATETIME, you would specify a length of 14 whereas to store the DATE you would specify a length of 8.

Timestamp Definition Format TIMESTAMP(2) YY TIMESTAMP(4) YYYY TIMESTAMP(6) YYMMDD TIMESTAMP(8) YYYYMMDD TIMESTAMP(10) YYMMDDHHMM TIMESTAMP(12) YYMMDDHHMMSS TIMESTAMP(14) YYYYMMDDHHMMSS

TIMESTAMP NULL DEFAULT "0000-00:00"

mysql>
mysql>
mysql> CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT "0000-00-00 00:00:00");
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO t2 VALUES (NOW());
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from t2;
mysql>
mysql> drop table t2;
Query OK, 0 rows affected (0.00 sec)
mysql>

TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

mysql>
mysql>
mysql> CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> INSERT INTO t VALUES (NOW());
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t VALUES (CURRENT_TIMESTAMP);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from t;
+---------------------+
| ts                  |
+---------------------+
| 2007-07-23 19:09:19 |
| 2007-07-23 19:09:19 |
+---------------------+
2 rows in set (0.00 sec)
mysql>
mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

TIMESTAMP NULL DEFAULT NULL

mysql>
mysql> CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL);
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> INSERT INTO t1 VALUES (NOW());
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (CURRENT_TIMESTAMP);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from t1;
+---------------------+
| ts                  |
+---------------------+
| 2007-07-23 19:09:19 |
| 2007-07-23 19:09:19 |
+---------------------+
2 rows in set (0.00 sec)
mysql>
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

You can include the NULL attribute in the definition of a TIMESTAMP column to allow the column to contain NULL values. For example:

mysql>
mysql> CREATE TABLE myTable
    -> (
    ->   ts1 TIMESTAMP NULL DEFAULT NULL,
    ->   ts2 TIMESTAMP NULL DEFAULT 0,
    ->   ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> desc myTable;
+-------+-----------+------+-----+---------------------+-------+
| Field | Type      | Null | Key | Default             | Extra |
+-------+-----------+------+-----+---------------------+-------+
| ts1   | timestamp | YES  |     | NULL                |       |
| ts2   | timestamp | YES  |     | 0000-00-00 00:00:00 |       |
| ts3   | timestamp | YES  |     | CURRENT_TIMESTAMP   |       |
+-------+-----------+------+-----+---------------------+-------+
3 rows in set (0.02 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)