Databases Reference
In-Depth Information
Records: 4 Duplicates: 0 Warnings: 0
mysql>
SELECT * FROM mytime;
+------------+
| testtime |
+------------+
| -01:01:01 |
| 01:01:01 |
| 01:23:45 |
| 123:04:05 |
| 123:45:06 |
| -123:45:06 |
+------------+
5 rows in set (0.01 sec)
Note that hours are shown with two digits for values within the range -99 to
+99.
HHMMSS
,
MMSS
, and
SS
Punctuation can be omitted, but the digit sequences must be two, four, or six
digits in length. Note that the rightmost pair of digits is always interpreted as
a
SS
(seconds) value, the second next rightmost pair (if present) as
MM
(minutes),
and the third rightmost pair (if present) as
HH
(hours). The result is that a value
such as
1222
is interpreted as 12 minutes and 22 seconds, not 12 hours and 22
minutes.
You can also input a time by providing both a date and time in the formats described
for
DATETIME
and
TIMESTAMP
, but only the time component is stored in a
TIME
type
column. Regardless of the input type, the storage and display type is always
HH:MM:SS
. The
zero time
00:00:00
can be used to represent an unknown or dummy
value. If an input date is invalid or out of range, the zero time
00:00:00
is stored.
The
TIME
type has an additional
fraction
component for storing fractions of sec-
onds, but, while a time value can be input with a fractional component, it is pres-
ently truncated before storage by MySQL; we've therefore omitted it from our
discussions.
TIMESTAMP
Stores and displays a date and time pair in the format
YYYY-MM-DD HH:MM:SS
for the
range
1970-01-01 00:00:00
to sometime in 2037. The behavior of this type has
varied over the life of MySQL (and continues to do so!), and this section describes
only the version implemented since MySQL 4.1. The key features of a
TIMESTAMP
column are twofold. First, if you assign
NULL
to it, it's set to the current date and
time. Second, a developer-selected
TIMESTAMP
column in a table can be automati-
cally updated to the current date and time when a row is inserted or updated. You
can always explicitly set a column to a value you want by assigning that value to
the column, regardless of whether it's the automatically updating column. The
automatic update feature is discussed later in this section. A nonupdating near-
equivalent is the
DATETIME
type described later in this section.
