convert a text column which represents time with different timezones to datetime in mysql

Asked Apr 12 '22 at 07:45

Active Apr 12 '22 at 07:45

Viewed 90 times

Noob in MySQL here. I have a table that has a text type column that represents datetime, such as:

Sun, 17 Oct 2021 05:57:00 -0400
Thu, 09 Sep 2021 08:00:00 -0400
Thu, 15 Apr 2021 13:37:26 -0400
Mon, 01 Mar 2021 05:37:48 -0500

I want to convert this column from text type to datetime. Now, I know that I can use the STR_TO_DATE() to convert string to datetime, but the problem is that the datetimes in the text column are not all in the same timezone. Some are in GMT-4, some are GMT-5. This makes the situation a bit tricky for me and I can't think of a way to solve this. As I said, I am a noob.

Can anyone please help me with this?

asked Apr 12 '22 at 07:45

Wahiduzzaman Khan

Wrap STR_TO_DATE with CONVERT_TZ – Akina Apr 12 '22 at 07:58
https://stackoverflow.com/questions/55920357/str-to-date-with-timezone – Ergest Basha Apr 12 '22 at 07:59
@Akina can you provide an example? – Wahiduzzaman Khan Apr 12 '22 at 08:13
@ErgestBasha The solution you linked to, works great when all the datetimes has same timezone/offset. In that case we can use STR_TO_DATE to parse the text as datetime then use CONVERT_TZ to convert datetimes to a specific timezone. But the problem is, in my text column I have two different timezones, GMT-4 and GMT-5. Can I implement the same solution in this case? If yes, how? – Wahiduzzaman Khan Apr 12 '22 at 08:17
2

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=698083eca7cbbfbdb14baea26d1dfc9b – Akina Apr 12 '22 at 08:24
@Akina you should post it as an answer, others might benefit from that – Ergest Basha Apr 12 '22 at 09:41

convert a text column which represents time with different timezones to datetime in mysql

0 Answers0