Mysql – Why is MySQL MariaDB GREATEST(timestamp,NOW()) padding appending results with zeros

mariadbMySQL

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()?

Best Answer

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() )
Related Question