Working with Dates and Times - MySQL - page 183

mycol TIME(2)
As an example, the 2014 Winter Olympics in Sochi are underway as I write. Scores for
some Olympic events are measured as elapsed time, and events vary in the resolution
used. The following table shows some representative events, their required time reso‐
lution for scores, and the TIME declaration appropriate for recording times of the com‐
petitors:

Event Resolution Data type

Biathlon Tenths

TIME(1)

Downhill skiing Hundredths TIME(2)

Luge, skeleton Thousandths TIME(3)

Temporal functions that return current time or date-and-time values also support frac‐
tional seconds. The default without an argument is no fractional part. Otherwise, the
argument specifies the desired resolution. Permitted values are 0 to 6, the same as when
declaring temporal columns:

mysql> SELECT CURTIME(), CURTIME(2), CURTIME(6);
+-----------+-------------+-----------------+
| CURTIME() | CURTIME(2) | CURTIME(6) |
+-----------+-------------+-----------------+
| 18:07:03 | 18:07:03.24 | 18:07:03.244950 |
+-----------+-------------+-----------------+

6.3. Changing MySQL's Date Format

Problem
You want to change the ISO format that MySQL uses for representing date values.

Solution
You can't. However, you can rewrite non-ISO input values into ISO format when storing
dates, and you can rewrite ISO values to other formats for display with the DATE_FOR
MAT() function.

Discussion
The CCYY-MM-DD format that MySQL uses for DATE values follows the ISO 8601 standard
for representing dates. Because the year, month, and day parts have a fixed length and
appear left to right in date strings, this format has the useful property that dates sort
naturally into the proper temporal order. Recipes 7.5 and 8.12 discuss ordering and
grouping techniques for date-based values.