How to query the unmodified timestamp inside a JSON object using MySQL?

Question

I would like to query a JSON object containing a MySQL TIMESTAMP named 'time':

SELECT time, JSON_OBJECT('t', time, 'v', value) AS value FROM values

But unfortunately, MySQL transforms the ISO 8601 formatted timestamp "2019-04-04T12:00:00.000Z" automatically into this presentation "2019-04-04 12:00:00.000000". See the following response:

{
  "time": "2019-04-04T12:00:00.000Z",
  "value": {
    "t": "2019-04-04 12:00:00.000000",
    "v": 30
  }
}

Is it possible to access time keeping its original type and presentation (as "2019-04-04T12:00:00.000Z") inside a JSON object? What is the recommended and clean approach to this?

Answer 1

You can use DATE_FORMAT() to make an ISO 8601 format date in an expression.

See Format date in MySQL SELECT as ISO 8601

In your case:

SELECT DATE_FORMAT(time, '%Y-%m-%dT%TZ'), 
  JSON_OBJECT('t', DATE_FORMAT(time, '%Y-%m-%dT%TZ'), 'v', value) AS value 
FROM values

Or alternatively:

SELECT v.time_8601, JSON_OBJECT('t', v.time_8601, 'v', v.value) AS value
FROM (SELECT DATE_FORMAT(time, '%Y-%m-%dT%TZ') AS time_8601, value FROM values) AS v

---

Re your comment:

https://dev.mysql.com/doc/refman/8.0/en/datetime.html says:

MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format.

The non-hacky way is to use that format. If you want to use a different format (even an ISO format), you must use the DATE_FORMAT() and STR_TO_DATE() functions.

There's a function GET_FORMAT() that returns the format string for some popular choices. But it doesn't support ISO 8601 format (that's noted specifically in the manual, as though this question has come up before).

-- formats in ISO 9075 like '%Y-%m-%d %H:%i:%s', NOT ISO 8601
DATE_FORMAT(time, GET_FORMAT(DATETIME, 'ISO'))