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.
Formule geprobeerd en na een beetje zoeken lukte het. Echter per cel apart.
Ik zou graag willen dat ik dezelfde formule op steeds nieuwe waarde zou kunnen toepassen.
Wellicht kan iemand dat ook laten zien. Ik kan namelijk niet meerdere waarden selecteren en tot resultaat omzetten. Kennlijk omdat Excel niet weet waar het met de uitkomsten heen moet.
Dus nu met copy paste en de celwaarde veranderd. Hoe kan dit geautomatiseerd.
Groet,
Niels