Automatische Zeitumstellung in SQL

Zwei mal im Jahr ist es so weit. Die ZEITUMSTELLUNG 👻

Dieses leidige Thema beschäftigt uns technische Marketing Cloud User alle halbe Jahr ums Neue. Einige verbringen womöglich sogar den Sonntag nach der Zeitumstellung mit Überstunden vor dem PC, um manuell die Zeitumrechnung in den SQLs anzupassen, damit alle Automationen am Montag wie gewohnt laufen.

Doch das muss nicht sein! Denn es gibt eine SQL-Funktion, die automatisch für Dich die Zeitumstellung in SQL Queries übernimmt.

At Time Zone

Hintergrund

Die Marketing Cloud Server Time ist stets eingestellt auf Central America Standard Time. Diese Zeitzone kann nicht in den Einstellung der Marketing Cloud angepasst werden und auch wenn der Marketing Cloud Tenant sich auf einem Europäischen Server befindet, bleibt Central America Standard Time die Systemzeit.

Für einige Kampagnen ist es jedoch notwendig Felder in die lokale Zeitzone zu übersetzen, um diese entweder als Filter in einer SQL anzuwenden oder um personalisierte Daten und Zeiten in die Marketing Kommunikation einzubetten.

Für diese Fälle gibt es die Funktion AT TIME ZONE, welche spezifische Datumsfelder in die lokale Zeitzone übersetzen kann.

Außerdem berücksichtigt AT TIME ZONE die lokale Zeitumstellung!

AT TIME ZONE in SQL verwenden

Zunächst verwenden wir AT TIME ZONE, um die System-Zeitzone für ein bestimmtes Feld zu bestimmen. Die Marketing Cloud selber weiß nämlich nicht, in welcher Zeitzone sich ihre Systemzeit befindet.

Der erste Teil der Funktion sieht so aus:

SELECT Date_Field AT TIME ZONE 'Central America Standard Time' as Data_Extension_Field

Dieses SQL Statement definiert für das Feld “Date_Field” die Zeitzone als “Central American Standard Time”.

Basierend auf dieser Zeitzone kann nun die lokale Zeitzone errechnet werden. Hierfür verwenden wir erneut die AT TIME ZONE Funktion:

SELECT Date_Field AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'Central European Standard Time' as Data_Extension_Field

Diese SQL-Statement definiert zunächst die System-Zeitzone für das Feld “Date_Field” als Central American Standard Time” und wandelt den Feld-Wert dann in die Zeitzone “Central European Standard Time” um.

Das Ergebnis der Funktion AT TIME ZONE wird als sogenanntes DATETIMEOFFSET angezeigt und muss somit noch in das gewünschte Datumsformat formatiert werden.

Datum formatieren

Ein DATETIMEOFFSET ist eine Datumsangabe, bei der die Zeitdifferenz dem ursprünglichen Feld-Wert beigefügt wird. Eine Zeitdifferenz von 1 Stunde würde zum Beispiel folgendermaßen dargestellt werden:

1912-10-25 12:24:32 +01:0

Anstatt die Zeitdifferenz beizufügen, möchten wir aber, dass die Differenz mit dem Feld-Wert verrechnet wird, um folgenden Wert darzustellen:

1912-10-25 13:24:32

Um das obige Ergebnis zu erhalten, verwenden wir die FORMAT Funktion folgendermaßen:

SELECT FORMAT(Date_Field AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'Central European Standard Time‘, ‚yyyy-MM-dd HH:mm:ss’) as Data_Extension_Field

Im ersten Teil der FORMAT Funktion bestimmen wir den Wert, der formatiert werden soll, inklusive der beiden AT TIME ZONE Funktionen. Im zweiten Teil, nach dem Komma, definieren wir dann das Format unseres Datumswerts.

Falls wir nur die Uhrzeit eines bestimmten Datumsfelds erhalten wollen, könne wir das Feld folgendermaßen formatieren:

SELECT FORMAT(Date_Field AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'Central European Standard Time‘, ‚HH:mm’) as Data_Extension_Field

Das Ergebnis unseres SQL-Statements wäre dann:

13:24

Eine Liste aller weiteren möglichen Datumsformate findest du hier.

Liste der Zeitzonen

Die Funktion AT TIME ZONE kann für alle Zeitzonen verwendet werden. Jedoch kennt die Funktion nur bestimmte englische Bezeichnungen für die jeweiligen Zeitzonen. Im Folgenden sind alle verwendbaren Zeitzonen-Bezeichnungen aufgelistet, in der Reihenfolge ihrer Abweichung von der koordinierte Weltzeit (von UTC-12 bis UTC+14):

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

7 Kommentare zu “Automatische Zeitumstellung in SQL

  • 8. November 2021 at 10:00
    Permalink

    Hallo,
    danke, dieser Tipp ist wirklich sehr hilfreich.
    Sofern sich die Datumsumwandlung auf Felder FROM ENT.Contact_Salesforce beziehen, klappt es auch.

    Wie sieht es aus, wenn ein Feld einer Data Extension den Wert GETDATE() haben soll?
    Gibt es da auch einen Trick um ‘Central America Standard Time’ in ‘Central European Standard Time‘ zu formatieren?

    Reply
    • 9. November 2021 at 09:02
      Permalink

      Hallo SP,
      GETDATE() liefert die den aktuellen Zeitpunkt des Betriebssystems des SQL-Servers unterhalb der Marketing Cloud zurück. Da wir kein Wissen darüber haben, in welcher Zeitzone Salesforce (bzw. Microsoft) den Server laufen lassen, weißt Du auch nicht, in welcher Zeitzone GETDATE() Dir die Uhrzeit liefert.
      Es gibt aber die Funktion GETUTCDATE(), welche Dir immer den Zeitpunkt in der Zeitzone UTC liefert.
      Auf diese Funktion kannst Du dann ganz normal die Zeitzonenumrechnung anwenden, wie im Blog Beitrag beschrieben.

      Reply
      • 9. November 2021 at 12:10
        Permalink

        Hallo,
        editiere ich das Feld in GETUTCDATE(), wird es wieder umgewandelt in GETDATE().
        Trotzdem Danke

        Reply
    • 10. November 2021 at 08:42
      Permalink

      Hallo SP,
      Laut Salesforce Dokumentation hat die Marketing Cloud IMMER das System Date welches Central America Standard Time entspricht. Egal, auf welchem Server sich der Tenant befindet.
      Beim GETDATE() kannst du daher die AT TIME ZONE Funktion genauso anwenden, wie bei einem Feld.
      Hier ein Beispiel:
      format(GETDATE() AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'Central European Standard Time', 'dd/MM/yy')

      Reply
        • 11. November 2021 at 15:55
          Permalink

          Hallo SP,
          jetzt verstehe ich … Du möchtest einen Default Wert setzen beim Importieren von Datensätzen.
          Wenn Du GETDATE() nimmst, wird die Zeit immer (wie Annika geschrieben hat) in UTC-6 gespeichert. Damit ist der Zeitstempel exakt in der gleichen Zeitzone wie bei synchronized Data Extensions.
          Das Umrechnen in UTC bzw. MEZ oder MESZ machst Du dann in den SQL-Statements, welche die importierten Daten selektieren. Bzw. mit AmpScript in einer Email.
          Also nicht schon beim Erstellen der Daten sondern beim Verwenden der Daten.

          Reply
          • 11. November 2021 at 17:10
            Permalink

            Vielen Dank.
            Alles klar.
            Case Closed 👍

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Newsletter Anmeldung

Melde Dich zu unserem Newsletter an, um keine News zur Sales-, Service- und Marketingcloud mehr zu verpassen. Außerdem senden wir Dir die neusten Schulungstermine.

X
X