Details
-
Bug
-
Status: Closed (View Workflow)
-
Trivial
-
Resolution: Fixed
-
5.5.25, 5.3.7
-
None
-
None
Description
Neither MySQL nor MariaDB documentation specify whether datetime values with fractional seconds are allowed to be represented as strings without delimiters, e.g. 201210312359591234 which is an equivalent of 2012-10-31 23:59:59.1234. More exactly, MySQL docs still say that either YYYYMMDDHHMMSS or YYMMDDHHMMSS are supported (no microseconds mentioned), but the example below shows it's obviously not quite so.
Currently the behavior (both in MySQL and MariaDB) looks somewhat inconsistent:
create table t1 (d datetime(5));
|
# Query OK, 0 rows affected (0.10 sec)
|
|
|
insert into t1 values ('2012103123595912');
|
# Query OK, 1 row affected (0.02 sec)
|
|
|
select * from t1;
|
# +---------------------------+
|
# | d |
|
# +---------------------------+
|
# | 2012-10-31 23:59:59.12000 |
|
# +---------------------------+
|
# 1 row in set (0.00 sec)
|
|
|
insert into t1 values ('201210312359591234');
|
# Query OK, 1 row affected, 1 warning (0.04 sec)
|
|
|
show warnings;
|
# +---------+------+----------------------------------------+
|
# | Level | Code | Message |
|
# +---------+------+----------------------------------------+
|
# | Warning | 1265 | Data truncated for column 'd' at row 1 |
|
# +---------+------+----------------------------------------+
|
# 1 row in set (0.00 sec)
|
|
|
select * from t1;
|
# +---------------------------+
|
# | d |
|
# +---------------------------+
|
# | 2012-10-31 23:59:59.12000 |
|
# | 2012-10-31 23:59:59.12000 |
|
# +---------------------------+
|
# 2 rows in set (0.00 sec)
|
That is, for values up to 16 digits (14+2) the format is allowed; but if the length of the fractional part exceeds 2 digits, it gets truncated, even although the column format allows higher precision.
If it's intentional, maybe it just needs to be documented as implementation specifics.
When the same values are represented as numbers rather than strings, they are declined and converted to zero date whenever they are longer than 14 digits – that is, with any precision of the intended fractional part.