Course index · Previous · Next

Parse Dates, Convert Timezones, and Define Reporting Windows

Module 3: Cleaning Real-World Data · Lecture 12 of 32

A timestamp is not a calendar date until you choose a timezone. Parse instants explicitly, convert them to the business timezone, and use half-open reporting intervals to avoid midnight and end-of-month errors.

What you will learn

• Distinguish localization from timezone conversion.
• Filter a month using an inclusive start and exclusive end.
• Explain how UTC instants can fall on different local dates.

Understand the problem

Instants and wall-clock labels

The course export contains timestamps with UTC offsets. Parse with utc=True to establish a common timeline. A timestamp without an offset is only a wall-clock label until a source timezone is supplied. tz_localize assigns a timezone interpretation to a naive value; tz_convert changes the display zone of an already aware instant. Applying the wrong operation changes meaning or raises an error.

Report in a declared business timezone

For the fictional store, daily reports use Asia/Kolkata. An event late on January 31 UTC can fall on February 1 in India. Store and compare instants consistently, then derive local dates for business grouping. In regions with daylight saving, nonexistent and ambiguous local times need explicit policies rather than an arbitrary offset assumption.

Half-open windows are composable

Define January as timestamps greater than or equal to January 1 midnight and strictly earlier than February 1 midnight in the reporting timezone. That includes every representable instant in January without guessing the last second or nanosecond of January 31. Adjacent monthly windows then meet without overlapping.

Dates do not prove coverage

The minimum and maximum timestamps reveal observed boundaries, not whether every source partition arrived. A missing day can mean no orders or an absent export. Keep a coverage manifest when the business needs complete daily reporting. The synthetic file spans a known generator window, but that convenience should not become a production assumption.

Run the worked example

Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 12 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.

from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT

orders = load_orders()
local = orders['ordered_at'].dt.tz_convert('Asia/Kolkata')
start = pd.Timestamp('2026-01-01', tz='Asia/Kolkata')
end = pd.Timestamp('2026-02-01', tz='Asia/Kolkata')
mask = local.ge(start) & local.lt(end)
january = orders.loc[mask]
boundary = pd.Series(pd.to_datetime(['2026-01-31T20:00:00Z'], utc=True))
print('January local orders:', len(january))
print('Boundary local date:', boundary.dt.tz_convert('Asia/Kolkata').dt.date.iloc[0])
assert boundary.dt.tz_convert('Asia/Kolkata').dt.month.iloc[0] == 2
assert january['ordered_at'].notna().all()

Verified output

January local orders: 83
Boundary local date: 2026-02-01

Walk through the result

The mask compares timezone-aware instants and correctly interprets the month in India. The boundary example demonstrates why extracting the UTC month first would classify some observations differently. The original ordered_at column stays in UTC, which remains useful for interchange and chronological ordering. A separate local representation avoids silently changing the storage convention while preparing a report.

Common mistakes and diagnosis

Do not compare naive and aware timestamps. Do not strip timezone information merely to silence a warning. Avoid end-of-month strings such as 23:59:59 when the source can carry subsecond precision. Converting timestamps to strings before filtering loses type guarantees and can create lexicographic mistakes. Do not treat a local calendar day as always exactly 24 hours when reporting in daylight-saving regions.

Practice assignment

Calculate February order count and net revenue using the same local-time contract. Confirm that January, February, and March windows partition all accepted orders in this dataset without overlap.

counts = []
for month in [1, 2, 3]:
    left = pd.Timestamp(year=2026, month=month, day=1, tz='Asia/Kolkata')
    right = left + pd.offsets.MonthBegin(1)
    chosen = local.ge(left) & local.lt(right)
    counts.append(int(chosen.sum()))
    if month == 2:
        print('February net INR:', int(orders.loc[chosen, 'net_paise'].sum()) / 100)
assert sum(counts) == len(orders)

Check your understanding

Why is “before February 1” safer than “on or before January 31 at 23:59:59”?

Reference and next step

• pandas time-series guide

Download the complete course kit: datasets, all examples, solutions, and capstone.

Return to the full Data Analysis with Python and pandas curriculum.

Course index · Previous · Next