英文:
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 8601
Example: 2023-05-10T10:32:01Z
Query:
SELECT
STR_TO_DATE(JSON_VALUE(generalinfo, '$.Information.timestamp'), '%Y-%m-%dT%H:%i:%s.%fZ') 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)
英文:
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)
答案1
得分: 1
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"
英文:
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"
答案2
得分: 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"
英文:
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"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论