Browse cheat sheets · Python
Work with Dates and Timestamps
Parse, compare, filter, normalize, localize, convert, group, and validate dates and timestamps in pandas without introducing timezone or boundary errors.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Parse a datetime column
- 2. Parse a known format explicitly
- 3. Parse timestamps directly as UTC
- 4. Check whether a datetime column is timezone-aware
- 5. Localize naive timestamps to the source timezone
- 6. Convert timezone-aware timestamps
- 7. Handle daylight saving ambiguity deliberately
- 8. Filter a full day safely
- 9. Filter a full month safely
- 10. Filter timezone-aware timestamps with matching boundaries
- 11. Filter based on business-local date
- 12. Extract datetime components
- 13. Normalize timestamps to midnight
- 14. Convert to Python date values
- 15. Floor timestamps to an interval
- 16. Round timestamps
- 17. Group by day with pd.Grouper
- 18. Group by month
- 19. Calculate time differences
- 20. Add time offsets
- 21. Sort by timestamp
- 22. Get the latest row per entity
- 23. Get the previous event timestamp
- 24. Compare timestamps from different sources safely
- 25. Use merge_asof for effective-dated joins
- 26. Limit time-distance matches with tolerance
- 27. Find invalid or missing dates after parsing
- 28. Check date range after parsing
- 29. Check duplicate timestamps within a business key
- What the result means
- What to look for
- Many values become NaT
- Timestamp appears shifted by several hours
- Daily totals differ between systems
- End-of-day rows are missing
- Duration is unexpectedly less than one day
- DST transitions produce ambiguous or missing local times
- Production notes
- Store event timestamps in UTC when possible
- Do not lose timezone information too early
- Date-only values and timestamps are different concepts
- Half-open intervals avoid boundary bugs
- Parsing can be expensive
- Timezone conversion can change reporting dates
- Calendar arithmetic differs from fixed durations
- Common mistakes
- 1. Comparing datetime strings instead of parsed timestamps
- 2. Using tz_localize() when you mean tz_convert()
- 3. Marking local timestamps as UTC
- 4. Filtering through 23:59:59
- 5. Using .dt.date too early
- 6. Using .dt.seconds for full durations
- 7. Ignoring daylight saving transitions
- 8. Grouping UTC timestamps by day for a local-business report
- 9. Overwriting parse failures without inspecting them
- Next step
- Related deep dives
Problem
Dates and timestamps look simple until production data includes:
- multiple formats
- invalid values
- timezone-aware and timezone-naive timestamps
- UTC and local time mixed together
- end-of-day boundary bugs
- daylight saving transitions
- date-only reporting mixed with precise event timestamps
- joins against effective-dated reference data
The goal is to handle time explicitly so that filtering, grouping, comparisons, joins, and reporting remain correct.
You need to answer:
- Is the column actually datetime?
- Which rows failed to parse?
- Are timestamps timezone-aware or naive?
- Is the source timezone known?
- Should the data be stored in UTC?
- Are date-range boundaries inclusive or exclusive?
- Are grouping periods based on UTC or business-local time?
Quick answer
Parse deliberately:
df["event_time"] = pd.to_datetime(
df["event_time"],
errors="coerce",
)
Inspect parse failures:
failed = df.loc[
df["event_time"].isna()
]
Filter a month using a half-open interval:
start = pd.Timestamp("2026-10-01")
end = pd.Timestamp("2026-11-01")
october = df.loc[
(df["event_time"] >= start)
& (df["event_time"] < end)
]
For timezone-aware production data, normalize to UTC:
df["event_time"] = pd.to_datetime(
df["event_time"],
utc=True,
errors="coerce",
)
Then convert to local time only when the business rule requires it.
Code / commands
1. Parse a datetime column
df["event_time"] = pd.to_datetime(
df["event_time"],
errors="coerce",
)
errors="coerce" converts invalid values to NaT.
This is useful during ingestion because bad values become visible instead of raising and stopping immediately.
Do not overwrite the source column before inspecting conversion failures if the original text matters for debugging.
Safer during investigation:
parsed = pd.to_datetime(
df["event_time"],
errors="coerce",
)
failed = df.loc[
df["event_time"].notna()
& parsed.isna()
]
print(failed)
2. Parse a known format explicitly
If the input format is stable, specify it.
df["event_date"] = pd.to_datetime(
df["event_date"],
format="%Y-%m-%d",
errors="coerce",
)
For timestamps:
df["event_time"] = pd.to_datetime(
df["event_time"],
format="%Y-%m-%d %H:%M:%S",
errors="coerce",
)
Explicit formats make intent clearer and help detect unexpected source changes.
3. Parse timestamps directly as UTC
df["event_time"] = pd.to_datetime(
df["event_time"],
utc=True,
errors="coerce",
)
Use this when:
- the source timestamps already include timezone offsets
- the source is documented as UTC
- you want a consistent timezone-aware representation
Be careful when parsing naive local timestamps with utc=True.
If the source text represents local wall-clock time, localize it first rather than pretending it was UTC.
4. Check whether a datetime column is timezone-aware
print(df["event_time"].dtype)
Examples:
datetime64[ns]
means timezone-naive.
datetime64[ns, UTC]
means timezone-aware in UTC.
You can also inspect:
print(df["event_time"].dt.tz)
For a naive datetime Series, this returns None.
5. Localize naive timestamps to the source timezone
Suppose source values are known to represent New York local time:
df["event_time"] = (
pd.to_datetime(
df["event_time"],
errors="coerce",
)
.dt.tz_localize("America/New_York")
)
Use tz_localize() when timestamps are naive but their real timezone is known.
Do not use tz_convert() on naive timestamps.
6. Convert timezone-aware timestamps
Once timestamps are timezone-aware:
df["event_time_local"] = (
df["event_time"]
.dt.tz_convert("America/New_York")
)
Use tz_convert() to represent the same moment in another timezone.
The instant in time stays the same; only the displayed/local wall-clock representation changes.
7. Handle daylight saving ambiguity deliberately
Localization can encounter ambiguous or nonexistent local times.
Example:
localized = (
pd.to_datetime(df["event_time"], errors="coerce")
.dt.tz_localize(
"America/New_York",
ambiguous="NaT",
nonexistent="NaT",
)
)
This is safer for investigation than silently guessing.
Inspect the affected rows before choosing a production rule.
8. Filter a full day safely
For precise timestamps, prefer a half-open interval.
start = pd.Timestamp("2026-10-04")
end = pd.Timestamp("2026-10-05")
day_rows = df.loc[
(df["event_time"] >= start)
& (df["event_time"] < end)
]
This includes every timestamp on October 4 without relying on a synthetic 23:59:59.999999.
9. Filter a full month safely
start = pd.Timestamp("2026-10-01")
end = pd.Timestamp("2026-11-01")
october = df.loc[
(df["event_time"] >= start)
& (df["event_time"] < end)
]
This pattern is robust for timestamps with sub-second precision.
10. Filter timezone-aware timestamps with matching boundaries
If the Series is UTC-aware:
start = pd.Timestamp(
"2026-10-01",
tz="UTC",
)
end = pd.Timestamp(
"2026-11-01",
tz="UTC",
)
filtered = df.loc[
(df["event_time"] >= start)
& (df["event_time"] < end)
]
Do not compare timezone-aware timestamps to timezone-naive boundaries.
11. Filter based on business-local date
Suppose timestamps are stored in UTC but reporting must use New York calendar days.
Convert before deriving the local date:
local_time = (
df["event_time"]
.dt.tz_convert("America/New_York")
)
df["local_date"] = local_time.dt.date
Then filter:
filtered = df.loc[
df["local_date"]
== pd.Timestamp("2026-10-04").date()
]
This matters for events near midnight UTC.
12. Extract datetime components
df["year"] = df["event_time"].dt.year
df["month"] = df["event_time"].dt.month
df["day"] = df["event_time"].dt.day
df["hour"] = df["event_time"].dt.hour
df["weekday"] = df["event_time"].dt.day_name()
Useful for:
- reporting
- feature engineering
- filtering
- time-of-day analysis
Do not permanently materialize components you can derive cheaply unless they are needed downstream.
13. Normalize timestamps to midnight
df["event_date"] = (
df["event_time"]
.dt.normalize()
)
For timezone-aware timestamps, the timezone is preserved.
Use this when you need a datetime-like day key instead of Python date objects.
14. Convert to Python date values
df["event_date"] = (
df["event_time"]
.dt.date
)
This produces Python date objects and generally yields an object-like Series.
For pandas-native time operations, .dt.normalize() or .dt.floor("D") often preserves a more useful datetime dtype.
15. Floor timestamps to an interval
Hourly:
df["event_hour"] = (
df["event_time"]
.dt.floor("h")
)
Daily:
df["event_day"] = (
df["event_time"]
.dt.floor("D")
)
Use this for bucketing exact timestamps into reporting intervals.
16. Round timestamps
df["rounded_time"] = (
df["event_time"]
.dt.round("15min")
)
Use rounding only when nearest interval is really the intended rule.
For reporting buckets, floor is often more predictable.
17. Group by day with pd.Grouper
daily = (
df.groupby(
pd.Grouper(
key="event_time",
freq="D",
)
)
.agg(
events=("event_id", "count"),
revenue=("revenue", "sum"),
)
.reset_index()
)
This keeps the time key as datetime.
18. Group by month
monthly = (
df.groupby(
pd.Grouper(
key="event_time",
freq="MS",
)
)
.agg(
events=("event_id", "count"),
revenue=("revenue", "sum"),
)
.reset_index()
)
MS means month-start frequency.
Use ME when month-end labels are explicitly desired in a pandas version that supports the alias.
19. Calculate time differences
df["processing_time"] = (
df["completed_at"]
- df["started_at"]
)
Result:
timedelta64[ns]
Convert to seconds:
df["processing_seconds"] = (
df["processing_time"]
.dt.total_seconds()
)
Do not use .dt.seconds when you need full duration.
.dt.seconds is only the seconds component within the day.
20. Add time offsets
df["expires_at"] = (
df["created_at"]
+ pd.Timedelta(hours=24)
)
Days:
df["next_day"] = (
df["event_time"]
+ pd.Timedelta(days=1)
)
For calendar-aware offsets:
df["next_month"] = (
df["event_time"]
+ pd.offsets.MonthBegin(1)
)
Use calendar offsets when “next month” is business-calendar logic rather than a fixed number of days.
21. Sort by timestamp
df = df.sort_values(
["customer_id", "event_time"]
)
This is important before:
- forward fill
- backward fill
- rolling calculations
- latest-record selection
- sequence analysis
Never assume source row order is chronological.
22. Get the latest row per entity
latest = (
df.sort_values(
["customer_id", "event_time"]
)
.groupby(
"customer_id",
as_index=False,
)
.tail(1)
)
This preserves the actual row associated with the latest timestamp.
Do not use:
groupby().agg("max")
to reconstruct a latest row across unrelated columns.
23. Get the previous event timestamp
df = df.sort_values(
["customer_id", "event_time"]
)
df["previous_event_time"] = (
df.groupby("customer_id")["event_time"]
.shift(1)
)
df["seconds_since_previous"] = (
df["event_time"]
- df["previous_event_time"]
).dt.total_seconds()
Useful for:
- sessionization
- event-gap analysis
- latency calculations
- repeated-request detection
24. Compare timestamps from different sources safely
Before comparison, normalize both sides.
Example:
left_time = pd.to_datetime(
left["event_time"],
utc=True,
errors="coerce",
)
right_time = pd.to_datetime(
right["event_time"],
utc=True,
errors="coerce",
)
Then compare or merge.
Do not compare a UTC-aware Series against a local naive Series and assume the difference is only formatting.
25. Use merge_asof for effective-dated joins
Example: attach the latest price in effect before each event.
events = events.sort_values(
["product_id", "event_time"]
)
prices = prices.sort_values(
["product_id", "effective_time"]
)
result = pd.merge_asof(
events,
prices,
left_on="event_time",
right_on="effective_time",
by="product_id",
direction="backward",
)
Use this when exact timestamp equality is not the business rule.
Validate:
- sort order
- timezone consistency
- entity key
- direction
- tolerance if applicable
26. Limit time-distance matches with tolerance
result = pd.merge_asof(
events,
prices,
left_on="event_time",
right_on="effective_time",
by="product_id",
direction="backward",
tolerance=pd.Timedelta("7D"),
)
This prevents matching to reference data that is too old.
Use a tolerance only when the business rule defines a meaningful maximum gap.
27. Find invalid or missing dates after parsing
parsed = pd.to_datetime(
df["event_time_raw"],
errors="coerce",
)
invalid = df.loc[
df["event_time_raw"].notna()
& parsed.isna()
]
print(invalid)
This is a critical ingestion check.
A successful pd.to_datetime() call with errors="coerce" can still hide bad source values unless you inspect the coercions.
28. Check date range after parsing
print(df["event_time"].min())
print(df["event_time"].max())
Use this to detect:
- future dates
- unexpectedly old dates
- wrong year parsing
- timezone shifts
- missing source periods
29. Check duplicate timestamps within a business key
duplicates = df.loc[
df.duplicated(
subset=["customer_id", "event_time"],
keep=False,
)
].sort_values(
["customer_id", "event_time"]
)
print(duplicates)
A duplicate timestamp may be valid.
The important question is whether the business key and event grain permit multiple rows at the same instant.
What the result means
| Concept | Meaning |
|---|---|
datetime64[ns] |
pandas/NumPy timezone-naive datetime dtype. |
datetime64[ns, UTC] |
timezone-aware datetime dtype in UTC. |
pd.Timestamp |
pandas scalar representing a date/time. |
NaT |
missing datetime/timedelta value. |
tz_localize() |
Assigns a timezone to naive timestamps without changing the wall-clock value. |
tz_convert() |
Converts timezone-aware timestamps to another timezone while preserving the instant. |
Timedelta |
Duration between timestamps or a fixed time offset. |
DateOffset |
Calendar-aware offset such as month or business-day movement. |
floor() |
Rounds down to a time boundary. |
normalize() |
Sets time-of-day to midnight while preserving date/timezone context. |
What to look for
Many values become NaT
This means parsing failed.
Possible causes:
- format changed
- mixed formats
- impossible dates
- timezone text
- malformed source data
Inspect the original strings.
Timestamp appears shifted by several hours
Likely timezone confusion.
Check:
- source timezone
- whether the source was already UTC
- whether
tz_localize()andtz_convert()were used in the correct order
Daily totals differ between systems
Check whether systems group by:
- UTC date
- local business date
- server timezone
- user timezone
The same event can belong to different calendar days depending on the reporting zone.
End-of-day rows are missing
This often comes from filtering with:
timestamp <= "2026-10-31"
which represents midnight at the start of the day.
Use a half-open interval ending at the next day/month.
Duration is unexpectedly less than one day
Check whether code used:
timedelta.dt.seconds
instead of:
timedelta.dt.total_seconds()
DST transitions produce ambiguous or missing local times
This is expected behavior in timezones with daylight saving transitions.
Handle ambiguity explicitly rather than guessing silently.
Production notes
Store event timestamps in UTC when possible
UTC is usually the safest canonical representation for:
- event storage
- cross-region systems
- APIs
- distributed processing
- data lakes
Convert to business-local time at the reporting/presentation boundary when required.
Do not lose timezone information too early
Converting timezone-aware timestamps to naive timestamps can make later interpretation ambiguous.
Keep timezone awareness until the business rule explicitly requires otherwise.
Date-only values and timestamps are different concepts
Examples:
2026-10-04
2026-10-04T15:27:18Z
The first is a calendar date.
The second is an instant in time.
Do not model them interchangeably.
Half-open intervals avoid boundary bugs
Prefer:
start <= timestamp < next_start
over manually constructing an end-of-day maximum timestamp.
This pattern is easier to reason about and works with arbitrary timestamp precision.
Parsing can be expensive
On large DataFrames:
- parse once
- avoid repeated conversion
- specify known formats where appropriate
- select only required columns
- push date filtering to the storage/query engine when possible
Timezone conversion can change reporting dates
A UTC timestamp near midnight may become the previous local day after conversion.
Always convert before deriving business-local date parts.
Calendar arithmetic differs from fixed durations
A month is not always 30 days.
Use DateOffset/calendar frequencies for calendar-based rules.
Use Timedelta for fixed elapsed durations.
Common mistakes
1. Comparing datetime strings instead of parsed timestamps
String comparison can appear to work for ISO formats but is fragile and semantically wrong for general datetime logic.
Parse first.
2. Using tz_localize() when you mean tz_convert()
Use:
tz_localize -> naive to timezone-aware
tz_convert -> aware timezone to another timezone
They solve different problems.
3. Marking local timestamps as UTC
This changes the meaning of the data.
If 08:00 was New York local time, do not simply assign UTC.
Localize to New York first, then convert to UTC.
4. Filtering through 23:59:59
This can still miss timestamps with finer precision.
Use the next boundary with <.
5. Using .dt.date too early
It converts timestamps to Python date objects and loses time-of-day.
Keep timestamps until you truly need date-only semantics.
6. Using .dt.seconds for full durations
.dt.seconds excludes whole days.
Use .dt.total_seconds().
7. Ignoring daylight saving transitions
Local times can be ambiguous or nonexistent.
Handle them explicitly.
8. Grouping UTC timestamps by day for a local-business report
Convert to the business timezone first.
Otherwise, events near midnight can land in the wrong reporting day.
9. Overwriting parse failures without inspecting them
errors="coerce" is helpful, but it can hide bad data if you never review newly created NaT values.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.