# Converting json timestamp to different timezone

**URL:** <https://forums.percona.com/t/converting-json-timestamp-to-different-timezone/18460>\
**Category:** Other MySQL® Questions\
**Created:** [November 9, 2022, 3:18pm UTC](https://forums.percona.com/t/converting-json-timestamp-to-different-timezone/18460 "2022-11-09T15:18:38Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ipcmlr](https://avatars.discourse-cdn.com/v4/letter/i/df705f/32.png) [@ipcmlr](https://forums.percona.com/u/ipcmlr)\
**Post date:** [November 9, 2022, 3:18pm UTC](https://forums.percona.com/t/converting-json-timestamp-to-different-timezone/18460/1 "2022-11-09T15:18:38Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [November 9, 2022, 6:08pm UTC](https://forums.percona.com/t/converting-json-timestamp-to-different-timezone/18460/2 "2022-11-09T18:08:04Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ipcmlr](https://avatars.discourse-cdn.com/v4/letter/i/df705f/32.png) [@ipcmlr](https://forums.percona.com/u/ipcmlr)\
**Post date:** [November 9, 2022, 6:17pm UTC](https://forums.percona.com/t/converting-json-timestamp-to-different-timezone/18460/4 "2022-11-09T18:17:31Z")

</div>

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.