Timeline for Why is MySQL MariaDB GREATEST(timestamp,NOW()) padding appending results with zeros?
Current License: CC BY-SA 3.0
14 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 18, 2016 at 20:05 | comment | added | ypercubeᵀᴹ |
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.
|
|
| Apr 18, 2016 at 20:01 | comment | added | Jeff Puckett |
I assume you didn't mean that first comma, which resulted in error, but SELECT GREATEST(NOW(), TIMESTAMP '2016-04-14 15:06:30') on Maria results in 2016-04-18 14:59:50.000000, which is surprising considering the timestamp literal from the posted answer doesn't work, while STR_TO_DATE does.
|
|
| Apr 18, 2016 at 19:58 | comment | added | ypercubeᵀᴹ |
Sorry for the many comments, I just don't have MariaDB available at the moment to test. Can you try a last one (in MariaDB)?: SELECT GREATEST(NOW(), TIMESTAMP '2016-04-14 15:06:30');
|
|
| Apr 18, 2016 at 19:52 | comment | added | Jeff Puckett |
SELECT GREATEST( '2016-04-14 15:06:30', CAST(NOW() AS TIMESTAMP)) results in an error on both machines: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TIMESTAMP))' at line 1
|
|
| Apr 18, 2016 at 19:50 | comment | added | Jeff Puckett |
I apologize for my previous comment, that was only the result on RedHat. select greatest('2016-04-02 12:24:52',cast(now() as datetime(0))) on Ubuntu results in an error 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(0)))' at line 1
|
|
| Apr 18, 2016 at 19:48 | comment | added | Jeff Puckett |
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.
|
|
| Apr 18, 2016 at 19:32 | comment | added | ypercubeᵀᴹ |
Interesting. And SELECT GREATEST(NOW(), '2016-04-14 15:06:30'); ?
|
|
| Apr 18, 2016 at 19:18 | comment | added | Jeff Puckett |
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
|
|
| Apr 18, 2016 at 19:15 | comment | added | ypercubeᵀᴹ |
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))?
|
|
| Apr 18, 2016 at 19:13 | vote | accept | Jeff Puckett | ||
| Apr 18, 2016 at 17:51 | answer | added | dnoeth | timeline score: 2 | |
| Apr 18, 2016 at 15:15 | history | edited | Jeff Puckett | CC BY-SA 3.0 |
question in title
|
| Apr 18, 2016 at 14:43 | review | First posts | |||
| Apr 18, 2016 at 15:01 | |||||
| Apr 18, 2016 at 14:35 | history | asked | Jeff Puckett | CC BY-SA 3.0 |