Filtering and Aggregating Data with Date and Timestamp Functions

Good to see you again. In the previous lesson, you used CASE to turn business rules into visible categories and conditional metrics. That same discipline matters for time-based reporting: “February sales,” “last 30 days,” “month to date,” and “orders older than seven days” are not interchangeable definitions.

This lesson covers how to filter and aggregate DATE and TIMESTAMP records in Snowflake. You will use DATE_TRUNC, DATEADD, DATEDIFF, and EXTRACT or DATE_PART to build monthly reporting queries, rolling windows, and simple aging analysis. The emphasis is on writing time boundaries that are correct, testable, and safe for dashboard QA.

---

Dates, timestamps, and reporting grain

A DATE contains only a calendar day:

2025-02-14

A TIMESTAMP includes both a day and a time:

2025-02-14 16:42:18

Suppose FCT_ORDER has one row per order:

Column	Meaning
order_id	Unique order identifier
order_date	Calendar date of the order
order_ts	Exact timestamp when the order was created
net_sales	Revenue for the order
order_status	Order lifecycle status

The distinction between order_date and order_ts affects filtering:

• A date filter can safely include a day using an equality condition.
• A timestamp filter must account for every moment within the day, including fractional seconds.

For example, this is safe when order_date is a DATE:

SELECT
    o.order_id,
    o.order_date,
    o.net_sales
FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
WHERE o.order_date = DATE '2025-02-14'
ORDER BY o.order_id;

But an order timestamp such as 2025-02-14 16:42:18 is not equal to DATE '2025-02-14'. You need a time range instead.

Before continuing, watch this short overview. The video uses general SQL examples rather than Snowflake-specific worksheets, but the ideas behind extracting parts, truncating timestamps, and calculating date differences apply directly.

---

DATE_TRUNC: create a real reporting period

DATE_TRUNC changes a date or timestamp to the beginning of a specified period. It does not return a number; it returns a date or timestamp value at a coarser level of detail.

DATE_TRUNC('month', o.order_ts)

If order_ts is:

2025-02-14 16:42:18

then the result is:

2025-02-01 00:00:00

The timestamp now represents the February 2025 reporting period. All orders in February 2025 receive the same truncated value, so they can be grouped together.

SELECT
    CAST(DATE_TRUNC('month', o.order_ts) AS DATE) AS order_month,
    COUNT(*) AS order_count,
    SUM(o.net_sales) AS total_net_sales,
    AVG(o.net_sales) AS average_order_value
FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
GROUP BY order_month
ORDER BY order_month;

This query changes the reporting grain:

• Source grain: one row per order.
• Result grain: one row per month.

The CAST(... AS DATE) is optional from a calculation perspective, but helpful in reporting because it displays the period as 2025-02-01 rather than midnight on that date. Treat that first day as a period key, not as a claim that all sales occurred on the first day.

Snowflake’s documentation is worth reading here because it distinguishes truncating a value from extracting a number from it.

DATE_TRUNC versus EXTRACT

These two expressions answer different questions:

DATE_TRUNC('quarter', o.order_ts)

EXTRACT(quarter FROM o.order_ts)

Expression	Example result	Meaning
DATE_TRUNC('quarter', order_ts)	2025-01-01 00:00:00	The actual quarter period containing the order
EXTRACT(quarter FROM order_ts)	1	The quarter number only

For most time-series reporting, prefer DATE_TRUNC. It creates a complete, sortable period identifier that includes the year.

This query is usually a mistake:

SELECT
    EXTRACT(month FROM o.order_ts) AS month_number,
    SUM(o.net_sales) AS total_net_sales
FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
GROUP BY month_number;

It groups every January together, regardless of year. January 2024 and January 2025 would be combined into one row.

If the business genuinely wants analysis by month number across all years, that may be appropriate. But for a monthly KPI trend, use DATE_TRUNC('month', ...), or group by both year and month:

SELECT
    EXTRACT(year FROM o.order_ts) AS order_year,
    EXTRACT(month FROM o.order_ts) AS order_month_number,
    SUM(o.net_sales) AS total_net_sales
FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
GROUP BY
    order_year,
    order_month_number
ORDER BY
    order_year,
    order_month_number;

---

Filter dates with explicit period boundaries

A correct calendar-month filter states a beginning and an end. For a DATE column, February 2025 can be written as:

SELECT
    o.order_id,
    o.order_date,
    o.net_sales
FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
WHERE o.order_date >= DATE '2025-02-01'
  AND o.order_date < DATE '2025-03-01'
ORDER BY o.order_date, o.order_id;

The start is included; the end is excluded.

This same pattern is especially important for a timestamp column:

SELECT
    o.order_id,
    o.order_ts,
    o.net_sales
FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
WHERE o.order_ts >= TO_TIMESTAMP_NTZ('2025-02-01 00:00:00')
  AND o.order_ts < TO_TIMESTAMP_NTZ('2025-03-01 00:00:00')
ORDER BY o.order_ts, o.order_id;

This is called a half-open interval. It includes every timestamp in February, beginning at midnight on February 1, but excludes precisely midnight on March 1.

Why not use BETWEEN for timestamp months?

This query looks reasonable but is risky:

WHERE o.order_ts BETWEEN
    TO_TIMESTAMP_NTZ('2025-02-01 00:00:00')
    AND TO_TIMESTAMP_NTZ('2025-02-28 23:59:59')

BETWEEN includes both boundaries. The problem is that a timestamp may contain fractional seconds:

2025-02-28 23:59:59.750

That valid February order is later than 23:59:59, so it would be excluded.

Trying to solve this by guessing the largest possible fractional second is fragile. A half-open interval needs no guesswork:

WHERE o.order_ts >= TO_TIMESTAMP_NTZ('2025-02-01 00:00:00')
  AND o.order_ts < TO_TIMESTAMP_NTZ('2025-03-01 00:00:00')

This pattern should become a habit for report filters, dashboard validation queries, and AI-generated SQL reviews.

Filter a dynamic reporting period with DATEADD

Hard-coded periods are useful for reproducible QA. Operational reports often need relative periods based on the current date.

For example, the following query returns the two most recently completed calendar months, excluding the current partial month:

SELECT
    CAST(DATE_TRUNC('month', o.order_date) AS DATE) AS order_month,
    COUNT(*) AS order_count,
    SUM(o.net_sales) AS total_net_sales
FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
WHERE o.order_date >= DATEADD(
          'month',
          -2,
          DATE_TRUNC('month', CURRENT_DATE())
      )
  AND o.order_date < DATE_TRUNC('month', CURRENT_DATE())
GROUP BY order_month
ORDER BY order_month;

If today is June 18, this includes:

• April 1 through April 30
• May 1 through May 31

It deliberately excludes June because June is incomplete.

A rolling 30-day window means something different. It is based on the current moment, not calendar-month boundaries:

SELECT
    COUNT(*) AS orders_last_30_days,
    SUM(o.net_sales) AS net_sales_last_30_days
FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
WHERE o.order_ts >= DATEADD('day', -30, CURRENT_TIMESTAMP())
  AND o.order_ts < CURRENT_TIMESTAMP();

Be precise in specifications:

Phrase	Possible definition
“February sales”	February 1 through March 1, using a half-open range
“Month to date”	First day of current month through today
“Previous month”	Entire calendar month before the current one
“Last 30 days”	Rolling 30-day period ending now
“Last 30 completed days”	Usually 30 full calendar days ending yesterday

A stakeholder may use these phrases casually, but a report cannot. Your SQL must implement one documented definition.

---

Use DATE_PART and EXTRACT for segmentation

EXTRACT and DATE_PART retrieve a numeric component from a date or timestamp. They are useful for segmenting a valid time range after you have defined it.

For example, an analyst investigating an unusual daily sales pattern may want orders by hour:

SELECT
    EXTRACT(hour FROM o.order_ts) AS order_hour,
    COUNT(*) AS order_count,
    SUM(o.net_sales) AS total_net_sales
FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
WHERE o.order_ts >= TO_TIMESTAMP_NTZ('2025-02-01 00:00:00')
  AND o.order_ts < TO_TIMESTAMP_NTZ('2025-03-01 00:00:00')
GROUP BY order_hour
ORDER BY order_hour;

This creates one row for each hour from 0 through 23. It can help identify:

• A sudden overnight spike in transactions
• An ETL job loading duplicate orders at a specific hour
• A reporting cutoff that is occurring in the wrong time zone
• A period with unexpectedly low transaction volume

For quarterly reporting, use a complete period key when the report spans multiple years:

SELECT
    CAST(DATE_TRUNC('quarter', o.order_date) AS DATE) AS order_quarter,
    SUM(o.net_sales) AS total_net_sales
FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
GROUP BY order_quarter
ORDER BY order_quarter;

Avoid using only this when there is more than one year:

EXTRACT(quarter FROM o.order_date)

It produces 1, 2, 3, or 4, but does not distinguish the first quarter of 2024 from the first quarter of 2025.

One Snowflake-specific caution: week-based truncation can depend on the session’s WEEK_START parameter. For weekly KPIs, document whether a week begins on Sunday, Monday, or another day. Otherwise, two analysts can produce different weekly totals from the same data.

---

DATEDIFF: calculate age and elapsed reporting intervals

DATEDIFF calculates the difference between two dates or timestamps in a chosen unit.

DATEDIFF('day', o.order_date, CURRENT_DATE())

This returns the number of day units between an order date and today. You can use it for aging analysis, service-level reporting, or QA checks for unusually old records.

The following query continues the CASE patterns from the previous lesson and creates explicit order-age categories:

SELECT
    CASE
        WHEN o.order_date IS NULL THEN 'Missing order date'
        WHEN DATEDIFF('day', o.order_date, CURRENT_DATE()) < 0
            THEN 'Future date review'
        WHEN DATEDIFF('day', o.order_date, CURRENT_DATE()) <= 7
            THEN '0-7 days'
        WHEN DATEDIFF('day', o.order_date, CURRENT_DATE()) <= 30
            THEN '8-30 days'
        ELSE 'Over 30 days'
    END AS order_age_band,

    COUNT(*) AS order_count,
    SUM(o.net_sales) AS total_net_sales

FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
GROUP BY order_age_band
ORDER BY order_age_band;

The explicit NULL and future-date branches matter. Without them, problematic records could silently enter an ordinary age band or disappear from an aggregate.

For a delivery-duration KPI, compare two event dates:

SELECT
    o.order_id,
    o.order_date,
    o.ship_date,
    DATEDIFF('day', o.order_date, o.ship_date) AS days_to_ship
FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
WHERE o.order_status = 'SHIPPED'
ORDER BY days_to_ship DESC, o.order_id;

Before publishing such a measure, confirm the business definition:

• Does the count begin on the order date or the next business day?
• Are weekends and holidays included?
• Should cancelled orders be excluded?
• What should happen when ship_date is NULL?
• Are the dates recorded in one business time zone?

The SQL function calculates a date difference. The business rules determine whether that difference is the KPI the organization actually needs.

---

A repeatable monthly KPI pattern

Here is a complete monthly sales report for completed orders in a fixed, auditable period.

SELECT
    CAST(DATE_TRUNC('month', o.order_ts) AS DATE) AS order_month,

    COUNT(*) AS completed_order_count,

    SUM(o.net_sales) AS completed_net_sales,

    AVG(o.net_sales) AS average_completed_order_value

FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o

WHERE o.order_status = 'COMPLETED'
  AND o.order_ts >= TO_TIMESTAMP_NTZ('2025-01-01 00:00:00')
  AND o.order_ts < TO_TIMESTAMP_NTZ('2025-04-01 00:00:00')

GROUP BY order_month
ORDER BY order_month;

Read it in the order Snowflake logically applies it:

1. FROM identifies the source rows.
2. WHERE retains completed orders in the three-month interval.
3. GROUP BY creates one group for each calendar month.
4. COUNT, SUM, and AVG calculate measures within each group.
5. ORDER BY places months in chronological order.

For QA, the most important part is not the aggregation itself. It is confirming that the filters match the stated definition.

If the monthly visual says its total completed sales from January through March is $125,000$, independently validate it with a direct total query using the same population:

SELECT
    SUM(o.net_sales) AS independently_calculated_completed_net_sales
FROM PORTFOLIO_DB.RAW.FCT_ORDER AS o
WHERE o.order_status = 'COMPLETED'
  AND o.order_ts >= TO_TIMESTAMP_NTZ('2025-01-01 00:00:00')
  AND o.order_ts < TO_TIMESTAMP_NTZ('2025-04-01 00:00:00');

The direct total should equal the sum of the monthly report rows. If it does not, investigate:

• Different status filters
• An omitted or duplicated month
• Incorrect timestamp boundaries
• A join added in one query but not the other
• A visual-level filter in Power BI
• A different interpretation of the reporting time zone

For formal testing, prefer fixed date boundaries like those above. CURRENT_DATE() and CURRENT_TIMESTAMP() are useful in live reporting, but their results naturally change over time. A defect report or portfolio QA record should document the exact reporting window used.

---

Key takeaways

Time-based SQL is fundamentally about defining the correct reporting period and applying it consistently.

• Use DATE_TRUNC to create a true monthly, quarterly, weekly, daily, or hourly period key.
• Use EXTRACT or DATE_PART to retrieve numeric parts such as hour, month number, quarter, or year.
• Do not group by month number alone when data spans multiple years.
• For timestamp periods, use a half-open range: include the start and exclude the next period’s start.
• Use DATEADD to construct dynamic time windows such as previous month or rolling 30 days.
• Use DATEDIFF for age and duration metrics, then apply explicit business rules with CASE.
• Validate dashboard time periods with an independently written query using the same documented boundaries.

Next, you will learn how to verify a reported total against an independently written SQL query. That will combine the SQL foundations you have covered so far—filters, joins, CASE, aggregation, and time boundaries—into a practical QA workflow.