It can be frustrating when you upload an Excel file into an analytics tool and your datetime field is suddenly a big, confusing number like 1,720,990,732.
This format is called epoch time and we'll do our best to demystify it for you.
So What's Epoch Time?
While pretty formatted datetimes are easy to understand for humans like you and me, computers prefer to work with raw numbers when possible. This is easier for computers to store and makes calculations faster.
Simply put, epoch time is the number of seconds between January 1, 1970 12:00:00 AM and your datetime.
So when you see an epoch time instead of a pretty formatted datetime, that's just a computer's way of storing your datetime data. Even Excel does this - it just makes sure to render it in a pretty formatted before showing it to you.
How Do I Convert Epoch Time into a Datetime?
Don't worry, it's simple to convert epoch time into a nice datetime format. Here's the trick:
Create a datetime value for January 1, 1970 12:00:00 AM
Add the epoch time number of seconds to it
In Savant, it looks like this:
DATE_ADD( TO_DATETIME('1970-01-01 00:00:00'), `EpochTime`, 'second')
Just use that Formula in the Transform tool and replace the `EpochTime` field with the name of your epoch time field. Savant will do the rest!
💡 Tip: Use the TO_INT() function to first convert your Excel Epoch Date field to an integer, if it is not one.
This approach should work in other analytics and SQL platforms too by following the two simple steps outlined above.
Still Have Questions?
We hope that solves you're Excel epoch time heartache and gets you back to building some amazing time-series analytics. Feel free to reach out with an question. We're always here to help!