MySQL Cookbook

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