Date parsing and calculations

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.

Sample input

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

We want to:

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 days
date_trunc('month', order_date) — first day of the month
format_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 days
F.date_trunc("month", "order_date") — first day of the month
F.date_format("order_date", "yyyy-MM-dd") — format as string
F.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.

​When to use this

​Sample input

​Implementation

​Expected output

​Tips and gotchas

When to use this

Sample input

Implementation

Expected output

Tips and gotchas