Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
5.5.29
-
None
-
None
-
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.
Attachments
Issue Links
- duplicates
-
MDEV-3813 UNIX_TIMESTAMP() reports decimal when date specified as string
- Closed
-
MDEV-4724 Some temporal functions do not preserve microseconds
- Closed
- relates to
-
MDEV-14948 UNIX_TIMESTAMP() returns decimal inconsistently
- Closed