MySQL Cookbook

Displaying TIMESTAMP Values in Readable Form

Problem

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

Solution

Reformat them with the DATE_FORMAT( ) function.

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 ...