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

timediff returns null when comparing decimal time to time string value

Details

    • 10.0.20

    Description

      timediff function does not correctly convert decimal values to datetime when comparing a decimal time to a string time, returning null. Problem does not manifest in the datediff function.

      select 
      	timediff('2014-01-01 00:00:00' , '2014-01-01 01:00:00' ),
          timediff(20140101000000.000 , 20140101010000.000  ),
      	timediff(20140101000000.000 , '2014-01-01 01:00:00' ),
      	datediff('2014-01-01 00:00:00' , '2014-01-02 01:00:00' ),
          datediff(20140101000000.000 , 20140102010000.000  ),
      	datediff(20140101000000.000 , '2014-01-02 01:00:00' );
          

      Attachments

        Activity

          Thanks for the report.

          elenst Elena Stepanova added a comment - Thanks for the report.

          There is a related problem.

          In string notation, TIMEDIFF takes into account the date part:

          MariaDB [test]> select timediff('2001-01-01 10:20:30','2001-03-02 10:20:31');
          +-------------------------------------------------------+
          | timediff('2001-01-01 10:20:30','2001-03-02 10:20:31') |
          +-------------------------------------------------------+
          | -838:59:59                                            |
          +-------------------------------------------------------+
          1 row in set, 1 warning (0.00 sec)

          while in numeric notation it ignores the date part:

          MariaDB [test]> select timediff(20010101102030,20010302102031);
          +-----------------------------------------+
          | timediff(20010101102030,20010302102031) |
          +-----------------------------------------+
          | -00:00:01                               |
          +-----------------------------------------+
          1 row in set (0.00 sec)

          bar Alexander Barkov added a comment - There is a related problem. In string notation, TIMEDIFF takes into account the date part: MariaDB [test]> select timediff('2001-01-01 10:20:30','2001-03-02 10:20:31'); +-------------------------------------------------------+ | timediff('2001-01-01 10:20:30','2001-03-02 10:20:31') | +-------------------------------------------------------+ | -838:59:59 | +-------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) while in numeric notation it ignores the date part: MariaDB [test]> select timediff(20010101102030,20010302102031); +-----------------------------------------+ | timediff(20010101102030,20010302102031) | +-----------------------------------------+ | -00:00:01 | +-----------------------------------------+ 1 row in set (0.00 sec)

          People

            bar Alexander Barkov
            MarkP Mark Punak
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.