How do convert this redshift query to MySQL about date

Question

So I want to convert this redshift query to MySQL. This is about getting the date truncating it then converting it to string

I tried doing this

SELECT DATE_FORMAT(CONVERT_TZ('material_boxes.created','UTC','America/New_York'),'%m') AS "month_only" FROM material_boxes

The redshift query is this

SELECT TO_CHAR(DATE_TRUNC('month', CONVERT_TIMEZONE('UTC', 'America/New_York', material_boxes.created )), 'YYYY-MM') AS "month_only" FROM material_boxes

The result should be the month only.

Please read [this SO question](https://stackoverflow.com/questions/2523286/mysql-convert-tz) which discusses how to setup the MySQL timezone tables. — Tim Biegeleisen, Apr 10 '19 at 14:05

score 0 · Answer 1 · answered Apr 11 '19 at 04:33

0

I did it by removing '' in material_boxes.created and changing "%m" to "%M" for the string for month.

SELECT DATE_FORMAT(CONVERT_TZ('material_boxes.created','UTC','America/New_York'),'%m') AS "month_only" FROM material_boxes

answered Apr 11 '19 at 04:33

Sandy Pabilonia

79
1
6

How do convert this redshift query to MySQL about date

1 Answers1