October 2002
Intermediate to advanced
1024 pages
27h 26m
English
You don’t like the way that MySQL displays
TIMESTAMP values.
Reformat them with
the DATE_FORMAT( ) function.
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 ...