Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-3922

UNIX_TIMESTAMP() reports decimal when date specified as string

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 5.5.29
    • Fix Version/s: 5.5.33, 5.3.13
    • 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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              rert Robert Krzykawski
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.