How to use Pandas Timestamp fold argument?

user_4621022 0
Viewed 127

When playing around with timezone conversions and dst impact, I have a hard time figuring out Pandas implementation of the fold parameter of the Timestamp constructor. The documentation mentions:

Due to daylight saving time, one wall clock time can occur twice when shifting from summer to winter time; fold describes whether the datetime-like corresponds to the first (0) or the second time (1) the wall clock hits the ambiguous time.

So far no surprise, but when I run the following code:

import pandas as pd
from datetime import datetime

pre_fold = pd.Timestamp(datetime(2022,10,30,1,30,0), tz="CET")
in_fold_fold0 = pd.Timestamp(datetime(2022,10,30,2,30,0), tz="CET")
in_fold_fold1 = pd.Timestamp(datetime(2022,10,30,2,30,0), tz="CET", fold=1)
post_fold = pd.Timestamp(datetime(2022,10,30,3,30,0), tz="CET")

print(f"fold0: {in_fold_fold0.fold}")
print(f"fold1: {in_fold_fold1.fold}")

print(f"Pre CET fold:       {pre_fold}  ->  UTC {pre_fold.tz_convert(tz='UTC')}")
print(f"In CET fold, fold0: {in_fold_fold0}  ->  UTC {in_fold_fold0.tz_convert(tz='UTC')}")
print(f"In CET fold, fold1: {in_fold_fold1}  ->  UTC {in_fold_fold1.tz_convert(tz='UTC')}")
print(f"Post CET fold:      {post_fold}  ->  UTC {post_fold.tz_convert(tz='UTC')}")

the output is not as expected:

fold0: 0
fold1: 1
Pre CET fold:       2022-10-30 01:30:00+02:00  ->  UTC 2022-10-29 23:30:00+00:00
In CET fold, fold0: 2022-10-30 02:30:00+01:00  ->  UTC 2022-10-30 01:30:00+00:00
In CET fold, fold1: 2022-10-30 02:30:00+01:00  ->  UTC 2022-10-30 01:30:00+00:00
Post CET fold:      2022-10-30 03:30:00+01:00  ->  UTC 2022-10-30 02:30:00+00:00

Line 4 should be:

In CET fold, fold0: 2022-10-30 02:30:00+02:00  ->  UTC 2022-10-30 00:30:00+00:00

What am I missing here?

PS: Using python's datetime objects results in expected output:

from datetime import datetime
from dateutil import tz

dt_pre_fold = datetime(2022,10,30,1,30,0, tzinfo=tz.gettz("CET"))
dt_in_fold_fold0 = datetime(2022,10,30,2,30,0, tzinfo=tz.gettz("CET"))
dt_in_fold_fold1 = datetime(2022,10,30,2,30,0, tzinfo=tz.gettz("CET"), fold=1)
dt_post_fold = datetime(2022,10,30,3,30,0, tzinfo=tz.gettz("CET"))

print(f"Pre CET fold:       {dt_pre_fold}  ->  UTC {dt_pre_fold.astimezone(tz.gettz('UTC'))}")
print(f"In CET fold, fold0: {dt_in_fold_fold0}  ->  UTC {dt_in_fold_fold0.astimezone(tz.gettz('UTC'))}")
print(f"In CET fold, fold1: {dt_in_fold_fold1}  ->  UTC {dt_in_fold_fold1.astimezone(tz.gettz('UTC'))}")
print(f"Post CET fold:      {dt_post_fold}  ->  UTC {dt_post_fold.astimezone(tz.gettz('UTC'))}")

Output:

Pre CET fold:       2022-10-30 01:30:00+02:00  ->  UTC 2022-10-29 23:30:00+00:00
In CET fold, fold0: 2022-10-30 02:30:00+02:00  ->  UTC 2022-10-30 00:30:00+00:00
In CET fold, fold1: 2022-10-30 02:30:00+01:00  ->  UTC 2022-10-30 01:30:00+00:00
Post CET fold:      2022-10-30 03:30:00+01:00  ->  UTC 2022-10-30 02:30:00+00:00
pandas fold timestamp datetime python
2 Answers

It appears that the timezone info is not correctly specified:

# using your code
x = pd.Timestamp(datetime(2022,10,30,2,30,0), fold = 0, tz="CET")
x.tz_convert('UTC')
# Timestamp('2022-10-30 01:30:00+0000', tz='UTC')

But if you use from dateutil import tz

x = pd.Timestamp(datetime(2022,10,30,2,30,0), fold = 0, tz=tz.gettz("CET"))
x.tz_convert('UTC')
# Timestamp('2022-10-30 00:30:00+0000', tz='UTC')

It returns the correct value

user_147516190

This kind of circumvents the question but instead of using 'fold', you can localize a timestamp to a certain time zone and use the ambiguous keyword to specify if it should be the DST or the non-DST time, from the docs:

ambiguous [...] bool-ndarray where True signifies a DST time, False signifies a non-DST time (note that this flag is only applicable for ambiguous times)

So you could have done what you need like

import pandas as pd

f0 = pd.Timestamp("2022-10-30 02:30:00").tz_localize("Europe/Berlin", ambiguous=True)
f1 = pd.Timestamp("2022-10-30 02:30:00").tz_localize("Europe/Berlin", ambiguous=False)

print(f0.tz_convert('UTC'))
print(f1.tz_convert('UTC'))
# 2022-10-30 00:30:00+00:00 # was DST, UTC+2
# 2022-10-30 01:30:00+00:00 # was non-DST, UTC+1

As to why 'fold' does not work properly, I think this goes back to the fact that pandas is still using pytz internally for time zone handling. pytz does not support 'fold', it uses keyword 'is_dst' instead. You can find more info on that e.g. in this blog post by Paul Ganssle. There's also some hints deep in the pandas src. In contrast, dateutil's timezone does support 'fold', which is likely the reason why @ZLi 's solution works.

side notes:

  • it is better to use actual IANA time zone names to avoid any ambiguities the abbreviations might have
  • don't mix native Python datetime and pandas' datetime, to avoid some of the rough edges of native Python datetime
user_101974180
Related