1and1 Help Centre Categories

print article

Changes to TIMESTAMP in MySQL 5

Queries of TIMESTAMP fields now return values in a different, more SQL92-compliant format. Up until MySQL 4.1, the TIMESTAMP was returned in the format YYYYMMDDHHMMSS (e.g. 20080218104114).

Since MySQL 4.1, these fields have been returned in the format YYYY-MM-DD HH:MM:SS (e.g. 2008-02-18 10:41:14). Modern applications use the Date and Time Functions to fetch data from the SQL server in the format required by the application.

In contrast, older applications parse these values, meaning that either the parser or the SQL fragment needs to be modified for them to be able to handle the new format. Such modification or reprogramming is therefore important and may be necessary in a lot of applications.

An example of a "simple" modification would be:
SELECT DATE_FORMAT(when,'%Y%m%d%H%i%s') as when from table instead of SELECT when from table. This causes the contents of the TIMESTAMP field to be returned in a format that the application recognises.
Please note:
Truncation of TIMESTAMP fields (i.e. the use of different timestamp lengths) is no longer supported as of MySQL 4.1.
Up to MySQL 4.1, the following field types returned the following strings:
Field type String
TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY

As of MySQL 4.1, the timestamp is always returned as a string with the format YYYY-MM-DD HH:MM:SS.

If you absolutely need shorter or more specific formats, then it is necessary to generate the required format using the Date and Time Functions.

Example:
SELECT DATE_FORMAT(when,'%y%m%d') returns the field "when" as shown in TIMESTAMP(6) in the format YYMMDD.
For additional information, you may want to reference: