1

Everything works as expected on my development machine, but when deploying to quality assurance environment, tests are failing because MariaDB is padding the result set with extra precision zeros after a decimal.

For example, when I run this query:

SELECT GREATEST(  '2016-04-14 15:06:30', NOW() )

On dev, running Ubuntu 14.04.4 LTS (Trusty Tahr) mysql Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.3, I get this expected result:

2016-04-18 09:07:42

On test, running Red Hat Enterprise Linux Server 7.2 (Maipo) mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1, I get this unexpected result:

2016-04-18 09:07:42.000000

I don't see anything in the GREATEST() documentation that would explain this behavior, so I think it must be a server configuration setting in my.cnf but I couldn't find anything in online search results.

Even more curiously, when running the following queries, I get the same unpadded results on both machines:

SELECT GREATEST(  '2016-04-14 15:06:30', '2016-04-14 16:06:30' )

2016-04-14 16:06:30

SELECT GREATEST(  NOW(), NOW() )

2016-04-18 09:24:22

SELECT GREATEST(  5, 10 )

10

Why is MariaDB GREATEST() padding/appending my result set with zeros on timestamp comparison with NOW()?

Improve this question
9
  • What do you get if you cast now() to timestamp or datetime(0), eg with SELECT GREATEST( '2016-04-14 15:06:30', CAST(NOW() AS TIMESTAMP))?
    – ypercubeᵀᴹ
    Commented Apr 18, 2016 at 19:15
  • select greatest('2016-04-02 12:24:52',cast(now() as datetime(0))) results in 2016-04-18 14:17:22.000000 on both servers
    – Jeff Puckett
    Commented Apr 18, 2016 at 19:18
  • Interesting. And SELECT GREATEST(NOW(), '2016-04-14 15:06:30'); ?
    – ypercubeᵀᴹ
    Commented Apr 18, 2016 at 19:32
  • SELECT GREATEST(NOW(), '2016-04-14 15:06:30'); results in 2016-04-18 14:43:16 on Ubuntu, and 2016-04-18 14:46:49.000000 on RedHat.
    – Jeff Puckett
    Commented Apr 18, 2016 at 19:48
  • 1
    The correct answer that would work in both servers would probably involve DATE_FORMAT, similarly to what is suggested in the answer but with reverse order: SELECT DATE_FORMAT( GREATEST(NOW(), '2016-04-14 15:06:30'), '%Y-%m-%d %H:%i:%s'); First compare the values, then convert to the format you want for display.
    – ypercubeᵀᴹ
    Commented Apr 18, 2016 at 20:05

1 Answer 1

Reset to default
2

The MariaDB doku claims for NOW():

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.

So this might be related to the fact that '2016-04-14 15:06:30' may look like a timestamp, but is actually a Varchar.

Try using timestamp literals:

SELECT GREATEST( TIMESTAMP '2016-04-14 15:06:30', NOW() )

Or functional conversion:

SELECT GREATEST( STR_TO_DATE('2016-04-14 15:06:31','%Y-%m-%d %H:%i:%s'), NOW() )
Improve this answer
2
  • I definitely think you're on to something, but SELECT GREATEST( TIMESTAMP '2016-04-14 15:06:30', NOW() ) still returns 2016-04-18 13:10:15.000000
    – Jeff Puckett
    Commented Apr 18, 2016 at 18:11
  • I found a solution using your recommendation by explicitly converting string to date. If you accept my edits, then I'll mark it as the answer
    – Jeff Puckett
    Commented Apr 18, 2016 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.