Avoiding time zone confusion in SFMC

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
_Open

Full 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.

namecurrent_utc_offsetis_currently_dst
Dateline Standard Time-12:000
UTC-11-11:000
Aleutian Standard Time-09:001
Hawaiian Standard Time-10:000
Marquesas Standard Time-09:300
Alaskan Standard Time-08:001
UTC-09-09:000
Pacific Standard Time (Mexico)-07:001
UTC-08-08:000
Pacific Standard Time-07:001
US Mountain Standard Time-07:000
Mountain Standard Time (Mexico)-06:001
Mountain Standard Time-06:001
Central America Standard Time-06:000
Central Standard Time-05:001
Easter Island Standard Time-05:001
Central Standard Time (Mexico)-05:001
Canada Central Standard Time-06:000
SA Pacific Standard Time-05:000
Eastern Standard Time (Mexico)-05:000
Eastern Standard Time-04:001
Haiti Standard Time-04:001
Cuba Standard Time-04:001
US Eastern Standard Time-04:001
Paraguay Standard Time-04:000
Atlantic Standard Time-03:001
Venezuela Standard Time-04:000
Central Brazilian Standard Time-04:000
SA Western Standard Time-04:000
Pacific SA Standard Time-03:001
Turks And Caicos Standard Time-04:000
Newfoundland Standard Time-02:301
Tocantins Standard Time-03:000
E. South America Standard Time-03:000
SA Eastern Standard Time-03:000
Argentina Standard Time-03:000
Greenland Standard Time-02:001
Montevideo Standard Time-03:000
Magallanes Standard Time-03:000
Saint Pierre Standard Time-02:001
Bahia Standard Time-03:000
UTC-02-02:000
Mid-Atlantic Standard Time-01:001
Azores Standard Time+00:001
Cape Verde Standard Time-01:000
UTC+00:000
Morocco Standard Time+01:001
GMT Standard Time+01:001
Greenwich Standard Time+00:000
W. Europe Standard Time+02:001
Central Europe Standard Time+02:001
Romance Standard Time+02:001
Central European Standard Time+02:001
W. Central Africa Standard Time+01:000
Namibia Standard Time+01:000
Jordan Standard Time+03:001
GTB Standard Time+03:001
Middle East Standard Time+03:001
Egypt Standard Time+02:000
E. Europe Standard Time+03:001
Syria Standard Time+03:001
West Bank Standard Time+03:001
South Africa Standard Time+02:000
FLE Standard Time+03:001
Israel Standard Time+03:001
Kaliningrad Standard Time+02:000
Libya Standard Time+02:000
Arabic Standard Time+03:000
Turkey Standard Time+03:000
Arab Standard Time+03:000
Belarus Standard Time+03:000
Russian Standard Time+03:000
E. Africa Standard Time+03:000
Iran Standard Time+04:301
Arabian Standard Time+04:000
Astrakhan Standard Time+04:000
Azerbaijan Standard Time+04:000
Russia Time Zone 3+04:000
Mauritius Standard Time+04:000
Saratov Standard Time+04:000
Georgian Standard Time+04:000
Caucasus Standard Time+04:000
Afghanistan Standard Time+04:300
West Asia Standard Time+05:000
Ekaterinburg Standard Time+05:000
Pakistan Standard Time+05:000
India Standard Time+05:300
Sri Lanka Standard Time+05:300
Nepal Standard Time+05:450
Central Asia Standard Time+06:000
Bangladesh Standard Time+06:000
Omsk Standard Time+06:000
Myanmar Standard Time+06:300
SE Asia Standard Time+07:000
Altai Standard Time+07:000
W. Mongolia Standard Time+07:000
North Asia Standard Time+07:000
N. Central Asia Standard Time+07:000
Tomsk Standard Time+07:000
China Standard Time+08:000
North Asia East Standard Time+08:000
Singapore Standard Time+08:000
W. Australia Standard Time+08:000
Taipei Standard Time+08:000
Ulaanbaatar Standard Time+08:000
North Korea Standard Time+08:300
Aus Central W. Standard Time+08:450
Transbaikal Standard Time+09:000
Tokyo Standard Time+09:000
Korea Standard Time+09:000
Yakutsk Standard Time+09:000
Cen. Australia Standard Time+09:300
AUS Central Standard Time+09:300
E. Australia Standard Time+10:000
AUS Eastern Standard Time+10:000
West Pacific Standard Time+10:000
Tasmania Standard Time+10:000
Vladivostok Standard Time+10:000
Lord Howe Standard Time+10:300
Bougainville Standard Time+11:000
Russia Time Zone 10+11:000
Magadan Standard Time+11:000
Norfolk Standard Time+11:000
Sakhalin Standard Time+11:000
Central Pacific Standard Time+11:000
Russia Time Zone 11+12:000
New Zealand Standard Time+12:000
UTC+12+12:000
Fiji Standard Time+12:000
Kamchatka Standard Time+13:001
Chatham Islands Standard Time+12:450
UTC+13+13:000
Tonga Standard Time+13:000
Samoa Standard Time+13:000
Line Islands Standard Time+14:000

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top