How do I convert UTC to Mexico City time?

The server's clock isn't the shop's clock

Online systems usually store time in UTC, Coordinated Universal Time: a reference clock that doesn't change with the country or the season. That way a system with customers in several time zones has one clock for everyone. That's why the column is called creado_utc.

For Café Altura, the clock that matters is Mexico City's, where its three shops are. If you're used to US time zones, here's the picture for 2026: Mexico City is six hours behind UTC all year. In January that's the same clock as Chicago (Central Standard Time), one hour behind New York. In July, Chicago and New York have moved their clocks forward and Mexico City hasn't, so Mexico City matches Denver and is one hour behind Chicago and two behind New York. (We checked this against the time zone database in the course sandbox, for 18:00 UTC on January 15 and July 15, 2026.)

The first order was created at 2026-02-01 02:44:40 UTC. What time and what day was it in the city?

Two conversions from UTC to the America/Mexico_City time zone. Write what you think each one prints and why.

The first order

Example 1 of 2

primer_pedido.py

import pandas as pd

utc = pd.Series(pd.to_datetime(["2026-02-01 02:44:40"], utc=True))
print(utc.dt.tz_convert("America/Mexico_City"))

What date and time do you think it was in Mexico City?

Why?

A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.

Example 2 of 2, opens once you explain this one

Make your prediction

Why the offset changed

On October 28, 2022, Mexico published the Ley de los Husos Horarios en los Estados Unidos Mexicanos (its federal time zone law). It took effect on October 30, 2022 and repealed the 2001 Ley del Sistema de Horario. Article 3, section I, defines the Central Zone, tied to the meridian 90 degrees west of Greenwich, as the zone that covers most of the country. The sections that follow carve out the Pacific, Northwest, and Southeast zones and the islands, and none of them includes Mexico City. Article 4 says: "Únicamente se aplicará un horario estacional para los estados y municipios de la frontera norte" (our unofficial translation: a seasonal schedule will apply only to the states and municipalities of the northern border). Article 5 lists those border municipalities and sets their seasonal schedule from 2 a.m. on the second Sunday of March to 2 a.m. on the first Sunday of November. Mexico City isn't among them.

That's why the city no longer changes its clocks in summer. The law doesn't say "six hours behind UTC"; that comes from the time zone database, in the conversion you just ran, which gives six hours behind UTC in both February and July 2026.

Which leads to the practical rule: convert with the zone name, not with a number. America/Mexico_City knows what the offset was on every date. Subtracting a fixed 6 hours gives the wrong time for any summer date before the law. If your data mixes dates from before and after 2022, work out with your Maestro how to convert them.

Ley de los Husos Horarios en los Estados Unidos Mexicanos (Chamber of Deputies, current text, in Spanish)

In pedidos, creado_utc already knows it's in UTC (its type is datetime64[us, UTC]). tz_convert moves it to another zone:

Python

data["creado_local"] = data["creado_utc"].dt.tz_convert("America/Mexico_City")

If your column has no time zone, first tell it which one it's in with .dt.tz_localize("UTC").

In DuckDB, creado_utc is a TIMESTAMP with no time zone. The first AT TIME ZONE says that time is in UTC; the second moves it to the city's time:

SQL

SELECT
  creado_utc,
  creado_utc AT TIME ZONE 'UTC' AT TIME ZONE 'America/Mexico_City' AS creado_local
FROM pedidos;

AT TIME ZONE and TIMESTAMPTZ functions (DuckDB documentation)

Series.dt.tz_convert (pandas documentation)

What changes in a report

A six-hour shift seems minor until you count by month. In UTC, pedidos has 145 orders in February and 159 in March. In Mexico City time it has 2 in January, 144 in February, and 158 in March: orders placed in the last hours of each month, city time, land in the next month in UTC.

The hour of the day changes even more. The chart below counts orders by hour using the time as stored, in UTC: it looks like people order coffee at one in the morning and nobody orders between 6 and 12.

Try it

Convert creado_utc to America/Mexico_City before taking the hour, and watch the chart shift. You're done when orders start at 7 in the morning and there are none from midnight to 6. If the chart doesn't change, share your code with your Maestro.

Your turn

The chart counts orders by hour of the day using UTC. Change the line that calculates hora so it uses Mexico City time (America/Mexico_City).

Your code (Python)

The chart redraws when you pause typing, or right away with Run. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your code.

What the chart shows

Bars showing the number of orders by hour of the day, from 0 to 23. With the time in UTC, orders appear from midnight to 5 and from 13 to 23, and there are none from 6 to 12.

Data: pedidos-en-linea, 304 rows

Enter saves a cell and Escape leaves it as it was. Leave a cell empty for a missing value.

The exercise's data, "pedidos-en-linea". Your edits run with your code.
Row				Remove
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Page 1 of 16

Run it yourself

Try it in DuckDB

Now the monthly report. The query counts orders and adds up the total by month, with the time in UTC. Convert the time before you take the month. strftime does the opposite of strptime: it writes a date as text using a format, and here '%Y-%m' keeps only the year and the month. A good answer has three rows: January with 2 orders, February with 144, and March with 158.

Your turn

Count the orders and add up the total by month, with the month in Mexico City time.

Tables: pedidos (pedido_id, creado_utc, total)

pedidos · 304 rows

Column	Type
pedido_id	BIGINT
creado_utc Cuándo se hizo el pedido, en UTC, como lo guarda la tienda en línea.	TIMESTAMP
total Total del pedido en pesos.	BIGINT

First rows

pedido_id	creado_utc	total
5001	2026-02-01T02:44:40Z	89
5002	2026-02-01T02:54:59Z	420
5003	2026-02-01T15:15:38Z	89

Your query (SQL)

Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your query.