Working with Dates and Times - MySQL - page 186

Sequence Meaning

Two-digit hour

%H

Two-digit minute

%i

Two-digit second

%s

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 statement displays 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 |
| 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 |
| 2017-03-16 12:30:15 | 3/16/17 12:30:15 PM | March 16, 2017 12:30:15 |
+---------------------+----------------------+----------------------------+
TIME_FORMAT() is similar to DATE_FORMAT() . It works with TIME , DATETIME , or TIME
STAMP values, but understands only time-related specifiers in the format string:

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 |
| 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 |
| 2017-03-16 12:30:15 | 12:30:15 PM | 12:30:15 |
+---------------------+--------------+--------------+
If DATE_FORMAT() or TIME_FORMAT() cannot produce the results that you want, write a
stored function that does. Suppose that you want to convert 24-hour TIME values to 12-
hour format but with a suffix of a.m. or p.m. rather than AM or PM . The following function
accomplishes that task. It uses TIME_FORMAT() to do most of the work, then strips the
suffix supplied by %r and replaces it with the desired suffix:

CREATE FUNCTION time_ampm ( t TIME )
RETURNS VARCHAR ( 13 ) # mm : dd : ss { a . m . | p . m . } format
DETERMINISTIC