CODEWITHSUNDEEP
mysql
Spiek Vier
Spiek Vier

Reputation: 65

Convert date column in MYSQL to different format

I need to update column pubDate_Date to 2018-02-04 20:39:55 from column pubDate with format Sun, 04 Feb 2018 20:39:55 +0100.

I have tried the following, but without success:

UPDATE `feeds` SET `pubDate_Date` = date('Y-m-d H:i:s', strtotime(`pubDate`))

Hope someone can point me in the right direction

Upvotes: 0

Views: 45

Answers (1)

eggyal
eggyal

Reputation: 125855

In general, you can use MySQL's STR_TO_DATE() function:

UPDATE feeds SET pubDate_Date = STR_TO_DATE(pubDate, '%a, %d %b %Y %T')

However there's no format code for timezone offsets, which MySQL will therefore ignore from the end of your original string (instead interpreting each string to be in the session's time_zone). Therefore:

  • If all records are in the same timezone, you could simply do SET time_zone = '+01:00' before the above update command.

  • Otherwise, do the following instead to adjust each time to the intended timezone:

    UPDATE feeds SET pubDate_Date = CONVERT_TZ(
  STR_TO_DATE(pubDate, '%a, %d %b %Y %T'),
  @@session.time_zone,
  CONCAT(SUBSTR(pubDate, -5, 3), ':', RIGHT(pubDate, 2)
)

Upvotes: 1

Related Questions