STR_TO_DATE with timezone

Question

I my MySQL (version 5.7.25) database there's have a column, which is full of varchar(100) dates like Fri May 04 08:08:42 UTC 2018. I need to convert them to date.

So far I've came up with

SELECT STR_TO_DATE('Fri May 04 08:08:42 UTC 2018', '%a %b %d %T %Y') AS to_date

but it returns null because of the timezone. But if I try:

SELECT STR_TO_DATE('Fri May 04 08:08:42 2018', '%a %b %d %T %Y') AS to_date

... it works perfectly. So is there a way to add a timezone to the date format?

Do you want to convert the timezone before saving as datetime? — Salman A, Apr 30 '19 at 12:12

Salman A · Accepted Answer · 2019-04-30T12:27:04.473

If the string always contains UTC then you can hard-code it:

SELECT STR_TO_DATE('Fri May 04 08:08:42 UTC 2018', '%a %b %d %T UTC %Y') AS to_date
-- 2018-05-04 08:08:42

If you want to convert the timezone information as well you need to use CONVERT_TZ function:

SELECT CONVERT_TZ(STR_TO_DATE('Fri May 04 08:08:42 UTC 2018', '%a %b %d %T UTC %Y'), '+00:00', 'system')
-- 2018-05-04 13:08:42
-- actual result depends on system timezone

STR_TO_DATE with timezone

1 Answers1