Learning Mysql

mysql> SELECT * FROM testdate;
+------------+
| mydate |
+------------+
| 2007-02-00 |
| 2007-02-01 |
| 2007-02-31 |
| 0000-00-00 |
+------------+
4 rows inset sec)
while with version 5.0.2 onwards, we have:
mysql> SELECT * FROM testdate;
+------------+
| mydate |
+------------+
| 2007-02-00 |
| 2007-02-01 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
4 rows inset sec)
Note also that the date is displayed in the
YYYY-MM-DD
format, regardless of how it was input.
TIME
Stores a time in the format
HHH:MM:SS
for the range to
838:59:59
. The values that can be stored are outside the range of the hour clock to allow large differences between time values (up to 34 days, 22 hours, 59 minutes, and 59 seconds) to be computed and stored. Times must always be input in the order days,
hours, minutes, and seconds, using the following formats:
DD HH:MM:SS
,
HH:MM:SS
,
DD HH:MM
,
HH:MM
,
DD HH
, or
SS
The
DD
represents a one-digit or two-digit value of days in the range 0 to The
DD
value is separated from the hour value,
HH
, by a space, while the other components are separated by a colon. Note that
MM:SS
is not a valid combination, since it cannot be disambiguated from
HH:MM
For example, if you insert
2 into a
TIME
type column, the value
61:25:59
is stored, since the sum of 2 days (48 hours) and 13 hours is 61 hours.
If you try inserting a value that’s out of bounds, a warning is generated, and the value is limited to the maximum time available. Similarly, if you try inserting an incorrect value, a warning is generated and the value is set to zero.
You can use the SHOW WARNINGS
command to reports the details of the warning generated by the previous SQL statement.
Let’s try all these out in practice:
mysql CREATE TABLE test_time(id SMALLINT, mytime TIME);
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO test_time VALUES, "2 13:25:59");

Download 4.24 Mb.

Share with your friends: