2

My sql server is located in GMT and I need to get the EST equivalent tz.

Somehow the EST is wrong.

select now(),convert_tz(now(),'GMT','EST'),convert_tz(now(),'GMT','EST') - interval 10 minute

The EST time should be 20:30 and not 19:30

Here is my results -

enter image description here

O. Jones
  • 103,626
  • 17
  • 118
  • 172
user1050619
  • 19,822
  • 85
  • 237
  • 413
  • 3
    Most of the Eastern timezone is currently observing Eastern *Daylight* Time, i.e. `EDT`. This is why it's better to use the timezone strings like `America/New_York`. – ceejayoz Apr 01 '17 at 00:34

1 Answers1

1

As @ceejayoz mentioned, the timezones of locations change depending on daylight savings observation. In addition, it's more proper to call it UTC rather than GMT, as when you call it GMT, it connotes that your servers are tune to some local time, say, London time, and that your server's time will switch to some other time, say BST, when daylight savings is observed.

Assuming, as I presume you are trying to communicate, that your servers are set to UTC time, never observing daylight savings, and that you want to translate it to the time observed by most Eastern US cities, a solution would be

SELECT NOW(),
  CONVERT_TZ(NOW(), 'UTC', 'America/New_York'),
  CONVERT_TZ(NOW(), 'UTC', 'America/New_York') - INTERVAL 10 MINUTE;
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
jhanschoo
  • 1,236
  • 13
  • 15
  • 1
    Fixed your slashes, but also - if you use `UTC_TIMESTAMP()` instead of `NOW()`, then it won't matter what time zone your server is set to. – Matt Johnson-Pint Apr 01 '17 at 17:44
  • 2
    GMT does not observe daylight savings time. GMT and UTC are the same time, within a second. The timezone in London that observes daylight savings time is named "Europe/London." – Matthew Simon Cavalletto Nov 17 '17 at 20:27