Secrets of High-Performance Time Intelligence in SQL

The Frame That Changed How I See Data

Mikhail Mikushin

Jun 11, 2026

Time Intelligence in SQL title graphic with dark blue network connections and glowing nodes.

Time intelligence is the center of any analytical system that deals with temporal data.

Today I will tell you about how you can run it in SQL.

Financial reporting:

Quarterly and Yearly financial statements.
Trend reporting.

2. Inventory management:

Stock Movement Analysis.
Lead Time Calculations.

3. Customer Behavior Analysis:

Cohort Analysis.
Churn Prediction.

4. Audit and Compliance:

Historical data tracking.
Change Logging.

Calendar:

Most production data warehouses include a date dimension table. You can build one with a single script that generates one row per day.

Each row carries columns for the date key, year, quarter, month, week number, day of week, fiscal period mappings, holiday flags, and any business-specific attributes.

When you compute year or quarter inline, every query must repeat the same calculations. If the business redefines the fiscal year to start from July to April, you will update the date table once instead of rewriting hundreds of queries.

The SQL Server query optimizer deals with join predicates on integer date keys more efficiently than computed expressions on date columns, because it can rely on statistics collected on the persisted column.

Example: Creating a Calendar Table

CREATE TABLE dbo.DateDimension (
    TheDate DATE PRIMARY KEY,
    Year INT,
    Month INT,
    Day INT,
    Week INT,
    Quarter INT,
    IsWeekend BIT,
    HolidayName NVARCHAR(100)
    -- Add more columns as needed
)

Recursive CTEs are useful when you want to generate date ranges or traverse hierarchical time data.

Example: Generate a Date Range

WITH DateRange AS 
(SELECT 
  CAST(’2023–01–01’ AS DATE) AS Date
 UNION ALL
 SELECT 
  DATEADD(DAY, 1, Date)
 FROM 
  DateRange
 WHERE 
  Date < ‘2023–12–31’)

SELECT 
  Date 
FROM 
  DateRange;

Date Types and Functions:

Date types in SQL Server:

Date.
Time.
Datetime2.
Datetimeoffset.

What to choose:

Use datetime2 for high-precision timestamps, event logs, and scenarios where you need sub-millisecond accuracy but do not require time zone awareness.
Use datetimeoffset when you store timestamps from distributed systems, multi-region applications, or when you need to preserve the original time zone or offset for compliance and audit.
Use date and time for scenarios where only the date or time component is relevant: birthdays or store opening hours.

Date functions in SQL Server:

DATEADD adds a specified time interval (day, hour, minute) to a date.
DATEDIFF computes the difference between two dates in a given unit (years, days, minutes).
DATEPART extracts a part of a date (year, quarter, month, day, weekday).
FORMAT converts a date into a formatted string.
DATE_BUCKET maps each timestamp to the start of its time bucket in SQL Server. Given a time, bucket size, and optional origin, it returns the bucket boundary.
EOMONTH returns the last day of the month containing a given date.
SYSDATETIME() retrieves the current date and time with a local timestamp.
SYSUTCDATETIME() retrieves the current date and time with UTC timestamp.
DATETRUNC truncates a datetime to a specified precision (year, quarter, week).

Timezone conversion:

Time Zone-Aware Data Types and Functions:

Datetimeoffset type and the AT TIME ZONE function in SQL Server support time zone conversions and DST-aware calculations.

datetimeoffset stores both the timestamp and the offset from UTC (e.g., 2026-06-03 09:37:00.1234567 -07:00).
AT TIME ZONE converts a timestamp to a specified time zone and runs DST transitions with Windows time zone rules.

Example:

-- Convert a UTC datetime to Pacific Time, accounting for DST
SELECT
    SYSUTCDATETIME() AS UtcNow,
    SYSUTCDATETIME() AT TIME ZONE ‘Pacific Standard Time’ AS PacificNow;

This query will return the current UTC time and its equivalent in Pacific Time, with the correct offset for DST if applicable.

List all available time zones:

You can get all supported time zones on your SQL Server instance:

SELECT 
  * 
FROM 
  sys.time_zone_info

The view will list all Windows time zones recognized by SQL Server, including their current offsets and DST rules.

Convert Between Time Zones:

When you work with legacy data types, you can use AT TIME ZONE and assign an initial time zone and then convert to another:

-- Assume OrderDate is stored in UTC as datetime2
SELECT
  OrderDate,
  OrderDate AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Eastern Standard Time’ 
  AS EasternTime
FROM 
  Sales.Orders;

Daylight Saving Time (DST) Edge Cases:

AT TIME ZONE function in SQL Server can run DST transitions, including ambiguous and invalid times:

Spring Forward (Gap): Times that do not exist (for example, 2:30 AM on the day clocks move forward) will be mapped to the next valid time with the new offset.
Fall Back (Overlap): Ambiguous times (for example, 1:30 AM occurs twice) are resolved using the pre-change offset.

Example:

-- Time in the DST gap (spring forward)
SELECT
    CONVERT(DATETIME2, ‘2026-03-08T02:30:00’) 
    AT TIME ZONE ‘Pacific Standard Time’;
-- Returns 2026-03-08 03:30:00 -07:00 (skips to the next valid time)

Window functions:

Here are the most popular window functions used in the time intelligence:

LAG. Accesses a value from a previous row in the window.
LEAD. Accesses a value from a subsequent row.
FIRST_VALUE. Returns the first value in the window frame.
LAST VALUE. Returns the last value in the window frame.

Running totals and cumulative aggregations:

Running totals, moving averages, and year-to-date summaries depend on T-SQL window frames.

The frame clause, ROWS or RANGE between … and …, defines which rows the aggregate function includes relative to the current row. The most common source of incorrect cumulative calculations is getting the frame wrong.

SQL Server supports a full OVER clause with framing for every cumulative calculation, so you should use it instead of correlated subqueries.

ROWS vs RANGE:

ROWS counts physical rows.
RANGE groups rows by their ORDER BY value.

If the sort key has duplicates, these functions will not work the same way.

If you have a table of daily sales where multiple transactions share the same date. A frame of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows from the partition start up to and including the current physical row.

A frame of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows with the same ORDER BY value as the current row, even if they appear after it physically.

In SQL Server, RANGE with temporal ORDER BY also supports the INTERVAL syntax.

Running Totals:

You can compute a running sum of sales or measurements over time.

Example:

SELECT
  OrderDate,
  Sales,
  SUM(Sales) OVER(ORDER BY OrderDate 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM 
  SalesOrders

Rolling Averages:

You can calculate moving averages in SQL.

For example, a 7-day rolling average:

SELECT
  Date,
  AVG(Value) OVER(ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
     AS SevenDayRollingAvg
FROM 
  Sales;

Period comparison:

Period over period comparison:

Use LAG() or LEAD() to compare a value with its previous or next period. For example:

SELECT
  Date,
  Value,
  LAG(Value) OVER (ORDER BY Date) AS PrevValue
FROM 
  TimeSeries;

Month-over-Month Change:

SELECT
  Month,
  Revenue,
  LAG(Revenue) OVER (ORDER BY Month) AS PrevMonthRevenue,
  Revenue - LAG(Revenue) OVER (ORDER BY Month) AS MoMChange,
  ROUND(100.0 * (Revenue - LAG(Revenue) OVER (ORDER BY Month)) 
    / LAG(Revenue) OVER (ORDER BY Month), 2) AS MoMChangePct
FROM 
  MonthlyRevenue;

Period-to-date totals:

You can combine Window functions with PARTITION BY to calculate YTD, MTD, and QTD aggregates:

SELECT
  Date,
  SUM(Value) OVER (PARTITION BY YEAR(Date) ORDER BY Date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTD,
  SUM(Value) OVER (PARTITION BY YEAR(Date), MONTH(Date) ORDER BY Date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MTD,
  SUM(Value) OVER (PARTITION BY YEAR(Date), DATEPART(QUARTER, Date) 
    ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QTD
FROM 
  Sales

Filling gaps / Missing periods:

Real data often has gaps. To create a complete timeline, use GENERATE_SERIES or a calendar table to generate all needed time points, then left-join actual data, and fill missing intervals with NULLs or zeros.

Example:

WITH Hours AS
(SELECT 
  DATEADD(hour, h.value, ‘2026-06-01’) AS HourStart
FROM 
  GENERATE_SERIES(0, 23, 1) AS h)

SELECT 
  Hours.HourStart, COALESCE(SUM(Sales),0) AS Sales
FROM 
  Hours
LEFT JOIN 
  SalesData 
  ON SalesData.SaleTime >= Hours.HourStart
  AND SalesData.SaleTime < DATEADD(hour, 1, Hours.HourStart)
GROUP BY 
  Hours.HourStart

Temporal tables:

SQL Server provides system-versioned temporal table functionality for historical analysis.

If you set system versioning on a table, SQL Server will keep old versions of rows in a history table. The current table always keeps the latest data, and every change, update, or delete, will store the old row with its validity interval.

Example: Create a Temporal Table

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Position NVARCHAR(100),
    Department NVARCHAR(100),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

SQL Server moves old versions to the history table on UPDATE or DELETE operations.

Each row has ValidFrom and ValidTo datetime2 columns that store its history.

To query past states, use the FOR SYSTEM_TIME clause.

For example, you can return the state of the Employee table on May 1, 2026.

SELECT 
  * 
FROM 
  dbo.Employee 
FOR 
  SYSTEM_TIME AS OF ‘2026-05-01’

Indexing and Performance:

Clustered indexes on date columns. A clustered index with the date/time column first will order rows physically by time. If possible, make the key (Date, …) so queries on dates will exploit the index.
Partitioning by date. For very large tables, use partitioning. Create range partitions by month or year so older data can be archived or dropped without affecting recent data. The query optimizer will skip entire partitions outside the filter range.
Columnstore indexes. If you run mostly analytics queries (scans and aggregations on recent or historical data), use a clustered columnstore index. It compresses well and will accelerate large scans. Use it for temporal history tables.
Covering indexes and filtered indexes. Use a covering index if you often query specific subsets (a recent timeframe or a particular category). For example, if real-time analytics only need the last 90 days, a filtered index covering that window cuts search costs.
Keep statistics fresh. Time queries might skew toward recent data. Keep histogram stats up to date (automatic stats update usually suffices if queries are frequent).

Best Practices:

Explicit NULL handling. Decide how to deal with gaps. If you need an interpolation, use window functions with IGNORE NULLS. Use ISNULL or COALESCE to replace null measures with 0.
Use appropriate granularity in queries. Aggregate to the necessary level (day, week, etc.) as early as possible. For example, if you only need daily totals, group by CAST(Date AS date) or use DATETRUNC(day, Date).
Store Timestamps in UTC: Always store event timestamps in UTC (datetime2 or datetimeoffset with offset +00:00).
Use datetimeoffset for Time Zone Awareness: Use datetimeoffset when the original time zone is important.
Optimize Query Performance: Use partitioning, aligned indexes, and SARGable predicates. Avoid functions on indexed columns in WHERE clauses.
Avoid OVER with Large Windows: Use partitioning to limit the window size.
Materialize Intermediate Results: Store window function results in temp tables if you will reuse them.
Avoid FORMAT in large queries, and use CONVERT or DATENAME/DATETRUNC instead.
Create and Use Calendar Tables.

Join 10,000+ analysts who follow for SQL insights
What’s your biggest challenge with time-based queries in SQL?
Let me know in the comments 👇

BIWave Substack

Discussion about this post

Ready for more?