Working with Dates and Times - MySQL - page 182

Many of the examples in this chapter draw on the following tables, which contain col‐
umns representing time, date, and date-and-time values. (The time_val table has two
columns for use in time interval calculation examples.)
mysql> SELECT t1, t2 FROM time_val;
+----------+----------+
| t1 | t2 |
+----------+----------+
| 15:00:00 | 15:00:00 |
| 05:01:30 | 02:30:20 |
| 12:30:20 | 17:30:45 |
+----------+----------+
mysql> SELECT d FROM date_val;
+------------+
| d |
+------------+
| 1864-02-28 |
| 1900-01-15 |
| 1999-12-31 |
| 2000-06-04 |
| 2017-03-16 |
+------------+
mysql> SELECT dt FROM datetime_val;
+---------------------+
| dt |
+---------------------+
| 1970-01-01 00:00:00 |
| 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 |
| 2017-03-16 12:30:15 |
+---------------------+
It is a good idea to create the time_val , date_val , and datetime_val tables right now
before reading further. (Use the appropriate scripts in the tables directory of the rec
ipes distribution.)
6.2. Using Fractional Seconds Support
As of MySQL 5.6.4, fractional seconds are supported for temporal types that include a
time part: DATETIME , TIME , and TIMESTAMP . For applications that require subsecond
resolution of time values, this enables you to specify fractional seconds precision down
to the microsecond level.
The default is to have no fractional seconds part, so to include it for temporal types that
support this capability, specify it explicitly in the column declaration: include ( fsp )
after the data type name in a column definition. fsp can be from 0 to 6 to indicate the
number of fractional digits. 0 means “none” (resolution to seconds), 6 means resolution
to microseconds. For example, to create a TIME column with two fractional digits (res‐
olution to hundredths of seconds), use this syntax: