date_format time with ":" produces warning

having a query like this (on a MariaDB 5 database):

SELECT DATE_FORMAT(FROM_UNIXTIME(mov.date), '%H:%i') AS time
FROM ek_movement mov

displays an error / warning in phpmyadmin query editor saying, it has added a closing bracket for me. The cause of that is obviously the char ":", separating the hours from the minutes. I`ve noticed that executing queries with that issue takes much longer compared to executing a query where no such error / warning appears (for example, by changing the separator character to space). How can i fix that, is there the possibility, to escape that ":"-character? I don't want to use combinations of CONCAT to achieve what i want.

1 answer

  • answered 2018-07-19 05:10 Rick James

    There is no problem with the colon.

    • If date is a TIMESTAMP or DATETIME datatype, remove the FROM_UNIXTIME.
    • If date is a BIGINT or INT, keep the FROM_UNIXTIME.
    • If date is a DATE, you will get only '00:00' or NULL.

    Think of it this way: For most of those case, simply selecting the column gives you something that looks like a date: 2018-07-18 22:07:21, so DATE_FORMAT() makes sense to act on it.

    But for INT, a value like 1531976939 clearly needs some kind of conversion before it can be treated like a date, hence the need for FROM_UNIXTIME().