When to use this
Date operations are at the heart of most analytics — calculating how many days since a customer’s last purchase, extracting the month from a timestamp for grouping, or formatting dates for a report. Each SQL engine handles dates slightly differently, so this recipe covers the most common operations side by side.
An orders table in the Raw layer:
| order_id | customer_id | order_date | shipped_date |
|---|
| 1001 | 201 | 2024-01-15 10:30:00 | 2024-01-18 14:00:00 |
| 1002 | 202 | 2024-02-20 09:00:00 | 2024-02-22 11:30:00 |
| 1003 | 201 | 2024-03-05 16:45:00 | NULL |
- Extract the year and month from
order_date
- Calculate the days between order and shipment
- Determine days since the order (relative to today)
Implementation
Nekt Express / BigQuery
Athena SQL
Python (Nekt SDK)
BigQuery uses EXTRACT, DATE_DIFF, and CURRENT_DATE().SELECT
order_id,
customer_id,
order_date,
shipped_date,
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month,
DATE_DIFF(CAST(shipped_date AS DATE), CAST(order_date AS DATE), DAY) AS days_to_ship,
DATE_DIFF(CURRENT_DATE(), CAST(order_date AS DATE), DAY) AS days_since_order
FROM `raw.orders`
Note the argument order difference: BigQuery’s DATE_DIFF takes (end, start, part) while Athena’s date_diff takes (part, start, end). This is a common source of bugs when porting queries.
Athena (Trino) provides EXTRACT, date_diff, and current_date for date operations.SELECT
order_id,
customer_id,
order_date,
shipped_date,
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month,
date_diff('day', order_date, shipped_date) AS days_to_ship,
date_diff('day', CAST(order_date AS DATE), current_date) AS days_since_order
FROM raw.orders
Other useful Athena date functions:
date_add('day', 30, order_date) — add 30 daysdate_trunc('month', order_date) — first day of the monthformat_datetime(order_date, 'yyyy-MM-dd') — format as string
In PySpark, use date functions from pyspark.sql.functions.import nekt
from pyspark.sql import functions as F
df = nekt.load_table(layer_name="Raw", table_name="orders")
result_df = (
df
.withColumn("order_year", F.year("order_date"))
.withColumn("order_month", F.month("order_date"))
.withColumn("days_to_ship",
F.datediff(F.col("shipped_date"), F.col("order_date"))
)
.withColumn("days_since_order",
F.datediff(F.current_date(), F.to_date("order_date"))
)
)
nekt.save_table(
df=result_df,
layer_name="Trusted",
table_name="orders_with_dates"
)
Other useful PySpark date functions:
F.date_add("order_date", 30) — add 30 daysF.date_trunc("month", "order_date") — first day of the monthF.date_format("order_date", "yyyy-MM-dd") — format as stringF.dayofweek("order_date") — day of the week (1 = Sunday)
Expected output
Assuming today is 2024-12-01:
| order_id | customer_id | order_date | shipped_date | order_year | order_month | days_to_ship | days_since_order |
|---|
| 1001 | 201 | 2024-01-15 10:30:00 | 2024-01-18 14:00:00 | 2024 | 1 | 3 | 321 |
| 1002 | 202 | 2024-02-20 09:00:00 | 2024-02-22 11:30:00 | 2024 | 2 | 2 | 285 |
| 1003 | 201 | 2024-03-05 16:45:00 | NULL | 2024 | 3 | NULL | 271 |
Tips and gotchas
Argument order differs between engines. This is the most common source of date calculation bugs:
- Athena:
date_diff('day', start, end) — unit first, then start, then end
- BigQuery:
DATE_DIFF(end, start, DAY) — end first, then start, then unit
- PySpark:
F.datediff(end, start) — always returns days, end first
Getting the order wrong produces negative numbers instead of positive ones. When shipped_date is NULL (e.g., order 1003 hasn’t shipped yet), all date difference calculations involving that column return NULL. This is the correct behavior — use Handle NULL values if you want to replace it with a default. Timestamps include both date and time. When calculating day differences, most engines ignore the time component and count calendar days. If you need hour-level or minute-level precision, use the appropriate unit ('hour', 'minute') in SQL or F.unix_timestamp differences in PySpark.
