Create intermediate Works with: Any AI

Datetime and Time Zone Cleaning Guide

Table output Has example

Parse mixed timestamps without silent errors.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

Help me convert messy timestamps into reliable datetimes. Sample values from each date column: [SAMPLE_VALUES]. Where the data came from and in which time zone events happened: [SOURCE_AND_TIMEZONES]. What I need to compute (durations, daily counts, business hours): [GOAL].

Produce:
1. A table classifying each sample format (ISO with offset, naive local time, epoch seconds or milliseconds, day-first versus month-first, text such as "Mar 3rd").
2. The parsing strategy per format, using pd.to_datetime with explicit format strings and errors="coerce", plus the code that counts and shows the rows that failed.
3. A rule for ambiguous dates such as 04/05/2026: how to decide day-first or month-first from the data (for example whether any value has a first part above 12) and what to do if it stays ambiguous.
4. Time zone handling: localize naive values only with a stated source zone, convert to UTC for storage, and convert back for display. Explain daylight saving problems (a missing hour in spring, a repeated hour in autumn) with the pandas options ambiguous and nonexistent.
5. Five unit tests with exact input values and expected outputs, including a value on a daylight saving boundary.
End with a warning list of silent errors to check for.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Data: orders with a timestamp column from three stores, stored as local time without zone.
Plan: 1 add a store_tz column; 2 localise: df.groupby("store").apply(lambda g: g["ts"].dt.tz_localize(g.name_tz, ambiguous="NaT", nonexistent="shift_forward")); 3 convert to UTC for storage; 4 convert back only for display.
Traps: daylight saving duplicates at 01:30; midnight boundary for daily totals (group on local date).
Check: the number of orders per UTC day vs per local day should differ only at edges; document the choice.
Test cases: 2025-03-30 02:30 in Europe/Madrid (nonexistent) and 2025-10-26 02:30 (ambiguous).

• datetime