Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Duplicate
-
Affects Version/s: 5.5.29
-
Component/s: None
-
Labels:None
-
Environment:CentOS release 5.8 (Final) - Linux mdp01r.prod.marinsw.net 2.6.18-308.11.1.el5 #1 SMP Tue Jul 10 08:48:43 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
Description
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.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
let's treat it as a bug for now. MySQL 5.6 does not exhibit this behavior.
The patch for MDEV-4724 fixed all problems described in this bug report.
Terminal output looks strange in jira, so attaching text file for formatting sake.