Dates, Times and Time Zones — Python Primer

Time flies, as the saying goes. It also stretches, repeats itself once a year, and occasionally skips an hour entirely while everyone is asleep.

Dates look simple and are not. They have variable-length months, leap years, time zones that shift twice a year, and an ambiguity in the middle of every American date that no amount of care removes.

The good news is that pandas handles nearly all of it, provided you parse deliberately and store in UTC.

Parsing

df['created_at'] = pd.to_datetime(df.created_at)

That works when the format is unambiguous. Frequently it is not.

pd.to_datetime('03/04/2026')     # 3 April or 4 March?

Pandas guesses American. Half the world writes the other way. The fix is to say what you mean:

pd.to_datetime(df.date, format='%d/%m/%Y')      # explicit, and fast
pd.to_datetime(df.date, dayfirst=True)          # explicit enough

Always pass format when you know it. It removes the ambiguity and it is dramatically faster, because pandas stops trying to infer the format for every row.

The format codes you need:

Code	Means	Example
`%Y`	4-digit year	2026
`%y`	2-digit year	26
`%m`	Month number	03
`%d`	Day	15
`%H` `%M` `%S`	Hour, minute, second	14 30 00
`%b` `%B`	Month name, short and full	Mar, March

When parsing fails

parsed = pd.to_datetime(df.date, format='%Y-%m-%d', errors='coerce')
bad = df.loc[parsed.isna() & df.date.notna(), 'date']
print(f"{len(bad)} unparseable values")
print(bad.value_counts().head(10))

errors='coerce' turns failures into NaT rather than raising, which lets you see all the problems at once instead of fixing them one at a time. Then look at what failed. It is usually one alternative format, or a placeholder like 0000-00-00.

For genuinely mixed formats:

parsed = pd.to_datetime(df.date, format='%Y-%m-%d', errors='coerce')
fallback = pd.to_datetime(df.date, format='%d/%m/%Y', errors='coerce')
df['date'] = parsed.fillna(fallback)

Try the most common format first, fill the gaps with the second. Trying to infer per-row is slow and produces silent mistakes.

Excel serial dates

Dates that arrive as numbers around 45000 are Excel serials, counting days from 1900:

pd.to_datetime(df.date_serial, unit='D', origin='1899-12-30')

That origin is not a typo. Excel believes 1900 was a leap year, and the offset compensates.

Unix timestamps

pd.to_datetime(df.ts, unit='s')      # seconds since 1970
pd.to_datetime(df.ts, unit='ms')     # milliseconds — check which you have

A timestamp around 1.7 billion is seconds. Around 1.7 trillion is milliseconds. Getting it wrong puts your data in 1970 or in the year 55000, so it is at least obvious.

Extracting Parts

Once parsed, the .dt accessor works like .str does for text:

d = df.created_at.dt

d.year, d.month, d.day
d.hour, d.minute
d.dayofweek          # Monday = 0
d.day_name()         # 'Monday'
d.month_name()
d.quarter
d.is_month_end
d.days_in_month
d.date               # drop the time
d.normalize()        # midnight of the same day, keeps the datetime type

These become features constantly:

df = df.assign(
    hour=df.created_at.dt.hour,
    weekday=df.created_at.dt.dayofweek,
    is_weekend=df.created_at.dt.dayofweek >= 5,
    month=df.created_at.dt.month,
)

One warning about cyclical values. Hour 23 and hour 0 are one hour apart, but as numbers they are twenty-three apart, and a model will believe the numbers. Encode the cycle:

import numpy as np
h = df.created_at.dt.hour
df['hour_sin'] = np.sin(2 * np.pi * h / 24)
df['hour_cos'] = np.cos(2 * np.pi * h / 24)

Now midnight and 23:00 sit next to each other, which is the truth.

Arithmetic

Subtracting two datetimes gives a Timedelta:

df['days_since'] = (pd.Timestamp.now(tz='UTC') - df.created_at).dt.days

df.created_at + pd.Timedelta(days=7)
df.created_at + pd.DateOffset(months=1)     # calendar-aware

The difference between those last two matters. Timedelta(days=30) is exactly thirty times twenty-four hours. DateOffset(months=1) moves to the same day next month, handling different month lengths. For “a month later” you want the second.

For working days:

from pandas.tseries.offsets import BDay
df.created_at + 5 * BDay()          # five business days

Time Zones

The rules that prevent almost all timezone pain:

Store UTC. Convert only for display.

utc = pd.to_datetime(df.created_at, utc=True)      # parse straight to UTC
local = utc.dt.tz_convert('Asia/Tokyo')          # for humans

A datetime without a timezone is naive. It represents a wall-clock reading with no information about where. Naive datetimes from different sources cannot be safely compared, and pandas will refuse to compare naive with aware:

TypeError: Cannot compare tz-naive and tz-aware timestamps

That error is the library protecting you. The fix is to localise the naive one, saying which zone it was recorded in:

naive = pd.to_datetime(df.local_time)
aware = naive.dt.tz_localize('Europe/London').dt.tz_convert('UTC')

flowchart LR
    N["naive<br/>'2026-03-15 14:30'<br/><i>where?</i>"]
    N -->|"tz_localize('Europe/London')<br/><b>declares</b> the zone"| A["aware<br/>14:30 London"]
    A -->|"tz_convert('UTC')<br/><b>changes</b> the zone"| U["aware<br/>14:30 UTC"]
    style N fill:#f7e8e8,stroke:#c02020
    style U fill:#eef1fc,stroke:#2141c8

tz_localize says “this reading was taken here”. tz_convert says “express this moment in a different zone”. Confusing them shifts your data by hours.

The hour that happens twice

When clocks go back, one local hour occurs twice. When they go forward, one hour does not exist. Localising those times is genuinely ambiguous, and pandas will tell you so:

s.dt.tz_localize('Europe/London', ambiguous='infer', nonexistent='shift_forward')

Use fixed offsets like UTC+05:30 only when you truly mean a fixed offset. A named zone like Asia/Lagos carries the historical rules; a fixed offset does not, and will be wrong for past dates in zones whose rules changed.

Time Series Operations

Once the index is a datetime, a set of operations unlocks:

df = df.set_index('created_at').sort_index()

df.loc['2026']                     # a whole year
df.loc['2026-03']                  # a month
df.loc['2026-03-01':'2026-03-15']  # a range, inclusive at both ends

Resampling

Changing the frequency, which is groupby for time:

daily = df.resample('D').agg(orders=('order_id', 'count'),
                             revenue=('revenue', 'sum'))

weekly = df.resample('W').revenue.sum()
monthly = df.resample('ME').revenue.sum()      # ME = month end

Resampling down aggregates. Resampling up creates gaps you must decide how to fill:

hourly = daily.resample('h').ffill()      # carry the last value forward

Note that resample('D').sum() on a period with no data gives zero, while .mean() gives NaN. That difference matters when you plot the result.

Rolling windows

daily['revenue_7d'] = daily.revenue.rolling(7).mean()
daily['revenue_28d'] = daily.revenue.rolling(28, min_periods=14).mean()

min_periods decides how much data a window needs before it produces a number. Without it, the first six days of a seven-day average are NaN.

A rolling mean is the standard way to make a noisy daily series readable. Use an odd window when you want it centred, and be aware that center=True uses future values, which is fine for a chart and leakage for a model.

Lags and changes

daily['prev_day'] = daily.revenue.shift(1)
daily['change'] = daily.revenue.diff()
daily['pct_change'] = daily.revenue.pct_change()
daily['same_day_last_week'] = daily.revenue.shift(7)

shift(1) moves values forward in time, so each row sees yesterday. Only ever shift positively for model features. A negative shift brings the future into the present, which is the purest form of leakage.

The Gaps You Cannot See

A time series with missing days does not look wrong. resample reveals it:

full = df.resample('D').size()
print(f"{(full == 0).sum()} days with no data")
print(full[full == 0].index[:10])

Missing days are almost always meaningful: a system outage, a holiday, a collection failure. Find them before you average over them.

What Carries Forward

Pass format explicitly. It removes ambiguity and is much faster.

Use errors='coerce' then inspect the failures as a group.

Store UTC, convert for display. tz_localize declares a zone; tz_convert changes one.

Encode cyclical time as sine and cosine so that hour 23 sits next to hour 0.

Only shift positively for features. shift(-1) is leakage.

Next: categories, and turning them into numbers without leaking your target into the training data.