Skip to content

Dates and date arithmetic

Problem

  1. How to extract calendar parts?
  2. How to compare dates?
  3. How to add days?
  4. How to compute day differences?

Notions: Dates and date arithmetic

SQL and Pandas syntax

  • SQLite commonly stores dates as text.
  • PostgreSQL has native date and timestamp types.
SELECT
    DATE(created_at) AS day,
    STRFTIME('%Y-%m', created_at) AS month,
    DATE(created_at, '+1 day') AS next_day,
    JULIANDAY(end_at) - JULIANDAY(start_at) AS days_diff
FROM t
WHERE DATE(created_at) BETWEEN DATE('2024-01-01') AND DATE('2024-01-31');

-- PostgreSQL
SELECT
    created_at::date AS day,
    TO_CHAR(created_at, 'YYYY-MM') AS month,
    created_at::date + INTERVAL '1 day' AS next_day,
    end_at::date - start_at::date AS days_diff
FROM t
WHERE created_at::date BETWEEN DATE '2024-01-01' AND DATE '2024-01-31';

df["created_at"] = pd.to_datetime(df["created_at"])
df["start_at"] = pd.to_datetime(df["start_at"])
df["end_at"] = pd.to_datetime(df["end_at"])

out = df.loc[
    df["created_at"].between("2024-01-01", "2024-01-31")
].copy()

out["day"] = out["created_at"].dt.date
out["month"] = out["created_at"].dt.strftime("%Y-%m")
out["next_day"] = out["created_at"] + pd.Timedelta(days=1)
out["days_diff"] = (out["end_at"] - out["start_at"]).dt.days

Example

import sqlite3
import pandas as pd

## SQL

con = sqlite3.connect(":memory:")

con.executescript("""
CREATE TABLE events (
    id INTEGER,
    created_at TEXT,
    start_at TEXT,
    end_at TEXT
);

INSERT INTO events VALUES
    (1, '2024-01-05', '2024-01-05', '2024-01-08'),
    (2, '2024-01-20', '2024-01-20', '2024-01-21'),
    (3, '2024-02-01', '2024-02-01', '2024-02-04');
""")

sql = """
-- Derive date fields and keep only January 2024 events.
SELECT
    id,
    DATE(created_at) AS day,
    STRFTIME('%Y-%m', created_at) AS month,
    DATE(created_at, '+1 day') AS next_day,
    CAST(JULIANDAY(end_at) - JULIANDAY(start_at) AS INTEGER) AS days_diff
FROM events
WHERE DATE(created_at) BETWEEN DATE('2024-01-01') AND DATE('2024-01-31')
ORDER BY id;
"""

pd.read_sql_query(sql, con)
#    id         day    month    next_day  days_diff
# 0   1  2024-01-05  2024-01  2024-01-06          3
# 1   2  2024-01-20  2024-01  2024-01-21          1

## Pandas

events = pd.DataFrame({
    "id": [1, 2, 3],
    "created_at": ["2024-01-05", "2024-01-20", "2024-02-01"],
    "start_at": ["2024-01-05", "2024-01-20", "2024-02-01"],
    "end_at": ["2024-01-08", "2024-01-21", "2024-02-04"]
})
events.dtypes
# id            int64
# created_at      str
# start_at        str
# end_at          str
# dtype: object

for col in ["created_at", "start_at", "end_at"]:
    events[col] = pd.to_datetime(events[col])
# events.dtypes
# id                     int64
# created_at    datetime64[us]
# start_at      datetime64[us]
# end_at        datetime64[us]
# dtype: object

out = events.loc[
    events["created_at"].between("2024-01-01", "2024-01-31")
].copy()

out["day"] = out["created_at"].dt.date
out["month"] = out["created_at"].dt.strftime("%Y-%m")
out["next_day"] = (out["created_at"] + pd.Timedelta(days=1)).dt.date
out["days_diff"] = (out["end_at"] - out["start_at"]).dt.days

out[["id", "day", "month", "next_day", "days_diff"]]
#    id         day    month    next_day  days_diff
# 0   1  2024-01-05  2024-01  2024-01-06          3
# 1   2  2024-01-20  2024-01  2024-01-21          1
out[["id", "day", "month", "next_day", "days_diff"]].dtypes
# id            int64
# day          object
# month           str
# next_day     object
# days_diff     int64
# dtype: object

Extra

How does sqlite handle/store date and timestamp? What data type they use?

SQLite does not have dedicated DATE or TIMESTAMP storage classes.

  • SQLite uses dynamic typing.
  • Dates and times are usually stored in one of these formats:
    • TEXT
      • ISO-8601 strings like: '2026-07-04 15:30:00'
    • INTEGER
      • Unix time, seconds since 1970-01-01 UTC: 1751643000
    • REAL
      • Julian day number: 2460496.14583

SQLite storage classes are:

  • NULL
  • INTEGER
  • REAL
  • TEXT
  • BLOB

So for date/timestamp, SQLite typically uses:

  • TEXT for readable timestamps
  • INTEGER for Unix epoch timestamps
  • REAL for Julian dates

You can declare columns as DATE or DATETIME, but SQLite treats them as type affinities, not true date/time types.