Extract date & time from JSON data in mysql

Question

I have the following data,

{"value": 0, "action": "UPDATED", "effectiveFrom": "2019-01-31T07:27:13.000Z"}

I am trying to extract the time value using the below SQL,

SELECT CASE
         WHEN JSON_EXTRACT(changeData, '$.action') = 'UPDATED' THEN
          time(replace(JSON_EXTRACT(changeData, '$.effectiveFrom'), 'T', ' '))
         ELSE
          'N'
       END AS ACTION
  FROM change

I used the replace function having read here that for mysql the 'T' should be replaced. My sql is returning null.

@BarbarosÖzhan I just noticed that the time function returns 00:20:19.000000 for the sample data i gave but it is 07:27:13.000. Is there anything i missed ? — Bisoux
– Bisoux, Commented Jul 22, 2020 at 12:18

Barbaros Özhan · Accepted Answer · 2020-07-22 12:32:57Z

1

You also need to replace double-quote and Z characters with '' or ' ' while replacing T with ' ' character for the time literal as

SELECT JSON_EXTRACT(changeData, '$.value') AS value,
       CASE
        WHEN JSON_EXTRACT(changeData, '$.action') = 'UPDATED' 
        THEN 
            TIME(
            REPLACE(REPLACE(REPLACE(
                                    JSON_EXTRACT(changeData, '$.effectiveFrom')
            ,'"',''),"Z",''),"T",' ')
            )
       ELSE 'N'
      END AS action
  FROM `change`

Demo

edited Jul 22, 2020 at 12:32

answered Jul 22, 2020 at 12:07

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

1 Comment

Bisoux Over a year ago

The time function returns 00:20:19.000000 for the sample data i gave but it should return 07:27:13.000.

Collectives™ on Stack Overflow

Extract date & time from JSON data in 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