Telling MySQL How to Display Dates or Times
5.3.1 Problem
You want to display dates or times in a format other than what MySQL uses by default.
5.3.2 Solution
Use the DATE_FORMAT( ) or TIME_FORMAT( ) functions to rewrite them.
5.3.3 Discussion
As already noted, MySQL displays dates in ISO format unless you tell it otherwise. To rewrite date values into other formats, use the DATE_FORMAT( ) function, which takes two arguments: a DATE, DATETIME, or TIMESTAMP value, and a string describing how to display the value. Within the formatting string, you indicate what to display using special sequences of the form %c, where c specifies which part of the date to display. For example, %Y, %M, and %d signify the four-digit year, the month name, and the two-digit day of the month. The following query shows the values in the date_val table, both as MySQL displays them by default and as reformatted with DATE_FORMAT( ):
mysql> SELECT d, DATE_FORMAT(d,'%M %d, %Y') FROM date_val; +------------+----------------------------+ | d | DATE_FORMAT(d,'%M %d, %Y') | +------------+----------------------------+ | 1864-02-28 | February 28, 1864 | | 1900-01-15 | January 15, 1900 | | 1987-03-05 | March 05, 1987 | | 1999-12-31 | December 31, 1999 | | 2000-06-04 | June 04, 2000 | +------------+----------------------------+
Clearly, DATE_FORMAT( ) tends to produce rather long column headings, so it's often useful to provide an alias to make a heading more concise or meaningful:
mysql> SELECT d, DATE_FORMAT(d,'%M %d, %Y') AS date FROM date_val; +------------+-------------------+ | d | date | +------------+-------------------+ | 1864-02-28 | February 28, 1864 | | 1900-01-15 | January 15, 1900 | | 1987-03-05 | March 05, 1987 | | 1999-12-31 | December 31, 1999 | | 2000-06-04 | June 04, 2000 | +------------+-------------------+
The MySQL Reference Manual provides a complete list of format sequences. Some of the more common ones are shown in the following table:
Sequence |
Meaning |
---|---|
%Y |
Four-digit year |
%y |
Two-digit year |
%M |
Complete month name |
%b |
Month name, initial three letters |
%m |
Two-digit month of year (01..12) |
%c |
Month of year (1..12) |
%d |
Two-digit day of month (01..31) |
%e |
Day of month (1..31) |
%r |
12-hour time with AM or PM suffix |
%T |
24-hour time |
%H |
Two-digit hour |
%i |
Two-digit minute |
%s |
Two-digit second |
%% |
Literal % |
The time-related format sequences shown in the table are useful only when you pass DATE_FORMAT( ) a value that has both date and time parts (a DATETIME or TIMESTAMP). The following query demonstrates how to display DATETIME values from the datetime_val table using formats that include the time of day:
mysql> SELECT dt, -> DATE_FORMAT(dt,'%c/%e/%y %r') AS format1, -> DATE_FORMAT(dt,'%M %e, %Y %T') AS format2 -> FROM datetime_val; +---------------------+----------------------+----------------------------+ | dt | format1 | format2 | +---------------------+----------------------+----------------------------+ | 1970-01-01 00:00:00 | 1/1/70 12:00:00 AM | January 1, 1970 00:00:00 | | 1987-03-05 12:30:15 | 3/5/87 12:30:15 PM | March 5, 1987 12:30:15 | | 1999-12-31 09:00:00 | 12/31/99 09:00:00 AM | December 31, 1999 09:00:00 | | 2000-06-04 15:45:30 | 6/4/00 03:45:30 PM | June 4, 2000 15:45:30 | +---------------------+----------------------+----------------------------+
TIME_FORMAT( ) is similar to DATE_FORMAT( ), but understands only time-related specifiers in the format string. TIME_FORMAT( ) works with TIME, DATETIME, or TIMESTAMP values.
mysql> SELECT dt,
-> TIME_FORMAT(dt, '%r') AS '12-hour time',
-> TIME_FORMAT(dt, '%T') AS '24-hour time'
-> FROM datetime_val;
+---------------------+--------------+--------------+
| dt | 12-hour time | 24-hour time |
+---------------------+--------------+--------------+
| 1970-01-01 00:00:00 | 12:00:00 AM | 00:00:00 |
| 1987-03-05 12:30:15 | 12:30:15 PM | 12:30:15 |
| 1999-12-31 09:00:00 | 09:00:00 AM | 09:00:00 |
| 2000-06-04 15:45:30 | 03:45:30 PM | 15:45:30 |
+---------------------+--------------+--------------+
Категории