clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799322 fiddles created (41679 in the last week).

CREATE TABLE `change` ( changeData VARCHAR(200) );
 hidden batch(es)


INSERT INTO `change` VALUES ('{"value": 0, "action": "UPDATED", "effectiveFrom": "2019-01-31T07:28:13.000Z"}'), ('{"value": 1, "action": "DELETED", "effectiveFrom": "2019-01-31T07:27:13.000Z"}')
 hidden batch(es)


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`
value action
0 07:28:13.000000
1 N
value action
0 07:28:13.000000
1 N
 hidden batch(es)