UNIX Timestamp in DATE bzw. DATETIME Format in Power BI umwandeln

November 1, 2020
Denis Selimovic
DAX, Power Query / Mcomment 3Created with Sketch Beta.0 comments

This post is also available in: Englisch

Wer ein Datum bzw. Datetime-Format im UNIX-Format erhält, der wundert sich zuerst, was es damit auf sich hat bzw. wie man das denn “normal” darstellen kann. Denn das UNIX-Datumsformat sieht folgendermaßen aus:

1604135115

Was genau ist UNIX Timestamp?

Um eine Lösung zu finden, wie wir das umwandeln können, müssen wir zuerst ein Mal verstehen, was die Zahl bedeutet. Der UNIX-Timestamp ist die Anzahl Sekunden seit dem 01. Januar 1970. Wieso das genau so ist, kann man im Wikipedia Artikel nachlesen. Ich möchte mich auf die Lösung des Problems fokussieren.

In dem oben genannten Beispiel sind 1604135115 Sekunden nach dem 01. Januar 1970 der 31. Oktober 2020 um 09:05 Uhr und 15 Sekunden.

Update 20.02.2023: Beim klassische UNIX-Timestamp handelt es sich wie gesagt um die Anzahl Sekunden seit dem 01.01.1970. Es gibt allerdings einige Programmiersprachen und APIs, welche den Timestamp in Millisekunden seit dem 01.01.1970 angeben. In so einem Fall müssen die unten genannten Lösungen einfach durch 1000 geteilt werden. Einige Beispiele hierzu finden sich am Ende des Beitrags. Der Timestamp für einen Wert im Jahr 2023 ist dann nicht wie oben angegeben 10-stellig, sondern 13-stellig. Dies ist aber kein klassischer UNIX-Timestamp.

Umwandlung in Power Query

Als Test-Szenario verwenden wir die folgenden Systemlogs, da UNIX-Timestamp oft in Logs oder ähnlichen technischen Daten vorkommt:

Daten mit UNIX-Timestamp als Datums- und Zeitformat
Daten mit UNIX-Timestamp als Datums- und Zeitformat

Die einfachste Art den UNIX-Timestamp umzuwandeln, ist genau das zu machen, was der UNIX-Timestamp darstellt. Wir zählen zum 01. Januar 1970 die angegebene Anzahl Sekunden hinzu. Hierzu erstellen wir uns als neue Spalte per #datetime Funktion das Datum 01. Januar 1970 und fügen dann die Anzahl Sekunden mit der #durantion Funktion hinzu:

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [TimeStamp])
Neue Spalte als DateTime hinzufügen
Neue Spalte als DateTime hinzufügen

Die neue Spalte enthält das jeweilige Datum/Uhrzeit als Datetime Format:

Neue Spalte im Datetime-Format
Neue Spalte im Datetime-Format

Die ursprüngliche TimeStamp-Spalte kann nun gelöscht werden, falls diese im Report nicht benötigt wird. Von nun an können wir mit dem wirklichen Datum weiter arbeiten.

Umwandlung von UNIX-Timestamp in DAX innerhalb von Power BI

Falls eine Umwandlung in Power Query aus irgendwelchen Gründen nicht möglich wäre, dann gibt es noch die Möglichkeit, dies in DAX nachzuholen. Meine erste Option wäre, dies als Calculated Column durchzuführen. Ich würde diese Umwandlung aber generell direkt im Power Query durchführen, da es Teil des Datenlade- und tranformationprozesses ist.

Wir brauchen allerdings eine Berechnung mehr als im Power Query. Starten wir wieder damit, das Ausgangsdatum 01. Januar 1970 mit der DATE-Funktion zu erstellen:

DATE(1970, 01, 01)

Nun müssen wir noch die Timestamp-Sekunden hinzurechnen. Dies ist allerdings nicht einfach mit der TIME-Funktion möglich. Den Timestamp müssen wir daher zuerst herunterrechnen auf Anzahl Tage und die Zeit:

UnixTimestamp = tTimestamp_DAX[TimeStamp] / ( 60 * 60 * 24 ) 

Schließlich müssen wir die auf Tage und Zeit heruntergerechnete Zeitdauer zum Startwert hinzuaddieren:

DateTime = 
VAR UnixTimestamp = tTimestamp_DAX[TimeStamp] / ( 60 * 60 * 24 ) 
RETURN
DATE( 1970, 01, 01 ) + UnixTimestamp

Die neue Spalte können wir nun im Report verwenden, die Timestamp-Spalte sollten wir zur besseren Übersicht im Frontend ausblenden:

image 7
Berechnung von Timestamp zu DateTime in DAX

Anpassungen für Timestamp im Millisekunden-Format

Wie im Update oben angegeben gibt es auch Programmiersprachen, die den Timestemp in Millisekunden anstatt Sekunden seit dem 01.01.1970 angeben. In diesem Fall ändern sich die Formeln folgendermassen:

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [TimeStamp] / 1000 )

Bzw. für den DAX-Ansatz:

DateTime = 
VAR UnixTimestamp = ( tTimestamp_DAX[TimeStamp] / 1000 ) / ( 60 * 60 * 24 ) 
RETURN
DATE( 1970, 01, 01 ) + UnixTimestamp