Skip navigation links
10.3.1.2 TIMESTAMP Properties as of MySQL 4.1 »
Section Navigation      [Toggle]

10.3.1.1. TIMESTAMP Properties Prior to MySQL 4.1

TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, not for other types such as DATETIME.)

The TIMESTAMP data type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns in a table, only the first one is updated automatically. (From MySQL 4.1.2 on, you can specify which TIMESTAMP column updates; see Section 10.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.)

Automatic updating of the first TIMESTAMP column in a table occurs under any of the following conditions:

  • You explicitly set the column to NULL.

  • The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.

  • The column is not specified explicitly in an UPDATE statement and some other column changes value. An UPDATE that sets a column to the value it does not cause the TIMESTAMP column to be updated; if you set a column to its current value, MySQL ignores the update for efficiency.

A TIMESTAMP column other than the first also can be assigned the current date and time by setting it to NULL or to any function that produces the current date and time (NOW(), CURRENT_TIMESTAMP).

Note that the information in the following discussion applies to TIMESTAMP columns only for tables not created with MAXDB mode enabled, because such columns are created as DATETIME columns.

You can set any TIMESTAMP column to a value different from the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP column. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later:

  • Let MySQL set the column when the row is created. This initializes it to the current date and time.

  • When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value: 

    UPDATE tbl_name
    SET timestamp_col = timestamp_col,
        other_col1 = new_value1,
        other_col2 = new_value2, ...

Another way to maintain a column that records row-creation time is to use a DATETIME column that you initialize to NOW() when the row is created and do not modify for subsequent updates.

TIMESTAMP values may range from the beginning of 1970 to partway through the year 2038, with a resolution of one second. Values are displayed as numbers. When you store a value in a TIMESTAMP column, it is assumed to be represented in the current time zone, and is converted to UTC for storage. When you retrieve the value, it is converted from UTC back to the local time zone for display. Before MySQL 4.1.3, the server has a single time zone. As of 4.1.3, clients can set their own time zones on a per-connection basis, as described in Section 9.7, “MySQL Server Time Zone Support”.

Prior to version 4.1, the format in which MySQL retrieves and displays TIMESTAMP values depends on the display size, as illustrated in the following table. The “full” TIMESTAMP format is 14 digits, but TIMESTAMP columns may be created with shorter display sizes.

Data Type Display Format
TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY

All TIMESTAMP columns have the same storage size, regardless of display size. The most common display sizes are 6, 8, 12, and 14. You can specify an arbitrary display size at table creation time, but values of 0 or greater than 14 are coerced to 14. Odd-valued sizes in the range from 1 to 13 are coerced to the next higher even number.

TIMESTAMP columns store legal values using the full precision with which the value was specified, regardless of the display size. This has several implications:

  • Always specify year, month, and day, even if your column types are TIMESTAMP(4) or TIMESTAMP(2). Otherwise, the value is not a legal date and 0 is stored.

  • If you use ALTER TABLE to widen a narrow TIMESTAMP column, information is displayed that previously was “hidden.”

  • Similarly, narrowing a TIMESTAMP column does not cause information to be lost, except in the sense that less information is shown when the values are displayed.

  • If you are planning to use mysqldump for the database, do not use TIMESTAMP(4) or TIMESTAMP(2). The display format for these data types are not legal dates and 0 will be stored instead. This inconsistency is fixed starting with MySQL 4.1, where display width is ignored. To prepare for transition to versions after 4.0, you should change to use display widths of 6 or more, which will produce a legal display format. You can change the display width of TIMESTAMP data types, without losing any information, by using ALTER TABLE as indicated above.

    If you need to print the timestamps for external applications, you can use MID() to extract the relevant part of the timestamp: for example, to imitate the TIMESTAMP(4) display format.

  • Although TIMESTAMP values are stored to full precision, the only function that operates directly on the underlying stored value is UNIX_TIMESTAMP(). Other functions operate on the formatted retrieved value. This means you cannot use a function such as HOUR() or SECOND() unless the relevant part of the TIMESTAMP value is included in the formatted value. For example, the HH part of a TIMESTAMP column is not displayed unless the display size is at least 10, so trying to use HOUR() on shorter TIMESTAMP values produces a meaningless result.

In MySQL 4.1, TIMESTAMP display format changes to be the same as DATETIME, that is, as a string in 'YYYY-MM-DD HH:MM:SS' format rather than as a number in YYYYMMDDHHMMSS format. To test applications written for MySQL 4.0 for compatibility with this change, you can set the new system variable to 1. This variable is available beginning with MySQL 4.0.12. It can be set at server startup by specifying the --new option to mysqld. At runtime, a user who has the SUPER privilege can set the global value with a SET statement: 

mysql> SET GLOBAL new = 1;

Any client can set its session value of new as follows: 

mysql> SET new = 1;

The general effect of setting new to 1 is that values for existing TIMESTAMP columns display as strings rather than as numbers. Also, DESCRIBE displays the column definition as TIMESTAMP(19), rather than as TIMESTAMP(14).

The effect differs somewhat for TIMESTAMP columns that are created while new is set to 1. In this case, column values display as strings and DESCRIBE shows the definition as TIMESTAMP(19), regardless of the current value of new.

In other words, with new=1, all TIMESTAMP values display as strings and DESCRIBE shows a display width of 19. For columns created while new=1, they continue to display as strings and to have a display width of 19 even if new is set to 0.

For a TIMESTAMP column that displays as a string, you can display it as a number by retrieving it as col_name+0.


User Comments

Posted by John Hall on January 12 2005 3:37am[Delete] [Edit]

If comparing a truncated timestamp to a full timestamp, it seems only the truncated portion is matched against. For example;

mysql> CREATE TEMPORARY TABLE tmptbl (ID INT, time TIMESTAMP(8));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO tmptbl (ID,time) VALUES ('15','20050111094301');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tmptbl WHERE time='20050111102500';

+------+----------+
| ID   | time     |
+------+----------+
|   15 | 20050111 |
+------+----------+
1 row in set (0.00 sec)


Using version 4.0.20-max-log

Add your own comment.