Convert Epoch Format Timestamp to Datetime

Version 3

    Description:

    Timestamps are often stored as a floating point number representing the number of days since an arbitrary date referred to as the epoch. The fractional portion of this number represents the time of day (0.25 means 06:00 am, 0.5 means 12:00 pm, etc). Excel and many databases store datetime fields using this type of representation, however the epoch date used varies for different systems. For Excel the epoch date is 30th December 1899.

     

    It is sometimes necessary to convert a floating number representing an epoch timestamp into a datetime datatype. Typically this is necessary when a CSV or other text file has numeric fields which represent timestamps.

     

    At first sight the simple way to achieve this is simply to connect to the text data source, which will automatically recognise the field as a number and then change the data type in Tableau to datetime. This works correctly unless a data extract has been created before changing the data type, in which case the result will be wrong by 2 days. The 2 day error is because the epoch date used by the data engine is 1st January 1900: 2 days later than for Excel. This issue is described in detail in this thread. The problem became more complex with the introduction of the fast data loader and the ability to append to data extracts, leading to lengthy discussions during the Tableau 6.1 beta.

     

    Dan Cory eventually came to the rescue with a very simple solution, which defines the required epoch date to be used for the conversion, rather than relying on the database engine's own epoch date.

     

    Example Calculation:

    For example, if the input field is expressed with respect to Excel's epoch date, the required calculation is as follows:

     

    DATETIME([timestamp] + INT(#December 30, 1899#))

     

    [timestamp] - a floating point number representing the number of whole and partial days since Excel's epoch

     

    Comments:

    If the calculation above is evaluated using the JET engine (which uses the Excel epoch), the term INT(#December 30, 1899#) will evaluate to 0 and the conversion will work as expected. However if a data extract has already been created (for example if an extract was created on first accessing the data source), this term will evaluate to -2 (since 30th December 1899 is 2 days before the data engine's epoch). So 2 days will be subtracted from the [timestamp] value before conversion, which this time will use the data engine's epoch, thus the correct date will be returned once more.