5.36 Displaying TIMESTAMP Values in Readable Form

I l@ve RuBoard

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.

I l@ve RuBoard