Displaying TIMESTAMP Values in Readable Form

5.36.1 Problem

You don't like the way that MySQL displays TIMESTAMP values.

5.36.2 Solution

Reformat them with the DATE_FORMAT( ) function.

5.36.3 Discussion

TIMESTAMP columns have certain desirable properties, but one that sometimes isn't so desirable is the display format (CCYYMMDDhhmmss). As a long unbroken string of digits, this is inconsistent with DATETIME format (CCYY-MM-DD hh:mm:ss) and is also more difficult to read. To rewrite TIMESTAMP values into DATETIME format, use the DATE_FORMAT( ) function. The following example uses the tsdemo2 table from Recipe 5.34:

mysql> SELECT t_create, DATE_FORMAT(t_create,'%Y-%m-%d %T') FROM tsdemo2; +----------------+-------------------------------------+ | t_create | DATE_FORMAT(t_create,'%Y-%m-%d %T') | +----------------+-------------------------------------+ | 20020715120003 | 2002-07-15 12:00:03 | +----------------+-------------------------------------+

You can go in the other direction, too (to display DATETIME values in TIMESTAMP format), though this is much less common. One way is to use DATE_FORMAT( ); another that's simpler is to add zero:

mysql> SELECT dt, -> DATE_FORMAT(dt,'%Y%m%d%H%i%s'), -> dt+0 -> FROM datetime_val; +---------------------+--------------------------------+----------------+ | dt | DATE_FORMAT(dt,'%Y%m%d%H%i%s') | dt+0 | +---------------------+--------------------------------+----------------+ | 1970-01-01 00:00:00 | 19700101000000 | 19700101000000 | | 1987-03-05 12:30:15 | 19870305123015 | 19870305123015 | | 1999-12-31 09:00:00 | 19991231090000 | 19991231090000 | | 2000-06-04 15:45:30 | 20000604154530 | 20000604154530 | +---------------------+--------------------------------+----------------+

See Recipe 5.3 for more information about rewriting temporal values in whatever format you like.