Converteren van UNIX timestamp in Tableau en Excel

Deze week kreeg ik een bestand aangeleverd met daarin een UNIX timestamp. In deze blog laat ik zien hoe je eenvoudig met Tableau of Excel een timestamp kunt omzetten in een bruikbaar datum – tijd veld.

UNIX Timestamp

Een UNIX timestamp is een getal dat het aantal seconden of milliseconden sinds middernacht 1 januari 1970 (GMT) uitdrukt. Indien de timestamp uit 10 cijfers bestaat betreft het seconden, bij 14 cijfers gaat het om milliseconden.

De UNIX timestamp 1376510200 is bijvoorbeeld woensdag 14 augustus 2013, 19:56:40 GMT. Ons brein kan uiteraard niets met deze timestamp, om er iets zinnigs mee te doen moet de timestamp vertaald worden naar een datum/tijd format.

Hier zijn twee manieren om de timestamp te converteren:

Converteren in Tableau

Converteren in Tableau gaat zeer eenvoudig, als je de juiste functie kent. Je maakt een calculated field op basis van de timestamp die er als volgt uitziet:

Dateadd voegt een waarde toe aan de datum achter de 2e komma, in dit geval onze startdatum 1-1-1970. Het is een 10 cijferige timestamp dus we voegen seconden toe. De INT rond de timestamp is nodig, omdat Tableau standaard een float gebruikt en dat is niet toegestaan binnen date functies.

1/24 is de correctie voor de GMT zone. In ons geval één uur extra. Als je het calculated field opslaat heb je een perfecte date-time dimensie.

Hoe dit laatste precies gaat met zomertijd (dan hebben we GMT +2) weet ik nog niet. Laat het me weten als jij het wel weet.

De datum notatie met de ‘hekjes’ (#) heet een date literal. Deze is van belang om datum velden op een juiste wijze te presenteren (zodat ze internationaal uitwisselbaar zijn).

Converteren in Excel

Het omzetten van UNIX timestamps naar een datum is ook eenvoudig in Excel, al kost het iets meer werk dan in Tableau. De volgende formule vertaalt de timestamp in kolom d naar een datum:

Het eerste deel van de formule is 1 januari 1970.
Deel twee, de waarde van de timestamp gedeeld door 8640 (60*60*24), is het aantal dagen dat sinds 1/1970 is verstreken. Dit tel je op bij deel 1.
Als laatste weer de GMT correctie

De laatste stap is het formatteren van het datumveld:

Het resultaat is een datum tijd veld dat je prima kunt gebruiken in Tableau en andere tools die gebruik maken van datum tijd velden.

Het voorbeeld betreft een timestamp met seconden (10 cijfers). In het geval van een timestamp met milliseconden verandert er weinig, je moet de timestamp alleen nog extra delen door 1000 om op hetzelfde uit te komen.