MySQL 5.7 JSON automatically formats time

Question

Does anyone know why the function JSON_OBJECT formats TIME-data automatically? When I do the following:

SELECT
        JSON_OBJECT(
            'Time_Transformed', TIME('11:42:00')
        ) AS myJson,
        TIME('11:42:00') AS `Time_Raw`

The result is:

myJson: {"Time_Transformed": "11:42:00.000000"}
Time_Raw: 11:42:00

What do you actually want? You want to know why this happening or how to fix this? — Bertram Gilfoyle, Apr 11 '17 at 11:55
I think this may answer why it does this: http://stackoverflow.com/a/1488415/6530134 — Timothy G., Apr 11 '17 at 11:58

score 0 · Answer 1 · answered Apr 11 '17 at 12:08

0

MySQL recognizes TIME values in several formats.

If you want to omit the fractional part, you can use this.

SELECT
    JSON_OBJECT(
        'Time_Transformed',  TIME_FORMAT(TIME('11:42:00'), '%H %i %s')
    ) AS myJson

answered Apr 11 '17 at 12:08

Bertram Gilfoyle

Is there a way to stop MySQL from doing that in the first place? I mean the formatting only happens when using JSON_OBJECT() – DataVader Apr 11 '17 at 12:46
I don't know if it is possible. But why don't you use TIME_FORMAT() as in the answer? – Bertram Gilfoyle Apr 11 '17 at 12:55
Because I have many columns and values with the type "TIME" and dont want to search for every single one of those – DataVader Apr 11 '17 at 13:19
I think it is the only possible solution. Try search and replace. – Bertram Gilfoyle Apr 11 '17 at 13:23

1 Answers1