How can I convert timestamp to datetime format in Dataprep?

Question

I have this dataset that returns an INT64 column with the info of date. In BigQuery I can use a TIMESTAMP_SECONDS() to have the info in datetime format but in Dataprep I do not have this option.

This is my dataset and how I convert the INT64 in date format

This is the normal procedure to convert it in Dataprep

This is the format that bigquery return me, so I tried this one and several others

And, this is how dataprep return the data, as a lot of errors

Answer 1

You get an error because Dataprep expects values in Datetime, not Unix Timestamp.

To format Unix Timestamp into Datetime, you need use the function unixtimeformat().

The function accepts a 13-digit timestamp (in milliseconds). If your dataset has a timestamp below the digit, you must multiply it first accordingly. You can use another function called multiply() to create a new column. I'm using a 10-digit timestamp as sample dataset, so I multiplied it by 1000 so it becomes 13-digit.

Functions used:

MULTIPLY(column1, 1000) #creates column2

UNIXTIMEFORMAT(column2, "yyyy-MM-dd HH:mm:ss.SSS") #creates finish_date

Sample Illustration: