CODEWITHSUNDEEP
mysqldatedatetimedate-formatting
Christian
Christian

Reputation: 3393

MySQL: LOAD DATA with time/dates with timezone information

I have text file that comes with timestamps of the form Fri, 05 Jan 2013 05:50:00 +0800. I don't know how I can read the file into my MySQL database. Fri, 05 Jan 2013 05:50:00 would work just fine with '%a, %d %M %Y %H:%i:%s', but the +xx00 throws everything off. That doesn't seem to be supported.

Is there a way to import the text file as is, or do I need to preprocess it first to handle the timezone information.

Upvotes: 0

Views: 569

Answers (1)

walter
walter

Reputation: 1239

It's a little complicated, but the answer is yes.

  1. First of all, you must replace your timezone information from +0800 to +08:00, because MySQL only support this kind of offset expression.

  2. Use STR_TO_DATE to get a standard datetime: STR_TO_DATE(SUBSTRING_INDEX(your_time_str, ' ', 5), '%a, %d %M %Y %H:%i:%s');

  3. [Optional, if you need] Convert the standard time string from timezone described in your_time_str to server timezone: CONVERT_TZ(standard_time_str, SUBSTRING_INDEX(your_time_str, ' ', -1), SELECT @@global.time_zone)

So the final statement is something like this:

CONVERT_TZ(STR_TO_DATE(SUBSTRING_INDEX(your_time_str, ' ', 5), '%a, %d %M %Y %H:%i:%s'), SUBSTRING_INDEX(your_time_str, ' ', -1), SELECT @@global.time_zone)

Upvotes: 1

Related Questions