Feature Engineering for Dates and Times in Python

Contents

Figure 1: A timestamp hides rush hour, weekends, and holidays — the model sees none of it until you break the column apart

Two years of hourly demand, a known recipe, and the encodings that recovered it

A timestamp is the most information-packed column in most datasets, and models can't read it. Hand a raw date to a model and it sees a big number. Hand it the right pieces, and it sees rush hour, weekends, and holidays.

Two years of hourly demand data were built with a known recipe, then different time features were tested on how well they recover it. The results show one encoding that fails badly, one that works for tiny cost, and a few traps that can quietly corrupt your scores.

Start With What pandas Gives You

pandas' .dt accessor pulls out the obvious parts: year, month, day, hour, dayofweek (Monday is 0), dayofyear, quarter, and flags like is_month_end. A quick check on Sunday 23:00 followed by Monday 00:00 gave hour 23 then 0, and day-of-week 6 then 0, exactly as expected.

That's the easy part. The interesting part is what you do with them.

The Synthetic Data

The series has 17,520 hourly rows, with a daily cycle (deliberately not a single sine wave), lower weekends, an annual season, a slow trend, a 30-unit drop on US federal holidays (taken from pandas' USFederalHolidayCalendar, 21 holiday days), and noise with standard deviation 5. Training used the first 18 months, testing the last 6. The best possible test R² is 0.958, set by the noise.

Cyclical Features: The Clock Isn't a Line

Hour 23 and hour 0 are one hour apart, but a raw integer says they're 23 apart. Sine and cosine encoding fixes that: in the check, hours 23 and 0 sit 0.261 apart in sin/cos space, identical to hours 11 and 12.

Now the test — a Ridge regression with all other features fixed, changing only how the hour is encoded:

Hour encoding	Columns	Test R²	Test RMSE
Raw hour (0–23)	1	0.800	10.84
One-hot hour	24	0.958	5.00
Sin/cos, 1 harmonic	2	0.905	7.49
Sin/cos, 2 harmonics	4	0.958	5.00
Sin/cos, 3 harmonics	6	0.958	5.00
Periodic spline (13 knots)	12	0.958	5.00

Here's the story:

• Raw hour is the worst. A line through a daily rhythm can't bend, so R² stalled at 0.800.
• One sine/cosine pair helped but wasn't enough. The daily pattern has two components, and a single harmonic captured only one.
• Two harmonics reached the noise floor with 4 columns, matching one-hot's 24 columns and the spline's 12.

import numpy as np

hour = df["timestamp"].dt.hour
df["hour_sin"] = np.sin(2 * np.pi * hour / 24)
df["hour_cos"] = np.cos(2 * np.pi * hour / 24)
# underfit? add a second harmonic:
df["hour_sin2"] = np.sin(4 * np.pi * hour / 24)
df["hour_cos2"] = np.cos(4 * np.pi * hour / 24)

One honest caveat: the daily pattern was built from exactly two harmonics, so "two harmonics is enough" is baked into the setup. Real daily patterns aren't that tidy. The takeaway is the mechanism: if one sine/cosine pair underfits, add harmonics or switch to one-hot or splines. IMO, one-hot is the safe fallback when you have plenty of data and only 24 values.

FYI: scikit-learn's cyclical-feature example runs a similar comparison on real bike-rental data, reporting that gradient boosting on raw integers reached an MAE of 0.044 while a naive linear model scored 0.142 — the same "trees cope, lines don't" pattern.

Trees Can Skip the Cyclical Trick

Gradient boosting doesn't need sine waves. HistGradientBoostingRegressor on raw integers (hour, day of week, day of year, elapsed years) hit a test R² of 0.911. Trees can learn non-monotonic relationships on their own, and scikit-learn's example makes the same point: trees can handle ordinal time features that wreck linear models.

Calendar Features Trees and Lines Both Need

Holidays are the cheapest big win. A single 0/1 is_holiday column:

Model	Without holiday flag	With holiday flag
Ridge + spline hour	0.912	0.958
HistGradientBoosting (raw integers)	0.911	0.946

Neither model could invent holidays from day-of-year alone, since they move around (and only 21 days in 18 months were holidays). The flag closed most of the remaining gap. pandas supplies calendars like USFederalHolidayCalendar; for other countries, you'll need your own holiday list.

Other useful derived features: weekend flags, is_month_end, days since an event, and time elapsed since a start date (the t column, which captured the trend).

Leakage: Time Doesn't Shuffle

Time-based data punishes the default workflow. A demand model using hour, day of week, and demand 24 and 168 hours earlier (lag features) was scored two ways:

Validation	R²
Shuffled K-fold	0.905
Time-ordered CV (with a 168-hour gap)	0.834

Shuffling let the model train on days surrounding each test day, which flatters it. Time-ordered splits mimic real forecasting. Not all of that 0.07 is leakage — early time-ordered folds train on less data and haven't seen a full year — but shuffled CV is still the wrong test for a forecast. Use TimeSeriesSplit.

The sneakier leak is a rolling average:

Rolling-mean feature	Time-ordered CV R²
Includes the current hour	0.830
Past hours only (shift(1) first)	0.804

The peeking version scored higher, because it contains a sliver of the answer. The effect is modest here (the current hour carries only 1/24 of a 24-hour average), but with shorter windows it grows. Always shift before you roll.

A real-world echo of that trap: a demand forecast showed a reassuring 0.90+ under shuffled CV during development, then underperformed badly in its first weeks live — the rolling-mean feature had been computed on the full series, so every training row could see a window that overlapped its own target. The score was honest about the wrong task. Rebuilding with shift(1) before .rolling() and time-ordered splits dropped the reported number and made it mean something.

Time Zones and Daylight Saving

Daylight saving breaks the idea that every day has 24 hours. Tested on America/New_York:

• Spring forward (2024-03-10 02:30) doesn't exist. tz_localize raised a ValueError.
• Fall back (2024-11-03 01:30) happens twice. It raised a ValueError too.
• Fixes: nonexistent='shift_forward' moved the first to 03:00 (−04:00). For the second, ambiguous=[True] picked the first occurrence (−04:00), and [False] picked the second (−05:00).

Two more traps from the run:

• The calendar day 2024-03-10 contains only 23 real hours in that zone, so don't assume 24 rows per day.
• Timestamp + Timedelta(days=1) added 24 absolute hours and landed at 01:00 the next day, while + DateOffset(days=1) kept the wall-clock time at 00:00. The pandas docs describe this exact distinction.

The safest habit: store timestamps in UTC, convert to local time only to build features like "hour of day" that depend on human schedules.

A Checklist

1. Extract parts with .dt: hour, day of week, month, day of year.
2. Linear models: encode cyclical parts with sin/cos (add harmonics if needed), periodic splines, or one-hot. Raw integers fail.
3. Tree models: raw integers are fine.
4. Add holiday and event flags. They gave the biggest jump in these tests.
5. Add elapsed time for trends.
6. Validate with time-ordered splits, and shift before rolling.
7. Use UTC, and handle DST gaps and overlaps explicitly — the same fit-on-training-discipline the preprocessing guide applies to every learned step.

Common Pitfalls

• Handing a raw datetime integer to a linear model — 16 points of R² gone before you start.
• One sine/cosine pair and calling it done — underfit patterns need harmonics, splines, or one-hot.
• Assuming a day always has 24 hours — DST days have 23 or 25, and your row counts know it.
• Shuffled CV on a forecasting problem — it trains on the future's neighbors.
• Rolling means that include the current row — shift before you roll, always.
• Localizing across DST without nonexistent/ambiguous — a ValueError in production, or silently duplicated hours.
• Ignoring holiday flags because day-of-year is "close enough" — holidays move, and both trees and lines failed without the flag.

Unlock AI That Actually Works

Get lifetime access to GPT-6 Astra, Claude Fable 5.1, Gemini 3.5, Grok 4.5, and more — all in one platform. Build websites, apps, videos, content, and digital products from a single command. No monthly fees. No tool-hopping.

Click here to get GPTAstra Max now — one-time payment, lifetime access.

Frequently Asked Questions

How should I encode hour of day for a linear model?

With cyclical encoding, not raw integers. A raw 0–23 hour column stalled at 0.800 test R² in these tests, because a line through a daily rhythm can't bend — and hour 23 sits 23 units from hour 0 instead of 1. A sine/cosine pair fixed the wrap-around (hours 23 and 0 are 0.261 apart in sin/cos space, same as 11 and 12) but underfit a two-component pattern; two harmonics reached the noise floor with 4 columns, matching one-hot's 24 columns. If one pair underfits, add harmonics, use a periodic spline, or fall back to one-hot.

Do tree models need cyclical time features?

No. Gradient boosting on raw integer features (hour, day of week, day of year, elapsed time) reached 0.911 test R² here, and adding the holiday flag lifted it to 0.946. Trees learn non-monotonic relationships on their own, so sine/cosine encoding buys them nothing — though calendar flags like is_holiday still helped both model types, because holidays move around the calendar and can't be inferred from day-of-year alone.

Why is shuffled cross-validation wrong for time series?

Because shuffling lets the model train on days surrounding each test day, which flatters it. With lag features (demand from 24 and 168 hours earlier), shuffled K-fold scored 0.905 while time-ordered CV with a 168-hour gap scored 0.834. Part of that gap reflects early folds training on less data, so not all 0.07 is leakage — but shuffled CV is still the wrong test for a forecast. Use TimeSeriesSplit, and shift before you roll: a rolling mean that includes the current hour peeked at the answer and scored 0.830 versus 0.804 for past-hours-only.

How do I handle daylight saving time when engineering features?

Store timestamps in UTC and convert to local time only to build features that depend on human schedules (hour of day, rush hour). When localizing a naive series across DST, pandas raises ValueError for both the nonexistent spring-forward time (2024-03-10 02:30) and the ambiguous fall-back time (2024-11-03 01:30) — resolve them with nonexistent='shift_forward' and ambiguous=[True/False] for the first or second occurrence. A DST day has 23 or 25 real hours, so never assume 24 rows per day.

Wrapping This Up

Raw timestamps carry rhythm that models can't see until you extract it. Linear models need cyclical encodings, because raw hours cost about 16 points of R². Trees manage with integers. Holiday flags added 3 to 5 points more, and honest validation needs time-ordered splits.

Pick one timestamp column in your data, extract hour and day of week, and compare a raw encoding against sine and cosine on a time-ordered split.

Related Articles

• Data Preprocessing for Machine Learning: Complete Step-by-Step Guide
• Building a Feature Engineering Pipeline with Scikit-learn Pipelines
• Data Validation with Great Expectations: Catch Bad Data Early
• Batch vs Streaming Data Pipelines: Which One Fits?