'How would I convert from UTC to EST based on the time value that's currently present in the column?

I'd like to convert the time on both start and end columns from UTC to EST using SQL. I found the below query somewhere on the web after hours of searching for a way to do this but to no avail (the query below's incorrect).

Is there a clean way to convert from UTC to EST based on the date of the field?

start column has the current value of 2019-06-01 05:00:00

end column has the current value of 2019-06-30 04:00:00

I'd like these to be in EST timezone.

Thanks in advance.

UPDATE my_table
SET start = from_tz(to_timestamp('2009-11-17 18:40:05','yyyy-mm-dd hh24:mi:ss'), 'UTC'), end = from_tz(to_timestamp('2009-11-17 18:40:05','yyyy-mm-dd hh24:mi:ss'), 'UTC') 
AT TIME zone 'America/New_York'
WHERE id = 3;
mysqlsqldebugging


Solution 1:[1]

You can use CONVER_TZ

you can use '+00:00' so the server hasn't to look it up, which will save time, like ysth explained in the comment

SELECT
  CONVERT_TZ('2007-03-11 2:00:00','UTC','America/New_York') AS time1,
  CONVERT_TZ('2007-03-11 3:00:00','UTC','America/New_York') AS time2;

time1               | time2              
:------------------ | :------------------
2007-03-10 21:00:00 | 2007-03-10 22:00:00

UPDATE my_table
SET start = CONVERT_TZ('2009-11-17 18:40:05','UTC','America/New_York')
    , end = CONVERT_TZ('2009-11-17 18:40:05','UTC','America/New_York') 

WHERE id = 3;

Table 'db_477873842.my_table' doesn't exist

SELECT
  CONVERT_TZ('2007-03-11 2:00:00','+00:00','America/New_York') AS time1,
  CONVERT_TZ('2007-03-11 3:00:00','+00:00','America/New_York') AS time2;

time1               | time2              
:------------------ | :------------------
2007-03-10 21:00:00 | 2007-03-10 22:00:00

UPDATE my_table
SET start = CONVERT_TZ('2009-11-17 18:40:05','+00:00','America/New_York')
    , end = CONVERT_TZ('2009-11-17 18:40:05','+00:00','America/New_York') 

WHERE id = 3;

Table 'db_477873842.my_table' doesn't exist

db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions