How can I turn a timestamp (int) column into a datetime column?

[TrifactaUsers]

I am ending up with timestamp (int) columns after doing min/max functions on dates during a pivot. I am unsure how to turn them back into datetimes within Cloud Dataprep.

[Trifacta_Alumni]

Hi, Stephen--

What is the source of your data? BigQuery table?

Have you tried to reset the data type on the column? To the left of the column name, click the icon. From the drop-down, select the Datetime icon. Then, you will need to select or specify the format of your Datetime values.

Does that help?

If not, can you please add in the body of your response your recipe?

Cheers,

-SteveO

[TrifactaUsers]

Hi Steve

The recipe works with the unioned result of a couple of bigquery tables yeah.

When I click the icon & datetime, none of the formats it offers correspond to timestamp unfortunately - lots of yyMMdd type stuff but no SSSSSSSSSSSSS.

Wrangle attached

Thanks

[Trifacta_Alumni]

Thanks, Stephen.

Not super-strong in script debugging, but I'll do my best. It appears that you are trying to turn the event_date column into an Integer type column.

First try this:

After you run your pivot, try to do an *Change column type* transformation on the event_date column. Parameters:

*Columns:* event_date

*New type:* Custom or Datetime

*Specify type:* Here you are specifying the format string. Try various combinations to see what works.

There is an example of this down at the bottom of this page: https://cloud.google.com/dataprep/docs/html/Settype-Transform_57344649

Here is some doc on formatting Datetime content: https://cloud.google.com/dataprep/docs/html/Datetime-Data-Type_57344795

You can format a pre-existing Datetime column using the DATEFORMAT function:

https://cloud.google.com/dataprep/docs/html/DATEFORMAT-Function_57344708

Or: https://cloud.google.com/dataprep/docs/html/UNIXTIMEFORMAT-Function_57344709

If the above doesn't work, you can try to create a duplicate of your event_date column before you start reformatting it. Suggest a step before this one:

replacepatterns col: Event_date with: '' on: 'Z'

After the pivot, you should still have your original data preserved.

Any of that help?

Cheers,

-SteveO

[TrifactaUsers]

Hi Steve

Before I do the pivot, the Event_date field is datetime, as are columns derived from it

When I do the pivot, & min()/max() type function on columns derived from Event_date, they become timestamps, int type.

The change column type/settype function does not seem to allow for this epoch unix timestamp type format (13 digits) when changing to Datetime.

It only seems to allow the pre-prescribed formats displayed when you click the icon next to column name & change it to datetime that way.

Anything else seems to yield the error 'Parameter type has invalid column type'.

Thanks

[TrifactaUsers]

Hi Stephen,

Please find below screen shots might help!

First convert it in to integer. ( I'm assuming it is in mmddyyyy (12242018) format).

Then convert it to datetime and select options as below.

[TrifactaUsers]

Hi

It's epoch/unix timestamp

So the date time right now in this format would be 1546885175

Thanks

[Trifacta_Alumni · Accepted solution]

Did you try working with the UNIXTIMEFORMAT function?

https://cloud.google.com/dataprep/docs/html/UNIXTIMEFORMAT-Function_57344709

Cheers,

-SteveO

[TrifactaUsers]

That would be the one! thanks Steve

[TrifactaUsers]

try this!