Browse cheat sheets · Python

Technology: PythonCategory: Data AnalysisDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Parse a datetime column
  5. 2. Parse a known format explicitly
  6. 3. Parse timestamps directly as UTC
  7. 4. Check whether a datetime column is timezone-aware
  8. 5. Localize naive timestamps to the source timezone
  9. 6. Convert timezone-aware timestamps
  10. 7. Handle daylight saving ambiguity deliberately
  11. 8. Filter a full day safely
  12. 9. Filter a full month safely
  13. 10. Filter timezone-aware timestamps with matching boundaries
  14. 11. Filter based on business-local date
  15. 12. Extract datetime components
  16. 13. Normalize timestamps to midnight
  17. 14. Convert to Python date values
  18. 15. Floor timestamps to an interval
  19. 16. Round timestamps
  20. 17. Group by day with pd.Grouper
  21. 18. Group by month
  22. 19. Calculate time differences
  23. 20. Add time offsets
  24. 21. Sort by timestamp
  25. 22. Get the latest row per entity
  26. 23. Get the previous event timestamp
  27. 24. Compare timestamps from different sources safely
  28. 25. Use merge_asof for effective-dated joins
  29. 26. Limit time-distance matches with tolerance
  30. 27. Find invalid or missing dates after parsing
  31. 28. Check date range after parsing
  32. 29. Check duplicate timestamps within a business key
  33. What the result means
  34. What to look for
  35. Many values become NaT
  36. Timestamp appears shifted by several hours
  37. Daily totals differ between systems
  38. End-of-day rows are missing
  39. Duration is unexpectedly less than one day
  40. DST transitions produce ambiguous or missing local times
  41. Production notes
  42. Store event timestamps in UTC when possible
  43. Do not lose timezone information too early
  44. Date-only values and timestamps are different concepts
  45. Half-open intervals avoid boundary bugs
  46. Parsing can be expensive
  47. Timezone conversion can change reporting dates
  48. Calendar arithmetic differs from fixed durations
  49. Common mistakes
  50. 1. Comparing datetime strings instead of parsed timestamps
  51. 2. Using tz_localize() when you mean tz_convert()
  52. 3. Marking local timestamps as UTC
  53. 4. Filtering through 23:59:59
  54. 5. Using .dt.date too early
  55. 6. Using .dt.seconds for full durations
  56. 7. Ignoring daylight saving transitions
  57. 8. Grouping UTC timestamps by day for a local-business report
  58. 9. Overwriting parse failures without inspecting them
  59. Next step
  60. 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() and tz_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.

Next step

Use the time issue to choose the next action.

  • Parse failures → inspect raw values and define explicit formats/source rules.
  • Timezone mismatch → establish the source timezone and normalize to a canonical zone.
  • Business reporting uses local dates → convert timezone before deriving day/month fields.
  • Boundary rows are missing → replace inclusive end timestamps with half-open intervals.
  • Need daily/monthly summaries → use pd.Grouper.
  • Need latest row per entity → sort by key + timestamp and select the actual row.
  • Need nearest effective-dated reference data → use merge_asof.
  • DST ambiguity appears → define explicit ambiguous/nonexistent-time handling.
  • Time processing is expensive → parse once and push filtering/grouping closer to the data source when possible.

← All cheat sheets