|
UNIX_TIMESTAMP() reports a decimal value when date string specified. Verified to work different on MySQL 5.5.9 (non MariaDB)
This bug was filed directly to maria-developers @ launchpad.net list too (using mysqlbug), but didn't get any response, so filing it here instead.. Can you guys verify if this is an issue or a "feature", and also cancel the launchpad.net list request?
On MariaDB 5.5.25
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 760505
|
Server version: 5.5.25-MariaDB MariaDB Server
|
|
mdp01r:(none)> select now();
|
+---------------------+
|
| now() |
|
+---------------------+
|
| 2012-10-16 22:46:17 |
|
+---------------------+
|
1 row in set (0.00 sec)
|
|
mdp01r:(none)> select unix_timestamp();
|
+------------------+
|
| unix_timestamp() |
|
+------------------+
|
| 1350427587 |
|
+------------------+
|
1 row in set (0.00 sec)
|
|
mdp01r:(none)> select unix_timestamp(now());
|
+-----------------------+
|
| unix_timestamp(now()) |
|
+-----------------------+
|
| 1350427590 |
|
+-----------------------+
|
1 row in set (0.00 sec)
|
|
mdp01r:(none)> select unix_timestamp('2012-10-16 22:46:17');
|
+---------------------------------------+
|
| unix_timestamp('2012-10-16 22:46:17') |
|
+---------------------------------------+
|
| 1350427577.000000 |
|
+---------------------------------------+
|
1 row in set (0.00 sec)
|
On MySQL 5.5.9
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 7
|
Server version: 5.5.9 Source distribution
|
|
root@localhost/(none)> select now();
|
+---------------------+
|
| now() |
|
+---------------------+
|
| 2012-10-16 15:54:16 |
|
+---------------------+
|
1 row in set (0.00 sec)
|
|
root@localhost/(none)> select unix_timestamp();
|
+------------------+
|
| unix_timestamp() |
|
+------------------+
|
| 1350428064 |
|
+------------------+
|
1 row in set (0.00 sec)
|
|
root@localhost/(none)> select unix_timestamp(now());
|
+-----------------------+
|
| unix_timestamp(now()) |
|
+-----------------------+
|
| 1350428084 |
|
+-----------------------+
|
1 row in set (0.00 sec)
|
|
root@localhost/(none)> select unix_timestamp('2012-10-16 15:54:16');
|
+---------------------------------------+
|
| unix_timestamp('2012-10-16 15:54:16') |
|
+---------------------------------------+
|
| 1350428056 |
|
+---------------------------------------+
|
1 row in set (0.00 sec)
|
|
root@localhost/(none)>
|
More examples:
MariaDB [test]> SET @@timestamp=UNIX_TIMESTAMP('1970-01-02 03:04:05.123456'); SELECT @@timestamp; SELECT FROM_UNIXTIME(@@timestamp);
|
Query OK, 0 rows affected (0.00 sec)
|
+--------------+
|
| @@timestamp |
|
+--------------+
|
| 86645.123456 |
|
+--------------+
|
1 row in set (0.00 sec)
|
+----------------------------+
|
| FROM_UNIXTIME(@@timestamp) |
|
+----------------------------+
|
| 1970-01-02 03:04:05.000000 |
|
+----------------------------+
|
1 row in set (0.00 sec)
|
The expected result is to preserve the fractional digits .123456
The same problem happens with TIME() and TIMESTAMP().
MariaDB-10.0.3:
MariaDB [test]> select time('2012-10-16 15:54:16.12');
|
+--------------------------------+
|
| time('2012-10-16 15:54:16.12') |
|
+--------------------------------+
|
| 15:54:16.120000 |
|
+--------------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select timestamp('2012-10-16 15:54:16.12');
|
+-------------------------------------+
|
| timestamp('2012-10-16 15:54:16.12') |
|
+-------------------------------------+
|
| 2012-10-16 15:54:16.120000 |
|
+-------------------------------------+
|
1 row in set (0.00 sec)
|
MySQL-5.6:
MySQL [test]> select time('2012-10-16 15:54:16.12');
|
+--------------------------------+
|
| time('2012-10-16 15:54:16.12') |
|
+--------------------------------+
|
| 15:54:16.12 |
|
+--------------------------------+
|
1 row in set (0.00 sec)
|
|
MySQL [test]> select timestamp('2012-10-16 15:54:16.12');
|
+-------------------------------------+
|
| timestamp('2012-10-16 15:54:16.12') |
|
+-------------------------------------+
|
| 2012-10-16 15:54:16.12 |
|
+-------------------------------------+
|
1 row in set (3.49 sec)
|
The same problem happens with TIMEDIFF:
MariaDB-10.0.3:
MariaDB [test]> select timediff('10:10:10.1','00:00:00');
|
+-----------------------------------+
|
| timediff('10:10:10.1','00:00:00') |
|
+-----------------------------------+
|
| 10:10:10.100000 |
|
+-----------------------------------+
|
1 row in set (0.01 sec)
|
MySQL-5.6:
MySQL [test]> select timediff('10:10:10.1','00:00:00');
|
+-----------------------------------+
|
| timediff('10:10:10.1','00:00:00') |
|
+-----------------------------------+
|
| 10:10:10.1 |
|
+-----------------------------------+
|
1 row in set (0.00 sec)
|
The same problem happens with TIMEDIFF:
MariaDB-10.0.3:
MariaDB [test]> select time_to_sec('10:10:10');
|
+-------------------------+
|
| time_to_sec('10:10:10') |
|
+-------------------------+
|
| 36610.000000 |
|
+-------------------------+
|
1 row in set (0.00 sec)
|
MySQL-5.6:
MySQL [test]> select time_to_sec('10:10:10');
|
+-------------------------+
|
| time_to_sec('10:10:10') |
|
+-------------------------+
|
| 36610 |
|
+-------------------------+
|
1 row in set (0.00 sec)
|
These functions should be fixed to take into account the number of decimal
digits of the arguments.
|
UNIX_TIMESTAMP-MariaDB_5.5.25.txt