Working with Dates

Dates are frequently an issue when you are dealing with large data sets. Most often used for timespans to limit or group reporting, working with dates can more difficult than other datatypes. Date spans like the examples provided below are most often used for summarization of historical data to look for trends, outliers, or anomalies. We THWACKsters frequently get asked for assistance in building these date spans, so we felt it made sense to collect the most common requests in one area.

Assumptions: We are assuming all dates are stored in the ObservationTimestamp property and are stored in the UTC time zone.

Examples of Common Date Spans

Current UTC Date/Time	Description	WHERE Clause	Range Start	Range End
2023-03-15 11:33:00	Last 15 minutes	`[ObservationTimestamp] >= ADDMINUTE( -15, GETUTCDATE() )`	2023-03-15 11:18:00
2023-03-15 11:33:00	Last 1 hour	`[ObservationTimestamp] >= ADDHOUR( -1, GETUTCDATE() )`	2023-03-15 10:33:00
2023-03-15 11:33:00	Last 1 day	`[ObservationTimestamp] >= ADDDAY( -1, GETUTCDATE() )`	2023-03-14 11:33:00
2023-03-15 11:33:00	Last 1 week	`[ObservationTimestamp] >= ADDWEEK( -1, GETUTCDATE() )`	2023-03-06 11:33:00
2023-03-15 11:33:00	Last 1 month	`[ObservationTimestamp] >= ADDMONTH( -1, GETUTCDATE() )`	2023-02-15 11:33:00
2023-03-15 11:33:00	Last 1 year	`[ObservationTimestamp] >= ADDYEAR( -1, GETUTCDATE() )`	2022-03-15 11:33:00
2023-03-15 11:33:00	Today	`[ObservationTimestamp] >= DATETRUNC('day', GETUTCDATE() )`	2023-03-15 00:00:00
2023-03-15 11:33:00	Yesterday	`[ObservationTimestamp] BETWEEN DATETRUNC('day', ADDDAY(-1, GETUTCDATE() ) ) AND DATETRUNC('day', GETUTCDATE() )`	2023-03-14 00:00:00	2023-03-15 00:00:00
2023-03-15 11:33:00	This week	`[ObservationTimestamp] >= DATETRUNC('week', GETUTCDATE() )`	2023-03-12 11:33:00
2023-03-15 11:33:00	Last week	`[ObservationTimestamp] BETWEEN DATETRUNC('week', ADDWEEK(-1, GETUTCDATE() ) ) AND DATETRUNC('week', GETUTCDATE() )`	2023-03-15 00:00:00	2023-03-12 00:00:00
2023-03-15 11:33:00	This month	`[ObservationTimestamp] >= DATETRUNC('month', GETUTCDATE() )`	2023-03-01 00:00:00
2023-03-15 11:33:00	Last month	`[ObservationTimestamp] BETWEEN DATETRUNC('month', ADDMONTH(-1, GETUTCDATE() ) ) AND DATETRUNC('month', GETUTCDATE() )`	2023-02-01 00:00:00	2023-03-01 00:00:00
2023-03-15 11:33:00	This quarter	`[ObservationTimestamp] >= DATETRUNC('quarter', GETUTCDATE() )`	2023-01-01 00:00:00
2023-03-15 11:33:00	Last quarter	`[ObservationTimestamp] BETWEEN DATETRUNC('quarter', ADDQUARTER-1, GETUTCDATE() ) ) AND DATETRUNC('quarter', GETUTCDATE() )`	2023-01-01 00:00:00	2023-03-01 00:00:00
2023-03-15 11:33:00	This year	`[ObservationTimestamp] >= DATETRUNC('year', GETUTCDATE() )`	2023-01-01 00:00:00
2023-03-15 11:33:00	Last year	`[ObservationTimestamp] BETWEEN DATETRUNC('year', ADDYEAR(-1, GETUTCDATE() ) ) AND DATETRUNC('year', GETUTCDATE() )`	2022-01-01 00:00:00	2023-01-01 00:00:00

Summarizing Date-bound Information

One of the best functions for summarizing among date/time spans is DOWNSAMPLE.

Suppose we want the CPU load for all of my nodes for the last 24 hours (1 day). We would start with a query to bring in all the information.

SELECT [Nodes].Caption
     , [Nodes].CPULoadHistory.AvgLoad
     , [Nodes].CPULoadHistory.ObservationTimestamp
FROM Orion.Nodes AS [Nodes]
-- Only show me information for the last 24 hours (1 day)
WHERE [Nodes].CPULoadHistory.ObservationTimestamp >= ADDDAY(-1, GETUTCDATE())

-- Returns 1,706 rows in a very small lab

Even in a small lab environment, this will return many thousands of records. What we really want is a summarization (average) of these values for each hour of the day.

SELECT [Nodes].Caption
     , AVG([Nodes].CPULoadHistory.AvgLoad) AS [AvgLoad]
     , DOWNSAMPLE([Nodes].CPULoadHistory.ObservationTimestamp, '01:00:00') AS [HourOfDay]
FROM Orion.Nodes AS [Nodes]
-- Only show me information for the last 24 hours (1 day)
WHERE [Nodes].CPULoadHistory.ObservationTimestamp >= ADDDAY(-1, GETUTCDATE())
GROUP BY [Nodes].Caption
       , DOWNSAMPLE([Nodes].CPULoadHistory.ObservationTimestamp, '01:00:00')
--
-- Returns 295 records
--

This returns significantly fewer rows but still provides us with the data we need.

The DOWNSAMPLE function can work with any aggregation function (average, sum, count, min, max).

Recommended