pandas exporting to TZ time format in .json file

user_5416828 0
Viewed 126

I'm currently doing this to generate a dataframe:

 dataframe = pd.read_sql("select date_trunc('minute', log_time) as date, .....

my output is a time that looks like this:

"date":"2020-06-01 00:08:00.000"

What I want to do is have a time output that looks like this in the json file that it is outputted to:

"date":"2020-06-08T23:01:00.000Z

I found documents that show you how to remove it but not sure how to add it. do I have to do this after the dataframe is made or is there something in my date_trun( command that should put it in this format

pandas dataframe time
1 Answers

Based off our conversation in the comments section, I have edited your question and added in the JSON file that it is outputted to to the line What I want to do is have a time output that looks like this in the JSON file that it is outputted to: At the end of the data, the only thing that matters is the raw value to be accurate in your JSON file. Don't worry about what it looks like in your Jupyter Notebook. I think this is a common mistake that people make and one that I have made in the past as well.

I would suggest not worrying about the datetime format in pandas. Just go with pandas default date/time until the very end.

THEN, as a final step, just before exporting to a JSON, change the format of the field to:

df['TIME'] = pd.to_datetime(df['TIME']).dt.strftime('%Y-%m-%dT%H:%M:%S.%f').str[:-3] + ['Z']

That will change to a format of 2020-06-08T23:01:00.000Z .

Note .str[:-3] is required because strftime doesn't support milliseconds (3 decimals) according to the documentation and only micorseconds (6 decimals). As such, you need to truncate the last 3 decimals to change to millisecond format.

That specific format is not directly supported with T and Z, so I did a little bit of string manipulation.

user_63667700
Related