I would like to query this column that is a JSON object.
| x | y | z |
----------------------------------------------------------------
| 1 | first | {"first_name":["Pat","Amy T."],"last_name":["Liman","Pedano"],"updated_at":["1989-11-30T19:00:00.000-05:00","2018-10-11T12:45:44.674-04:00"]} |
| 2 | second | {"first_name":["Elli","Ala"],"last_name":["Sheppard","Mcgrial"],"updated_at":["2019-03-16T11:15:25.714-04:00","2019-05-21T11:29:00.684-04:00"]} |
where z is a json object.
I tried
SELECT x, y, t1.first_name, t1.last_name, t1.updated_at
FROM versions_table v
LATERAL VIEW json_tuple(v.z, 'first_name', 'last_name', 'updated_at') t1
as `first_name`, `last_name`, `updated_at`
where first_name is not null;
but I'm getting an json array. What would be the best approach to change the date format for
the output to be like this 2019-01-03.
Thank you!
Your date string is in "combined timestamp" format. To my understanding, you're just trying to cast that string into a date type.
PostgreSQL
The following example works and will cast your string into the date you want:
SELECT '2018-10-11T12:45:44.674-04:00'::date
You can learn some more about casting in this tutorial. :: is the cast operator.
Another option would be to use the to_date function or similar:
SELECT to_date('2018-10-11T12:45:44.674-04:00', 'YYYY-MM-DD');
MySQL
In MySQL you can use the DATE function
SELECT DATE('2018-10-11T12:45:44.674-04:00')
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments