You are Here: FAQ Scripting and Programming Languages MySQL Database Article #16

Changes to TIMESTAMP in MySQL 5

Queries of TIMESTAMP fields now return values in a new format that conforms to SQL92.

Up to 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 ( http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html ) 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 recognizes.

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 http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

The SQL command:
SELECT DATE_FORMAT(when,'%y%m%d')
returns the field "when" as shown in TIMESTAMP(6) in the format YYMMDD.

Print Article

Feedback

Overview	Search
Sitemap	Glossary