Python tool: datetime written to workflow as date

[ToPlo]

Hi all, 

I'm using a Python tool in my workflow to do some cleaning and calculations on time series data.

I use pandas dataframes to work with the data and then write the dataframe back to the workflow/

However, the timestamp field (yyyy-mm-dd hh:mm:ss, datetime64[ns]) is truncated to only the date when writing back to the workflow. The datetime field is fine just before, so it looks like the Alteryx.write function truncates the datetime to date. 

Any ideas how to solve this?

Thanks for your consideration.

[Sntrada]

I can try to help, but I will take trial and error, so I will need your workflow. You can provide a skeleton version with at least one row of data and the part of the code relating to the date. 

[ToPlo · Accepted solution]

Thanks for your offer to help Sntrada!

I have been dabbling with the workflow... Apparently, the datetime gets truncated when the time of the first record in the table is 00:00:00. I guess this is the default time given to a datestamp, so the column is interpreted as a date.

I tested a few different starting datetimes:

2020-09-13 00:00:00     only date column is retuned 

2020-09-13 00:00:01     no problem

2020-09-13 00:10:00     no problem

2020-09-13 12:00:00     no problem

In the first case, the data type indicated by the 'Browse' tool is 'date', while in rest of the cases it is 'datetime'

So it seems like a bug with the interpretation of the datetime column by the Alteryx.write function when the first record is at 00:00:00. 

I will circumvent this by making sure that the query does not start at this time. Perhaps this can be raised as a bug with Alteryx.

[Sntrada]

Kudos to you!

I think you can mark your answer as the solution, in case someone else has a similar issue in the future. 

[Ross_K]

Hey,

Encountered the same problem and took longer than it should have to track it down 😋

With the help of the docs here, I added a little extra code to my Python script right before Alteryx.write to explicitly set the datatypes.

This was complicated a little as Alteryx doesn't understand the Pandas datatypes, so had to make a little lookup table too.

All in all it seemed to resolve the issue.

I hope it helps and let me know if you make any improvements!

# Start the dictionary
output_metadata = {}
# Now loop through the columns looking up matching types
# Each item in the match list has the Pandas dtype as the key and Alteryx dtype as the value
dtype_match_list = {'object':'v_wstring',
                    'int64':'int64',
                    'float64':'float',
                    'bool':'bool',
                    'datetime64':'datetime'}
for column in df.columns:
    # Get the datatype of the column
    pandas_dtype = str(df[column].dtype)
    # Some fields have additional info, e.g. datetime64[ns, UTC], so strip that off
    pandas_dtype = pandas_dtype.split("[")[0]
    output_metadata[column] = {'type':dtype_match_list[pandas_dtype]}
# Write the df to the output
Alteryx.write(pandas_df=df, columns=output_metadata, outgoing_connection_number=1)

[iObject]

my first record was not 00:00:00 but I still am seeing this issue. so frustrating, I have 5 outputs and 4 of them are fine but randomly this one is not.

The column in question is just a datestamp at runtime so i know for a fact it isn't 00:00:00. any way to directly set the column to datetime? what a ridiculous bug