Converting json timestamp to different timezone

MySQL & MariaDB Other MySQL® Questions
1

I am trying to convert a json timestamp to a different timezone.
if i put the literal date it works fine but returns a null when i use a json_extract nested inside the convert_tz.
Is it possible to nest the json_extract in convert_tz?

root@localhost percona> select json_extract(info, “$.audit_record.timestamp”) from audit_data where id = (select max(id) from percona.audit_data);
±-----------------------------------------------+
| json_extract(info, “$.audit_record.timestamp”) |
±-----------------------------------------------+
| “2022-11-08T22:55:30Z” |
±-----------------------------------------------+
1 row in set (0.00 sec)

root@localhost percona> select convert_tz(“2022-11-08T22:55:30Z”,‘+00:00’, ‘-7:00’);
±-----------------------------------------------------+
| convert_tz(“2022-11-08T22:55:30Z”,‘+00:00’, ‘-7:00’) |
±-----------------------------------------------------+
| 2022-11-08 15:55:30 |
±-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

root@localhost percona> select CONVERT_TZ(json_extract(info, “$.audit_record.timestamp”),‘+00:00’,‘-7:00’) from audit_data where id = (select max(id) from percona.audit_data);
±----------------------------------------------------------------------------+
| CONVERT_TZ(json_extract(info, “$.audit_record.timestamp”),‘+00:00’,‘-7:00’) |
±----------------------------------------------------------------------------+
| NULL |
±----------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

1 Like
2

Try using JSON_UNQUOTE(JSON_EXTRACT(...)) because there are quotes around your timestamp in the first example. Also, you have warnings on the last 2 examples. Type SHOW WARNINGS and see what else is wrong.

1 Like
4

thanks for being eagle eyed vs my being blind to the quotes.
pretty sure you are correct.

also the warning is “Truncated incorrect datetime value: ‘2022-11-08T22:55:30Z’”
but that is ok for me.