> ## Documentation Index
> Fetch the complete documentation index at: https://docs.nekt.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Date parsing and calculations

> Extract date parts, calculate differences, and format dates consistently across SQL engines.

## 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:

1. Extract the **year** and **month** from `order_date`
2. Calculate the **days between** order and shipment
3. Determine **days since the order** (relative to today)

***

## Implementation

<Tabs>
  <Tab title="Nekt Express / BigQuery">
    BigQuery uses `EXTRACT`, `DATE_DIFF`, and `CURRENT_DATE()`.

    ```sql theme={null}
    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`
    ```

    <Tip>
      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.
    </Tip>
  </Tab>

  <Tab title="Athena SQL">
    Athena (Trino) provides `EXTRACT`, `date_diff`, and `current_date` for date operations.

    ```sql theme={null}
    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
    ```

    <Tip>
      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
    </Tip>
  </Tab>

  <Tab title="Python (Nekt SDK)">
    In PySpark, use date functions from `pyspark.sql.functions`.

    ```python theme={null}
    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"
    )
    ```

    <Tip>
      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)
    </Tip>
  </Tab>
</Tabs>

***

## 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

<Warning>
  **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.
</Warning>

<Note>
  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](./handle-null-values) if you want to replace it with a default.
</Note>

<Note>
  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.
</Note>