Python Pandas add local time columns from UTC column

Question

I have a Panda df with a UTC column (not a timeseries index, a UTC Date field). The data is displayed in the df as:

2017-11-13 05:00:00

I need to create a new column (leaving the UTC column intact), but with localtime and have it displayed as:

2017-11-13 00:00:00

(assuming US/Eastern)

I tried:

df['DATE_TIME_UTC'].dt.tz_localize('US/Eastern')

but this displays:

2017-11-13 05:00:00-5:00

and when pushed to the DB, it shows the UTC time again. Please advise how store the datetime in the new field as localtime.

Thanks

I had a similar problem (but in pyspark) and I just convert my date variable into a string — Sotos
– Sotos, Commented Nov 26, 2018 at 13:48
What @Sotos said. I had this exact same issue in BigQuery also. The db 'silently' drops the timezone offset. Easiest (but not best) way is to convert to string prior to writing to DB. — jhomr
– jhomr, Commented Nov 26, 2018 at 16:31

emmet02 · Accepted Answer · 2018-11-26 14:08:06Z

1

The datetime is currently timezone naive, only you the user know that it is in UTC.

You need to convert it to a timezone aware datetime, by using the following

# Create Timezone Aware using tz_localize
df['DATE_TIME_UTC'] = df['DATE_TIME_UTC'].dt.tz_localize('UTC')

Next you want to convert it to be the local timezone

# Convert to local timezone
df['DATE_TIME_LOCAL'] = df['DATE_TIME_UTC'].dt.tz_convert('US/Eastern')

Now you should have the timezone information that you require.

answered Nov 26, 2018 at 14:08

emmet02

9425 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the explanation. This is what I was looking for.