Why does BETWEEN miss the last day? Filtering dates that have a time

The online orders

Café Altura also sells online. The online store keeps each order in the pedidos (orders) table: a pedido_id, the moment it was created (creado_utc, created in UTC), and the total in pesos. There are 304 synthetic orders from February and March 2026.

This column is already a date, not text, but it carries something extra: the time. The first order was created at 2026-02-01 02:44:40. A date with a time is called a timestamp, and in DuckDB its type is TIMESTAMP.

The column name says utc: those times are in UTC, not Mexico City time. You'll deal with that in the next lesson. The problem on this page is a different one, and it would show up even if everything were in your local time. Make a prediction first.

Four orders from the end of March, with times, and two ways to keep only the March ones. Write what you think each program prints and why.

March with <= '2026-03-31'

Example 1 of 2

cerrado.py

import pandas as pd

pedidos = pd.Series(pd.to_datetime([
    "2026-03-30 18:05",
    "2026-03-31 00:00",
    "2026-03-31 09:15",
    "2026-03-31 21:40",
]))
de_marzo = pedidos[(pedidos >= "2026-03-01") & (pedidos <= "2026-03-31")]
print(len(de_marzo))

How many orders do you think it counts as March?

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

A date on its own means midnight

When you compare a timestamp with a date that has no time, the date stands for its very first instant: '2026-03-31' is 2026-03-31 00:00:00. That explains three common mistakes:

BETWEEN '2026-03-01' AND '2026-03-31' includes both ends, but the upper end is midnight at the start of the 31st. In pedidos it counts 154 orders and 33,469 pesos. March actually had 159 orders and 34,630 pesos in UTC.
creado_utc = '2026-03-31' looks for orders created at exactly 00:00:00. There were 5 orders that day, and the filter finds 0.
<= '2026-03-31 23:59:59' looks like a fix, but if your data stores fractions of a second, it drops anything after the last whole second.

If you can't see why BETWEEN misses the 31st, ask your Maestro in your own words.

The half-open interval

The version that never fails is a half-open interval: from the first instant of the period, included, up to the first instant of the next period, not included.

creado_utc >= '2026-03-01' AND creado_utc < '2026-04-01'

It works the same way for one day (>= '2026-03-31' AND < '2026-04-01'), a week, or a year, and it doesn't care how many days the month has or how many decimals the time carries.

Compare the column with two date strings and join the conditions with &, each one in parentheses:

Python

marzo = data[(data["creado_utc"] >= "2026-03-01") & (data["creado_utc"] < "2026-04-01")]

Series.between("2026-03-01", "2026-03-31") has the same problem as BETWEEN: in pedidos it counts 154.

SQL

SELECT count(*) AS pedidos, sum(total) AS total
FROM pedidos
WHERE creado_utc >= '2026-03-01'
  AND creado_utc < '2026-04-01';

Another option that works for whole days is to strip the time from the column before comparing: creado_utc::DATE BETWEEN '2026-03-01' AND '2026-03-31' also counts 159.

BETWEEN and comparison operators (DuckDB documentation)

Try it

The query below uses BETWEEN and loses orders. Change it to a half-open interval. A good answer has one row: 159 orders and 34,630 pesos.

Your turn

Count the March 2026 orders (in UTC) and add up their total without losing any order from the 31st.

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.

Run it yourself

Try it on your own

Now in pandas: count the orders from March 31, 2026, in UTC, and add up their total. Use a one-day half-open interval. A good answer counts 5 orders. If you get 0, share your code with your Maestro.

Count the orders from March 31, 2026 (in UTC) and add up their total, using a half-open interval.

Your code (Python)

Run your code to see what it prints and the value of its last expression. Every run starts fresh. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your code.

Run your code to see its output here.

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