How can I convert a Unix timestamp to a date in Foundry Contour?

Question

The Foundry Contour table and column editors make it easy to parse a string containing some combination of year, month, and day as a date, but I haven't been able to figure out how to convert a Long representing a Unix timestamp into a date.

Is there some trick that I'm missing? I've tried CAST(utc_column AS date), which results in an error, and I've explored "Parse dates", but it seems that only strings containing some combination of year, month, and day work for that.

Answer 1

You cannot cast longs to dates directly but you can cast them to timestamps. If you specifically want a date and not a timestamp, you can then cast to date.

Something to watch out for is that Spark assumes that longs that you are converting to timestamps are in seconds since epoch, not milliseconds since epoch.

As a result the final query may have to be something like:

CAST(CAST((utc_column) AS TIMESTAMP) AS DATE)

or

CAST(CAST((utc_column / 1000) AS TIMESTAMP) AS DATE)

depending on the scaling of your underlying Unix timestamp. This is an underlying feature of Spark and the Palantir team is looking into ways to improve the experience around this casting.