Working with time zones in Marketing Cloud, especially in SQL, is not always easy. We have multiple time zones in there, depending on where you look. Here is a good unofficial (compiled in best effort) overview of where which time zone applies:
Whenever you need to adjust the time zone in your queries, you need to observe that SFMC system time is actually hard coded to UTC-6, NOT CST. You might have been doing an approximate timezone conversion, which was usually done by using the DATEADD() function to add the needed number of hours. However, this has always been a bad workaround, and not so much a proper solution – especially when wanting to adjust of daylight savings time.
Luckily, you can utilise AT TIME ZONE function in SQL. To convert Event Date in e.g. _Open data view to Central European Time, you can use following query:
SELECT
EventDate
AT TIME ZONE 'Central America Standard Time'
AT TIME ZONE 'Central Europe Standard Time' as OpenTime
FROM
_OpenFull list of valid time zones can be found in the bottom of this post.
In the above example, the first AT TIME ZONE declares the current timezone settings for the date field (CAST), and the second AT TIME ZONE instructs to convert the timezone to the new timezone (CEST). Depending on which time zones you are converting, daylight savings time is observed. Time zones with DST support are marked in the below list.
This is a very easy and versatile method for ensuring your time stamps are consistent across your various data sources.
| name | current_utc_offset | is_currently_dst |
| Dateline Standard Time | -12:00 | 0 |
| UTC-11 | -11:00 | 0 |
| Aleutian Standard Time | -09:00 | 1 |
| Hawaiian Standard Time | -10:00 | 0 |
| Marquesas Standard Time | -09:30 | 0 |
| Alaskan Standard Time | -08:00 | 1 |
| UTC-09 | -09:00 | 0 |
| Pacific Standard Time (Mexico) | -07:00 | 1 |
| UTC-08 | -08:00 | 0 |
| Pacific Standard Time | -07:00 | 1 |
| US Mountain Standard Time | -07:00 | 0 |
| Mountain Standard Time (Mexico) | -06:00 | 1 |
| Mountain Standard Time | -06:00 | 1 |
| Central America Standard Time | -06:00 | 0 |
| Central Standard Time | -05:00 | 1 |
| Easter Island Standard Time | -05:00 | 1 |
| Central Standard Time (Mexico) | -05:00 | 1 |
| Canada Central Standard Time | -06:00 | 0 |
| SA Pacific Standard Time | -05:00 | 0 |
| Eastern Standard Time (Mexico) | -05:00 | 0 |
| Eastern Standard Time | -04:00 | 1 |
| Haiti Standard Time | -04:00 | 1 |
| Cuba Standard Time | -04:00 | 1 |
| US Eastern Standard Time | -04:00 | 1 |
| Paraguay Standard Time | -04:00 | 0 |
| Atlantic Standard Time | -03:00 | 1 |
| Venezuela Standard Time | -04:00 | 0 |
| Central Brazilian Standard Time | -04:00 | 0 |
| SA Western Standard Time | -04:00 | 0 |
| Pacific SA Standard Time | -03:00 | 1 |
| Turks And Caicos Standard Time | -04:00 | 0 |
| Newfoundland Standard Time | -02:30 | 1 |
| Tocantins Standard Time | -03:00 | 0 |
| E. South America Standard Time | -03:00 | 0 |
| SA Eastern Standard Time | -03:00 | 0 |
| Argentina Standard Time | -03:00 | 0 |
| Greenland Standard Time | -02:00 | 1 |
| Montevideo Standard Time | -03:00 | 0 |
| Magallanes Standard Time | -03:00 | 0 |
| Saint Pierre Standard Time | -02:00 | 1 |
| Bahia Standard Time | -03:00 | 0 |
| UTC-02 | -02:00 | 0 |
| Mid-Atlantic Standard Time | -01:00 | 1 |
| Azores Standard Time | +00:00 | 1 |
| Cape Verde Standard Time | -01:00 | 0 |
| UTC | +00:00 | 0 |
| Morocco Standard Time | +01:00 | 1 |
| GMT Standard Time | +01:00 | 1 |
| Greenwich Standard Time | +00:00 | 0 |
| W. Europe Standard Time | +02:00 | 1 |
| Central Europe Standard Time | +02:00 | 1 |
| Romance Standard Time | +02:00 | 1 |
| Central European Standard Time | +02:00 | 1 |
| W. Central Africa Standard Time | +01:00 | 0 |
| Namibia Standard Time | +01:00 | 0 |
| Jordan Standard Time | +03:00 | 1 |
| GTB Standard Time | +03:00 | 1 |
| Middle East Standard Time | +03:00 | 1 |
| Egypt Standard Time | +02:00 | 0 |
| E. Europe Standard Time | +03:00 | 1 |
| Syria Standard Time | +03:00 | 1 |
| West Bank Standard Time | +03:00 | 1 |
| South Africa Standard Time | +02:00 | 0 |
| FLE Standard Time | +03:00 | 1 |
| Israel Standard Time | +03:00 | 1 |
| Kaliningrad Standard Time | +02:00 | 0 |
| Libya Standard Time | +02:00 | 0 |
| Arabic Standard Time | +03:00 | 0 |
| Turkey Standard Time | +03:00 | 0 |
| Arab Standard Time | +03:00 | 0 |
| Belarus Standard Time | +03:00 | 0 |
| Russian Standard Time | +03:00 | 0 |
| E. Africa Standard Time | +03:00 | 0 |
| Iran Standard Time | +04:30 | 1 |
| Arabian Standard Time | +04:00 | 0 |
| Astrakhan Standard Time | +04:00 | 0 |
| Azerbaijan Standard Time | +04:00 | 0 |
| Russia Time Zone 3 | +04:00 | 0 |
| Mauritius Standard Time | +04:00 | 0 |
| Saratov Standard Time | +04:00 | 0 |
| Georgian Standard Time | +04:00 | 0 |
| Caucasus Standard Time | +04:00 | 0 |
| Afghanistan Standard Time | +04:30 | 0 |
| West Asia Standard Time | +05:00 | 0 |
| Ekaterinburg Standard Time | +05:00 | 0 |
| Pakistan Standard Time | +05:00 | 0 |
| India Standard Time | +05:30 | 0 |
| Sri Lanka Standard Time | +05:30 | 0 |
| Nepal Standard Time | +05:45 | 0 |
| Central Asia Standard Time | +06:00 | 0 |
| Bangladesh Standard Time | +06:00 | 0 |
| Omsk Standard Time | +06:00 | 0 |
| Myanmar Standard Time | +06:30 | 0 |
| SE Asia Standard Time | +07:00 | 0 |
| Altai Standard Time | +07:00 | 0 |
| W. Mongolia Standard Time | +07:00 | 0 |
| North Asia Standard Time | +07:00 | 0 |
| N. Central Asia Standard Time | +07:00 | 0 |
| Tomsk Standard Time | +07:00 | 0 |
| China Standard Time | +08:00 | 0 |
| North Asia East Standard Time | +08:00 | 0 |
| Singapore Standard Time | +08:00 | 0 |
| W. Australia Standard Time | +08:00 | 0 |
| Taipei Standard Time | +08:00 | 0 |
| Ulaanbaatar Standard Time | +08:00 | 0 |
| North Korea Standard Time | +08:30 | 0 |
| Aus Central W. Standard Time | +08:45 | 0 |
| Transbaikal Standard Time | +09:00 | 0 |
| Tokyo Standard Time | +09:00 | 0 |
| Korea Standard Time | +09:00 | 0 |
| Yakutsk Standard Time | +09:00 | 0 |
| Cen. Australia Standard Time | +09:30 | 0 |
| AUS Central Standard Time | +09:30 | 0 |
| E. Australia Standard Time | +10:00 | 0 |
| AUS Eastern Standard Time | +10:00 | 0 |
| West Pacific Standard Time | +10:00 | 0 |
| Tasmania Standard Time | +10:00 | 0 |
| Vladivostok Standard Time | +10:00 | 0 |
| Lord Howe Standard Time | +10:30 | 0 |
| Bougainville Standard Time | +11:00 | 0 |
| Russia Time Zone 10 | +11:00 | 0 |
| Magadan Standard Time | +11:00 | 0 |
| Norfolk Standard Time | +11:00 | 0 |
| Sakhalin Standard Time | +11:00 | 0 |
| Central Pacific Standard Time | +11:00 | 0 |
| Russia Time Zone 11 | +12:00 | 0 |
| New Zealand Standard Time | +12:00 | 0 |
| UTC+12 | +12:00 | 0 |
| Fiji Standard Time | +12:00 | 0 |
| Kamchatka Standard Time | +13:00 | 1 |
| Chatham Islands Standard Time | +12:45 | 0 |
| UTC+13 | +13:00 | 0 |
| Tonga Standard Time | +13:00 | 0 |
| Samoa Standard Time | +13:00 | 0 |
| Line Islands Standard Time | +14:00 | 0 |