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

27
April 15, 2019, at 11:40 PM

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?

READ ALSO
Multiple row INSERT from a single SELECT result

Multiple row INSERT from a single SELECT result

I am inserting multiple rows into a table, using the result from a subquery :

8
Equivalent Query from MySQL to PostgreSQL

Equivalent Query from MySQL to PostgreSQL

I'm switching from MySQL to PostgreSQL and trying to add a UNIQUE INDEX

16
MySQL / MariaDB column value unique in different column

MySQL / MariaDB column value unique in different column

I have a database schema for user login data that stores an E-Mail address for each userNaturally, this E-Mail needs to be unique because each user should only have one account

12
MySQL duplicate entry when updating multiple rows

MySQL duplicate entry when updating multiple rows

I have a table of items where each item has an itemOrderitemOrder is part of the PRIMARY KEY

32