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?
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