5.3 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:
|
%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 |
+---------------------+--------------+--------------+
|
No comments:
Post a Comment