Converting string extracted from json value to date returning NULL

Question

I'm saving a date in one db field in json format. The json value looks like:

[{"key":"jkajdajsdiajsdik"},{"created_date":"2018-01-17 15:51:23"}]

I want to extract the created_date from json through sql and i'm getting it through the following query.

select SUBSTRING_INDEX(SUBSTRING_INDEX(testjson, 'created_date\":', -1),'}',1) as created_date from test

The above query returns

"2018-01-17 15:51:23"

Now I'm trying to convert this returned string to a date.

select STR_TO_DATE(SUBSTRING_INDEX(SUBSTRING_INDEX(testjson, 'created_date\":', -1),'}',1),'%Y-%m-%d %H:%i:%s') as created_date from test

But this is returning NULL. When I tried with a sample

SELECT STR_TO_DATE(" 2018-01-17 15:51:23",'%Y-%m-%d %H:%i:%s')

Its returning

2018-01-17 15:51:23

What am I doing wrong? Can anyone help me to fix this?

Thanks in advance

What is your MySQL server version ? Newer versions have JSON specific functions available. — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 27, 2018 at 12:34

Madhur Bhaiya · Accepted Answer · 2018-11-27 12:58:47Z

3

The date returned after Substring_Index() operations also contains double quotes. I added double quotes in the format specified for the Str_to_Date() function:

select STR_TO_DATE(
           SUBSTRING_INDEX(
               SUBSTRING_INDEX(testjson, 'created_date\":', -1),'}'
               ,1)
           ,'"%Y-%m-%d %H:%i:%s"') 
AS created_date from test

Result

| created_date        |
| ------------------- |
| 2018-01-17 15:51:23 |

View on DB Fiddle

However, in version 5.7 and above, it would be better to utilize JSON functions instead. We can use Json_Extract() function to get the value corresponding to created_date key, and the use the Str_To_Date() function.

Query #2

SET @j := '[{"key":"jkajdajsdiajsdik"},{"created_date":"2018-01-17 15:51:23"}]';

SELECT STR_TO_DATE(JSON_EXTRACT(@j, '$[*].created_date'), '["%Y-%m-%d %H:%i:%s"]') AS created_date;

Result

| created_date        |
| ------------------- |
| 2018-01-17 15:51:23 |

View on DB Fiddle

edited Nov 27, 2018 at 12:58

answered Nov 27, 2018 at 12:41

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

1 Comment

Madhur Bhaiya Over a year ago

@Jenz happy to help. Check the updated answer; it will be better to use JSON functions, if your MySQL version supports it.

Salman Arshad · Accepted Answer · 2018-11-27 12:41:22Z

2

Your code is also including ". Make this small change to your code to exclude them:

select SUBSTRING_INDEX(SUBSTRING_INDEX(testjson, 'created_date":"', -1),'"}',1) as created_date from test
--                                                              ^        ^
-- -------------------------------------------------------------+        |
-- ----------------------------------------------------------------------+

answered Nov 27, 2018 at 12:41

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Collectives™ on Stack Overflow

Converting string extracted from json value to date returning NULL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related