Question 167933 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Mariadb JSON iso 8601 timestamp as datetime

I am trying to convert a json timestamp as datetime in MariaDB but running into issues. I am extracting the json values using JSON_VALUE and have tried converting the date using STR_TO_DATE which returns null (I’m guessing because of the T and Z in the timestamp).

What the data looks like in the table:

'{"Information":{"timestamp":"2022-05-03T14:50:06.782Z","Name":"Organization"}}

Datetime format: ISO 86071
Example: 2023-05-10T10:32:01Z

Query:

SELECT
  STR_TO_DATE(JSON_VALUE(generalinfo, '$.Information.timestamp'), "%y-%m-%d, %T") AS "datetime",
  JSON_VALUE(generalinfo, '$.Information.Name') AS Name,
FROM rawjson
GROUP by Name
ORDER BY datetime

That query returns NULL in the datetime field. How do I format the timestamp to remove t and z from the json data and have the date displayed as Year Month date Time (2023 05 10 11:00:00)

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After a little more experimentation, I figured this one out. It's a little messy but it works, If there's a better way to do this, I am all ears. I added a REGEXP_REPLACE to remove the T and Z characters from the timestamp, then wrapped that into STR_TO_DATE.

    STR_TO_DATE(REGEXP_REPLACE(JSON_VALUE(generalinfo, '$.Information.timestamp'), '[A-Z]',' '), '%Y-%m-%d %T') AS "datetime"

  2. 0

    No need for regexp. It’s just the format that is wrong:

    select STR_TO_DATE( '2012-06-25T20:05:13Z', '%Y-%m-%d %T'); returns null

    select STR_TO_DATE( '2022-05-03T14:50:06.782Z', '%Y-%m-%dT%H:%i:%s'); will return appropriate result.

    so in your case :

    STR_TO_DATE(JSON_VALUE(generalinfo, '$.Information.timestamp'), '%Y-%m-%d %T') AS "datetime"
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search