We’re onto day 2 of #TipWeek at The Data School and today I’m going to show you how to deal with Unix time in your data. Unix is a system for measuring time, defined as the number of seconds that have elapsed since Midnight on January 1 1970 and is commonly found when dealing with web browsing data.
You can identify Unix time when you see a field that appears to be related to a date and time but which confusingly consists of 10 random numbers.
Once you have identified that you have encountered Unix time, use a ‘Select’ tool in Alteryx to change the existing field to an Int64 type and then drag in a ‘Formula’ tool. Create a new field by typing a new field name under Output Field and select the DateTime type. You now need to write a custom expression to convert the Unix time to a readable one. To do this, we use the DateTimeAdd formula as follows: DateTimeAdd(‘1970-01-01′,[your original field name],’seconds’), which tells Alteryx to add the number of seconds contained in your existing field to January 1 1970, hence creating a readable date time field.
If you don’t have access to Alteryx, Unix time can also be converted easily in Tableau (or SQL) using this very helpful Tableau Knowledge Base article.
EDIT: I was asked in the comments section about whether it is possible to convert time INTO Unix, which of course it is! Assuming your existing date field is set as a Date/Date Time field, simply use the following formula:
In Alteryx: DateTimeDiff([Date Field],’1970-01-01′,’seconds’)
In Tableau: DATEDIFF(‘second’,DATE(“1970-01-01”),[Date Field])
I hope you find this tip useful, feedback and comments much appreciated as always and a big thanks to fellow Info Lab-er Paul Houghton for the inspiration. For more help understanding how to deal with complex time issues in Alteryx check out his awesome blog posts here and here.