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?

Bill Karwin · Accepted Answer · 2019-04-17 15:42:50Z

2

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'))

edited Apr 17, 2019 at 15:42

answered Apr 16, 2019 at 18:52

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jengeb Over a year ago

Thanks, that works. But I wonder if this is the 'recommended' way, because it seems a bit hacky – like a workaround – to me. Do you know why MySQL modifies the time (with type TIMESTAMP) and outputs it in different ways – depending on the uage of JSON_OBJECT?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related