to_json converts to UTC when encoding ISO formatted datetimes

[Komnomnomnom]

Code Sample

In [99]: ts = Timestamp('2013-01-10 00:00:00-0500', tz='US/Eastern')

In [100]: pd.io.json.dumps(ts, iso_dates=True)
Out[100]: '"2013-01-10T05:00:00.000Z"'

Expected Output

In [100]: pd.io.json.dumps(ts, iso_dates=True)
Out[100]: '"2013-01-10T00:00:00.000-05:00"'

cf

In [109]: ts.isoformat()
Out[109]: '2013-01-10T00:00:00-05:00'

[jreback]

@Komnomnomnom is this an option that you want to add? (e.g. the iso_dates kw)?

[Komnomnomnom]

iso_dates is an existing option on pd.json.dumps. I didn't use pd.to_json as it only takes Series or DataFrame but the date behaviour is the same:

In [5]: df = pd.DataFrame({'A':[ts]})
In [6]: df.to_json(date_format='iso')
Out[6]: '{"A":{"0":"2013-01-10T05:00:00.000Z"}}'

[jreback]

ahh ok, so its a bug. ok then!

[Komnomnomnom]

Sorry I should have been more clear in the description :)

[jreback]

@Komnomnomnom np!

no pressure on fixing this one for 0.18.1. Can be in the next version.

[sdementen]

any milestone for this feature ? currently, need to convert datetime to str before exporting to json and feed plotly charts...

[jorisvandenbossche]

No specific milestone, but contributions are welcome!

[jeliashi]

Any updates?

[jreback]

@jeliashi you or others from the community are welcome to submit a patch

[jeliashi]

@jeliashi you or others from the community are welcome to submit a patch

seems like just updating the files that are copied from numpy (pandas/_libs/tslib/src/datetime/* should fix this. such as this one: https://github.com/numpy/numpy/blob/main/numpy/core/src/multiarray/datetime_strings.c

If I have time, I can tackle, but maybe someone else wants to do so before me

[jeliashi]

So I made a PR to fix this #41573 that tries to mimic what the numpy library does. I've been thinking about this a lot lately and was wondering if it would make more sense to move away from the numpy library way to do it.

They use the C interface to call the pytz objects directly which is what I am doing in this PR. But I don't have to do it their way. They use their pytz objects to calculate new datetime objects in utc time which they then use to calculate differences in minuted between UTC and their local time.

However, I was thinking instead we could use the datetime object directly to create the strings within the C interface.
This would be a little more involved for arrays, but still easily doable. What are people's thoughts?