Dates, times and time zones (IST and UTC)

A timestamp names a moment, and a time zone says how a clock in one place reads that moment. Store every timestamp in UTC, keep the zone attached to the data, and convert to India Standard Time (IST, written Asia/Kolkata in code) only when you show, filter or group for readers in India. IST is UTC plus 5 hours 30 minutes all year, so every moment after 18:30 UTC already falls on the next IST date. A daily count that groups by the UTC date therefore puts late-evening activity on the wrong day. pandas calls a timestamp with a zone aware and one without a zone naive, and most time bugs come from mixing the two, or from treating a naive clock reading as if it named a moment. This lesson parses timestamps the ways logs write them, converts them, counts daily users on both clocks, measures durations, and shows which comparison mistake pandas stops and which one it lets through.

Naive and aware timestamps

pd.to_datetime reads text into the datetime64 type, and what it returns depends on whether the text names a zone. The same moment is written four ways here:

"""One moment written four ways, parsed with pandas and put into one time zone."""
import pandas as pd

with_z = pd.to_datetime(pd.Series(["2026-10-04T18:45:00Z"]))
with_offset = pd.to_datetime(pd.Series(["2026-10-05T00:15:00+05:30"]))
no_zone = pd.to_datetime(pd.Series(["2026-10-04 18:45:00"]))
epoch = pd.to_datetime(pd.Series([1791139500]), unit="s")
print("Z suffix:      ", with_z.dtype)
print("+05:30 offset: ", with_offset.dtype)
print("no zone:       ", no_zone.dtype)
print("epoch seconds: ", epoch.dtype, "->", epoch.iloc[0])
print("epoch, utc=True:", pd.to_datetime(pd.Series([1791139500]), unit="s", utc=True).dtype)

mixed = pd.Series(["2026-10-04T18:45:00Z", "2026-10-05T00:15:00+05:30"])
try:
    pd.to_datetime(mixed)
except ValueError as error:
    print("\nTwo zones in one column:", str(error).split(".")[0])

utc = pd.to_datetime(mixed, utc=True)
print("\nWith utc=True, both rows are the same moment:")
print(utc.to_string())
ist = utc.dt.tz_convert("Asia/Kolkata")
print("\nConverted to IST:")
print(ist.to_string())
print("Zone object:", repr(ist.dt.tz))

Z suffix:       datetime64[us, UTC]
+05:30 offset:  datetime64[us, UTC+05:30]
no zone:        datetime64[us]
epoch seconds:  datetime64[s] -> 2026-10-04 18:45:00
epoch, utc=True: datetime64[s, UTC]

Two zones in one column: Mixed timezones detected

With utc=True, both rows are the same moment:
0   2026-10-04 18:45:00+00:00
1   2026-10-04 18:45:00+00:00

Converted to IST:
0   2026-10-05 00:15:00+05:30
1   2026-10-05 00:15:00+05:30
Zone object: zoneinfo.ZoneInfo(key='Asia/Kolkata')

A Z at the end means UTC, so the first value is aware, with the type datetime64[us, UTC]. pandas 3 parses text to microseconds (us), where pandas 2 used nanoseconds. The second value carries an offset of +05:30, and pandas keeps exactly that: a fixed offset, UTC+05:30, which is not the named zone Asia/Kolkata. The third has no zone at all, so it is naive: pandas knows the clock reading and nothing about where the clock was. Epoch seconds count from the Unix epoch, a moment defined in UTC, but unit="s" still returns a naive value unless you also pass utc=True.

Text with two different zones in one column stops pandas 3 with a ValueError instead of producing a column of mixed objects. utc=True turns every row into a UTC moment, and the output shows that the two strings named the same instant. dt.tz_convert("Asia/Kolkata") then changes the clock reading and nothing else. The zone object is a zoneinfo.ZoneInfo, because pandas 3 uses the standard library’s zoneinfo module for named zones instead of the pytz package, as its release notes say.

From UTC to IST before you group by date

The rule that matters most in practice is the order of two steps: convert first, then take the date or the hour. The examples use 14 logins from a weekend, stored in UTC:

user_id,login_utc
u01,2026-10-03T03:10:00Z
u02,2026-10-03T06:55:00Z
u03,2026-10-03T15:40:00Z
u04,2026-10-03T16:05:00Z
u01,2026-10-03T19:20:00Z
u05,2026-10-03T20:45:00Z
u02,2026-10-04T02:30:00Z
u06,2026-10-04T05:15:00Z
u03,2026-10-04T15:35:00Z
u07,2026-10-04T15:50:00Z
u04,2026-10-04T16:20:00Z
u08,2026-10-04T18:40:00Z
u05,2026-10-04T19:05:00Z
u09,2026-10-04T22:30:00Z

"""Daily active users and the busiest hours, counted in UTC and in IST."""
import pandas as pd

logins = pd.read_csv("logins.csv")
logins["utc"] = pd.to_datetime(logins["login_utc"], utc=True)
logins["ist"] = logins["utc"].dt.tz_convert("Asia/Kolkata")

by_utc_date = logins.groupby(logins["utc"].dt.date)["user_id"].nunique()
by_ist_date = logins.groupby(logins["ist"].dt.date)["user_id"].nunique()
print("Daily active users by UTC date:")
print(by_utc_date.rename_axis("UTC date").to_string())
print("\nDaily active users by IST date:")
print(by_ist_date.rename_axis("IST date").to_string())

moved = logins[logins["utc"].dt.date != logins["ist"].dt.date]
print(f"\n{len(moved)} of {len(logins)} logins fall on the next date in IST:")
print(moved[["user_id", "utc", "ist"]].to_string(index=False))


def busiest(hours, label):
    """The three hours with the most logins (the earlier hour first on a tie)."""
    counts = hours.value_counts().sort_index().sort_values(ascending=False, kind="stable")
    return counts.head(3).rename_axis(label).rename("logins").to_string()


print("\nBusiest hours in IST:")
print(busiest(logins["ist"].dt.hour, "IST hour"))
print("\nBusiest hours in UTC:")
print(busiest(logins["utc"].dt.hour, "UTC hour"))

Daily active users by UTC date:
UTC date
2026-10-03    5
2026-10-04    8

Daily active users by IST date:
IST date
2026-10-03    4
2026-10-04    7
2026-10-05    3

5 of 14 logins fall on the next date in IST:
user_id                       utc                       ist
    u01 2026-10-03 19:20:00+00:00 2026-10-04 00:50:00+05:30
    u05 2026-10-03 20:45:00+00:00 2026-10-04 02:15:00+05:30
    u08 2026-10-04 18:40:00+00:00 2026-10-05 00:10:00+05:30
    u05 2026-10-04 19:05:00+00:00 2026-10-05 00:35:00+05:30
    u09 2026-10-04 22:30:00+00:00 2026-10-05 04:00:00+05:30

Busiest hours in IST:
IST hour
21    5
0     3
8     2

Busiest hours in UTC:
UTC hour
15    3
16    2
19    2

Grouped by the UTC date, the weekend has two days with 5 and 8 users. Grouped by the IST date, the same 14 logins spread over three days with 4, 7 and 3 users, because 5 logins happened after 18:30 UTC. Neither table is wrong as arithmetic, but only the second answers “how many people used the app on Sunday” for a team in India.

Hours show a second effect. The busiest IST hour is 21:00, with 5 logins. In UTC those 5 logins are split between 15:00 and 16:00, because the IST hour from 21:00 to 22:00 runs from 15:30 to 16:30 UTC. An offset with half an hour in it means that an hourly UTC table can never be relabelled into IST hours afterwards: the conversion has to happen row by row, before the grouping.

Fields and formats with the .dt accessor

Once a column has the datetime64 type, the .dt accessor reads its parts in the column’s own zone: dt.hour, dt.day_name(), dt.date and many more. Two of them look alike and behave differently.

"""The .dt accessor: fields to compute with, and text for people to read."""
import pandas as pd

ist = pd.to_datetime(
    pd.Series(["2026-09-28T04:30:00Z", "2026-10-04T15:35:00Z", "2026-10-04T18:40:00Z"]), utc=True
).dt.tz_convert("Asia/Kolkata")

fields = pd.DataFrame({
    "ist": ist,
    "date": ist.dt.date,
    "day_start": ist.dt.floor("D"),
    "hour": ist.dt.hour,
    "weekday": ist.dt.day_name(),
})
print(fields.to_string(index=False))
print("\ndate:", fields["date"].dtype, "| day_start:", fields["day_start"].dtype)

shown = ist.dt.strftime("%d %b %Y, %I:%M %p")
print("\nFor a report:", shown.tolist(), shown.dtype)
print("Sorted as text:   ", sorted(shown))
print("Sorted as moments:", shown[ist.sort_values().index].tolist())

                      ist       date                 day_start  hour weekday
2026-09-28 10:00:00+05:30 2026-09-28 2026-09-28 00:00:00+05:30    10  Monday
2026-10-04 21:05:00+05:30 2026-10-04 2026-10-04 00:00:00+05:30    21  Sunday
2026-10-05 00:10:00+05:30 2026-10-05 2026-10-05 00:00:00+05:30     0  Monday

date: object | day_start: datetime64[us, Asia/Kolkata]

For a report: ['28 Sep 2026, 10:00 AM', '04 Oct 2026, 09:05 PM', '05 Oct 2026, 12:10 AM'] str
Sorted as text:    ['04 Oct 2026, 09:05 PM', '05 Oct 2026, 12:10 AM', '28 Sep 2026, 10:00 AM']
Sorted as moments: ['28 Sep 2026, 10:00 AM', '04 Oct 2026, 09:05 PM', '05 Oct 2026, 12:10 AM']

dt.date returns plain Python date objects, stored with the object type, which is fine as a grouping key but loses the zone and the fast datetime methods. dt.floor("D") keeps the datetime64 type and the zone, so the start of each IST day can still be compared, sorted and converted. dt.strftime turns timestamps into text with the str type, which suits a report and nothing else: sorted as text, the September login lands after the two October ones, because “28” sorts after “04”. Format at the very end, after all filtering, sorting and grouping.

Durations with Timedelta

Subtracting two timestamps gives a Timedelta, an exact length of time. Both columns must be aware, or both naive; here both are UTC, so the session that runs past midnight needs no special care.

"""Session lengths as Timedelta values: in minutes, in parts, and as a check on the data."""
import pandas as pd

sessions = pd.DataFrame({
    "user_id": ["u03", "u07", "u08", "u09"],
    "start": ["2026-10-04T15:35:00Z", "2026-10-04T15:50:00Z", "2026-10-04T18:40:00Z", "2026-10-04T22:30:00Z"],
    "end": ["2026-10-04T16:17:30Z", "2026-10-04T15:50:45Z", "2026-10-04T19:25:00Z", "2026-10-04T22:28:00Z"],
})
for column in ["start", "end"]:
    sessions[column] = pd.to_datetime(sessions[column], utc=True)

sessions["length"] = sessions["end"] - sessions["start"]
sessions["minutes"] = sessions["length"] / pd.Timedelta(minutes=1)
print(sessions[["user_id", "length", "minutes"]].to_string(index=False))
print("\ndtype:", sessions["length"].dtype)
print("parts of the first length:", sessions["length"].dt.components.iloc[0][["hours", "minutes", "seconds"]].tolist())

bad = sessions[sessions["length"] < pd.Timedelta(0)]
print("\nSessions that end before they start:", bad["user_id"].tolist())
good = sessions.drop(bad.index)
print("Median of the others:", good["length"].median())
print("A 30-minute timeout after the first start:", (sessions["start"].iloc[0] + pd.Timedelta(minutes=30)).tz_convert("Asia/Kolkata"))

user_id            length  minutes
    u03   0 days 00:42:30    42.50
    u07   0 days 00:00:45     0.75
    u08   0 days 00:45:00    45.00
    u09 -1 days +23:58:00    -2.00

dtype: timedelta64[us]
parts of the first length: [0, 42, 30]

Sessions that end before they start: ['u09']
Median of the others: 0 days 00:42:30
A 30-minute timeout after the first start: 2026-10-04 21:35:00+05:30

Dividing by pd.Timedelta(minutes=1) turns a length into a plain number of minutes, which charts and averages need, and dt.components splits it into days, hours, minutes and seconds. The last session ends two minutes before it starts, and pandas shows that as -1 days +23:58:00: minus one day plus 23 hours 58 minutes, which is minus 2 minutes. A negative length is a data problem, such as two devices with unsynchronised clocks, so filter it out and count it before you report a median or an average. Adding a Timedelta to a timestamp works the other way, for example to find when a 30-minute session timeout expires.

Daylight saving: the change IST does not have

Many zones move their clocks twice a year, and pandas 3 changed what “one day later” means across such a change. The UK, for example, puts its clocks forward at 01:00 on the last Sunday of March, according to GOV.UK, and the example starts the day before that change:

"""A calendar day against 24 hours, in London (clocks change) and in Kolkata (they never do)."""
import pandas as pd

for zone in ["Europe/London", "Asia/Kolkata"]:
    start = pd.Timestamp("2026-03-28 08:00", tz=zone)
    print(zone)
    print("  start:           ", start)
    print("  + Day(1):        ", start + pd.offsets.Day(1))
    print("  + 24 hours:      ", start + pd.Timedelta(hours=24))
    day = pd.Timestamp("2026-03-30", tz=zone) - pd.Timestamp("2026-03-29", tz=zone)
    print("  length of the day the UK clocks change:", day)

try:
    pd.Timestamp("2026-03-29 01:30").tz_localize("Europe/London")
except ValueError as error:
    print("\nLondon, 01:30 that night:", error)

Europe/London
  start:            2026-03-28 08:00:00+00:00
  + Day(1):         2026-03-29 08:00:00+01:00
  + 24 hours:       2026-03-29 09:00:00+01:00
  length of the day the UK clocks change: 0 days 23:00:00
Asia/Kolkata
  start:            2026-03-28 08:00:00+05:30
  + Day(1):         2026-03-29 08:00:00+05:30
  + 24 hours:       2026-03-29 08:00:00+05:30
  length of the day the UK clocks change: 1 days 00:00:00

London, 01:30 that night: 2026-03-29 01:30:00 is a nonexistent time due to daylight savings time. Try using the 'nonexistent' argument.

In pandas 3, pd.offsets.Day(1) is a calendar day: 08:00 on Saturday plus one day is 08:00 on Sunday, even though only 23 hours passed in London. pd.Timedelta(hours=24) is always 24 hours and lands at 09:00 on the London clock. The pandas 3 release notes describe this change: Day used to mean a fixed 24 hours. A schedule such as “every day at 08:00” needs the calendar day; a measured duration such as “24 hours after the order” needs the Timedelta. A wall-clock time inside the skipped hour, such as 01:30 that night, never happened, and tz_localize raises a ValueError for it unless you choose what to do with the nonexistent argument.

In Asia/Kolkata both additions give the same answer and every day lasts 24 hours. The IANA Time Zone Database, which zoneinfo reads, has kept Asia/Kolkata at UTC+05:30 without any daylight saving rule since 1945, and it records the offsets and rules of every other zone too, so an Indian dataset that meets London or New York times is handled correctly as long as each value carries the right zone name.

Weekday numbers differ between pandas, polars and DuckDB

“Day 1 of the week” depends on the tool. One week, from a Monday to a Sunday, numbered by three engines:

"""One week, numbered by pandas, polars and DuckDB."""
import duckdb
import pandas as pd
import polars as pl

week = pd.DataFrame({"day": pd.date_range("2026-09-28", "2026-10-04", freq="D")})
iso = week["day"].dt.isocalendar()
con = duckdb.connect()
con.execute("SET enable_progress_bar = false")
sql = con.sql("SELECT dayofweek(day) AS dow, isodow(day) AS isodow FROM week ORDER BY day").df()

table = pd.DataFrame({
    "date": week["day"].dt.strftime("%Y-%m-%d"),
    "day": week["day"].dt.day_name().str[:3],
    "pandas dayofweek": week["day"].dt.dayofweek,
    "pandas ISO day": iso["day"].to_numpy(),
    "polars weekday": pl.Series(week["day"].dt.date.tolist()).dt.weekday().to_list(),
    "DuckDB dayofweek": sql["dow"],
    "DuckDB isodow": sql["isodow"],
})
print(table.to_string(index=False))
print("\nISO year, week and day of the Sunday:", iso.iloc[-1].tolist())

      date day  pandas dayofweek  pandas ISO day  polars weekday  DuckDB dayofweek  DuckDB isodow
2026-09-28 Mon                 0               1               1                 1              1
2026-09-29 Tue                 1               2               2                 2              2
2026-09-30 Wed                 2               3               3                 3              3
2026-10-01 Thu                 3               4               4                 4              4
2026-10-02 Fri                 4               5               5                 5              5
2026-10-03 Sat                 5               6               6                 6              6
2026-10-04 Sun                 6               7               7                 0              7

ISO year, week and day of the Sunday: [2026, 40, 7]

pandas’ dt.dayofweek starts from Monday = 0, DuckDB’s dayofweek from Sunday = 0, and polars’ weekday and DuckDB’s isodow use the ISO 8601 numbering, Monday = 1 to Sunday = 7. A filter such as “weekday < 5 means a working day” is right in pandas and wrong in DuckDB, so compare day names in tests, or use the ISO number everywhere. The ISO calendar also numbers weeks: the Sunday of this week is day 7 of ISO week 40, and ISO weeks always start on a Monday.

Comparing naive and aware timestamps

pandas refuses some comparisons between aware and naive values and quietly answers others. Both behaviours are in this example, with the fix:

"""Comparing aware timestamps (with a zone) and naive ones (without): one error, one silent mistake."""
import pandas as pd

logins = pd.to_datetime(pd.Series(["2026-10-04T15:35:00Z", "2026-10-04T18:40:00Z"]), utc=True)
cutoff = pd.Timestamp("2026-10-05 00:00")  # meant as midnight in IST, written without a zone

try:
    logins < cutoff
except TypeError as error:
    print("logins < cutoff ->", type(error).__name__ + ":", error)

same_moment = pd.Timestamp("2026-10-04 15:35")  # the first login's UTC time, without a zone
print("logins == same_moment ->", (logins == same_moment).tolist())

cutoff_ist = cutoff.tz_localize("Asia/Kolkata")
print("\nWith the zone attached:", cutoff_ist)
print("logins < cutoff_ist ->", (logins < cutoff_ist).tolist())
print("logins == same_moment in UTC ->", (logins == same_moment.tz_localize("UTC")).tolist())

logins < cutoff -> TypeError: Invalid comparison between dtype=datetime64[us, UTC] and Timestamp
logins == same_moment -> [False, False]

With the zone attached: 2026-10-05 00:00:00+05:30
logins < cutoff_ist -> [True, False]
logins == same_moment in UTC -> [True, False]

Ordering an aware column against a naive cutoff raises a TypeError, which is the helpful case: the code stops. Testing equality does not raise. Every row compares as False, so a filter written with == silently returns nothing, even for a row whose clock reading matches. The fix is the same for both: say which zone the naive value belongs to with tz_localize, here Asia/Kolkata for a cutoff meant as midnight in India. The 18:40 UTC login is then correctly after midnight IST. A plain string such as "2026-10-05" is read in the column’s own zone, which is convenient when the column is already converted, and a trap when nobody remembers which zone it holds.

Your turn: daily users in IST

The exercise runs in the browser with only the standard library, which is enough here: IST is a fixed offset, so datetime.timezone(timedelta(hours=5, minutes=30)) converts every moment exactly.

from datetime import datetime, timedelta, timezone

IST = timezone(timedelta(hours=5, minutes=30))


def daily_active_users(events):
    """Different users per IST date: {"2026-10-04": 7, ...}, dates in ascending order.

    events is a list of (user_id, moment) pairs, such as ("u01", "2026-10-04T18:45:00Z").
    """
    # Replace this line with your code.
    return {}


def peak_hour(events):
    """The IST hour (0-23) with the most logins; the earlier hour wins a tie."""
    # Replace this line with your code.
    return 0

from daily_users import daily_active_users, peak_hour

WEEKEND = [
    ("u01", "2026-10-03T03:10:00Z"),
    ("u02", "2026-10-03T06:55:00Z"),
    ("u03", "2026-10-03T15:40:00Z"),
    ("u04", "2026-10-03T16:05:00Z"),
    ("u01", "2026-10-03T19:20:00Z"),
    ("u05", "2026-10-03T20:45:00Z"),
    ("u02", "2026-10-04T02:30:00Z"),
    ("u06", "2026-10-04T05:15:00Z"),
    ("u03", "2026-10-04T15:35:00Z"),
    ("u07", "2026-10-04T15:50:00Z"),
    ("u04", "2026-10-04T16:20:00Z"),
    ("u08", "2026-10-04T18:40:00Z"),
    ("u05", "2026-10-04T19:05:00Z"),
    ("u09", "2026-10-04T22:30:00Z"),
]


def test_counts_by_ist_date():
    """counts the users of each IST date, after converting from UTC"""
    assert daily_active_users(WEEKEND) == {"2026-10-03": 4, "2026-10-04": 7, "2026-10-05": 3}


def test_dates_in_ascending_order():
    """returns the dates in ascending order, whatever the order of the events"""
    assert list(daily_active_users(list(reversed(WEEKEND)))) == ["2026-10-03", "2026-10-04", "2026-10-05"]


def test_offsets_and_the_midnight_boundary():
    """reads +05:30 offsets as well as Z, and puts 18:30 UTC on the next IST date"""
    events = [
        ("a", "2026-10-05T00:15:00+05:30"),
        ("a", "2026-10-04T18:45:00Z"),
        ("b", "2026-10-04T18:29:59Z"),
        ("c", "2026-10-04T18:30:00Z"),
    ]
    assert daily_active_users(events) == {"2026-10-04": 1, "2026-10-05": 2}


def test_peak_hour_in_ist():
    """finds the busiest hour on the IST clock, not the UTC one"""
    assert peak_hour(WEEKEND) == 21


def test_peak_hour_tie():
    """returns the earlier hour when two hours have as many logins"""
    events = [
        ("a", "2026-10-04T13:40:00Z"),
        ("b", "2026-10-04T02:31:00Z"),
        ("c", "2026-10-04T13:31:00Z"),
        ("d", "2026-10-04T02:45:00Z"),
    ]
    assert peak_hour(events) == 8

Interview questions

Why should an application store timestamps in UTC and convert them to IST only when it shows or groups them? (warm-up)

A UTC timestamp names one moment without ambiguity: it never jumps for daylight saving, and every server, database and country reads it the same way. A clock reading without its zone could be IST, UTC or London time, and zones with daylight saving skip an hour every spring and repeat one every autumn. Storing UTC keeps arithmetic, sorting and joins correct, and lets each report choose its own zone from the same column: a team in India groups by the IST date, a team in London by the London date. In pandas, parse with utc=True, keep the column aware, and call dt.tz_convert("Asia/Kolkata") just before taking the date or the hour, because the IST date of any moment after 18:30 UTC is the next day.

What is the difference between tz_localize and tz_convert in pandas? (warm-up)

tz_localize attaches a zone to naive timestamps and keeps their clock reading: 09:00 becomes 09:00 IST, which pandas now knows to be 03:30 UTC. tz_convert moves aware timestamps to another zone and keeps the moment: 03:30 UTC becomes 09:00 IST, the same instant on another clock. Localize answers “which clock wrote this?”, convert answers “what does this moment read elsewhere?”. Each refuses the other kind of data: tz_convert on naive values and tz_localize on aware ones raise a TypeError. Localizing can also fail in zones with daylight saving, where a time in the skipped hour does not exist and one in the repeated hour is ambiguous; pass nonexistent= or ambiguous= to decide. IST has no such hours.

Key takeaways

• Keep timestamps aware and in UTC; convert with dt.tz_convert("Asia/Kolkata") just before you take the date or the hour.
• Every moment after 18:30 UTC belongs to the next IST date, and IST’s half-hour offset means hourly UTC tables cannot be relabelled as IST hours.
• tz_localize names the zone of naive clock readings; tz_convert changes the clock of aware moments.
• In pandas 3, Day(1) is a calendar day and Timedelta(hours=24) is 24 hours: they differ where clocks change.
• Weekday numbers differ: Sunday is 6 in pandas, 7 in polars and in ISO numbering, and 0 in DuckDB’s dayofweek.
• Ordering aware against naive values raises a TypeError, but == silently returns False.