Database Reference
In-Depth Information
Formatting Dates and Time
In thefirst section of this chapter, we looked briefly at the temporal data types in MySQL
and MariaDB, including the formats in which dates and times are stored. I mentioned that
if you don't like those formats, there are built-in functions that may be used to return tem-
poral data in different formats. The most useful isthe
DATE_FORMAT()
function, and a
similar one,
TIME_FORMAT()
. Youcan use these to format date and time values taken
from a column, a string, or another function. With these two functions, you can specify the
format you want with simple formatting codes. Let's redo the SQL statement from the ex-
ample at the end of the previous section, using these functions:
SELECT common_name AS 'Endangered Bird',
CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
DATE_FORMAT(time_seen, '%W, %M %e, %Y') AS 'Date Spotted',
TIME_FORMAT(time_seen, '%l:%i %p') AS 'Time Spotted'
FROM bird_sightings
JOIN humans USING(human_id)
JOIN rookery.birds USING(bird_id)
JOIN rookery.conservation_status USING(conservation_status_id)
WHERE conservation_category = 'Threatened' LIMIT 3;
+---------------------+--------------+----------------------------+-----------+
| Endangered Bird | Birdwatcher | Date Spotted |
Time |
+---------------------+--------------+----------------------------+-----------+
| Eskimo Curlew | Elena Bokova | Tuesday, October 1, 2013 |
5:09 AM |
| Red-billed Curassow | Marie Dyer | Wednesday, October 2, 2013 |
7:39 AM |
| Red-billed Curassow | Elena Bokova | Wednesday, October 2, 2013 |
8:41 AM |
+---------------------+--------------+----------------------------+-----------+
This is still a hefty SQL statement, but the portions related to formatting the date and time
is more straightforward. With the
DATE_FORMAT()
and the
TIME_FORMAT()
func-
tions, you give the column to format as the first argument and then provide a string in
quotes that contains formatting codes and text to lay out how you want the date and time
formatted. Incidentally, the
DATE_FORMAT()
function will return times in addition to
dates. So there's really no need to use
TIME_FORMAT()
. It's just a matter of style.
The problems we had in the previous two examples (i.e., lack of padding for minutes, no
colon, and the need for
IF()
to indicate morning or evening), doesn't exist here. We took
care of all of that by using the
'%l:%i %p'
formatting codes. If we were willing to in-
