CODEWITHSUNDEEP
mysqlsql
ak93
ak93

Reputation: 1309

MySQL JSON_OBJECT() datetime format

I'm creating a JSON structure by using a JSON_OBJECT and JSON_ARRAY functions in my select query. The problem I have is with the format in which datetime columns are returned in the formatted JSON result.

For example I have a table titles

id (INT), title (VARCHAR), created_at (DATETIME)

and a row would be like this

1,"Title 1","2019-02-03 12:13:14"

If I now do the following query

SELECT JSON_OBJECT('title',title,'created_at',created_at) AS title_json FROM titles WHERE id = 1;

I will get the resulting title_json column as:

{
    "title": "Title 1",
    "created_at": "2019-02-03 12:13:14.000000"
}

I would like to have the datetime values returned in the standard YYYY-MM-DD HH:ii:ss format, without the trailing zeroes.

Is this possible?

I have looked through the JSON_OBJECT documentation but couldn't find any clues to this mystery. I'm thinking the format used might be defined somewhere in the server/database settings. The ideal solution for my case would be to optionally set the desired format in individual queries themselves.

I'm using: Server: MySQL Community Server (GPL) Version: 5.7.24

Upvotes: 6

Views: 5823

Answers (2)

DarthJDG
DarthJDG

Reputation: 16591

While the accepted solution is versatile, if you just want to use the default string representation, you can simply cast the value to a string before passing it into the JSON_OBJECT.

SELECT
    JSON_OBJECT(
        'title', title,
        'created_at', CAST(created_at AS CHAR)
    ) AS title_json
FROM titles
WHERE id = 1;

Note that the default string representation of DATETIME is the desired YYYY-MM-DD hh:mm:ss, but it does support fractional seconds as an option with a precision of up to 6 decimal places.

This means that if the field's data type is e.g. DATETIME(3), it will be converted to the default format of YYYY-MM-DD hh:mm:ss.fff. In this case explicit formatting is the only option.

When you put a DATETIME value directly into a JSON_OBJECT, MySQL automatically casts it into the JSON datatype. This results in a string showing the full 6 digits of precision, regardless of the precision defined by the field type.

The link below is the only place I've found this behaviour mentioned in the documentation, but it doesn't go into detail or explain what is actually happening:

https://dev.mysql.com/doc/refman/8.4/en/json.html#json-values

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use DATE_FORMAT

SELECT JSON_OBJECT('title',title,'created_at',
                    DATE_FORMAT(created_at, "%Y-%c-%d %H:%i:%s")) AS title_json 
  FROM titles 
 WHERE id = 1;

Upvotes: 6

Related Questions