{"id":49118,"date":"2023-01-12T08:00:00","date_gmt":"2023-01-12T16:00:00","guid":{"rendered":""},"modified":"2024-01-31T13:40:39","modified_gmt":"2024-01-31T21:40:39","slug":"working-with-time-series-data-in-sql-server-2022-and-azure-sql","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2023\/01\/12\/working-with-time-series-data-in-sql-server-2022-and-azure-sql\/","title":{"rendered":"Working with time series data in SQL Server 2022 and Azure SQL"},"content":{"rendered":"\n

Part of the <\/em>SQL Server 2022 blog series<\/em><\/a>.<\/p>\n\n\n\n

Time series data is a set of values organized in the order in which they occur and arrive for processing. Unlike transactional data in SQL Server<\/a>, which is not time-based and may be updated often, time series data is typically written once and rarely, if ever, updated.<\/p>\n\n\n\n

Some examples of time series data include stock prices, telemetry from equipment sensors on a manufacturing floor, and performance metrics from SQL Server, such as CPU, memory, I\/O, and network utilization.<\/p>\n\n\n\n

Time series data is often used for historical comparisons, anomaly detection and alerting, predictive analysis, and reporting, where time is a meaningful axis for viewing or analyzing data.<\/p>\n\n\n\n

Time series capabilities in SQL Server were introduced in Azure SQL Edge<\/a>, Microsoft\u2019s version of SQL Server for the Internet of Things (IoT) which combines capabilities such as data streaming and time series with built-in machine learning and graph features.<\/p>\n\n\n

\n\t
\n\t\t
\n\n\t\t\t\t\t\t\t
\n\t\t\t\t\t\"\"\t\t\t\t<\/div>\n\t\t\t\n\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\n\t\t\t\t\t

SQL Server 2022<\/h2>\n\n\t\t\t\t\t
\n\t\t\t\t\t\t

The most Azure-enabled release yet, with continued performance, security, and availability innovation.<\/p>\n\t\t\t\t\t<\/div>\n\n\t\t\t\t\t\t\t\t\t\t\t

\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\tLearn more<\/span>\n\t\t\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t\t<\/a>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t<\/div>\n\n\t\t\t\t\t<\/div>\n\t<\/div>\n<\/div>\n\n\n\n

With SQL Server 2022 and Azure SQL, we\u2019ve brought time series capabilities to the entire SQL Server family. Time series capabilities in SQL Server consist of enhancements to existing T-SQL functions for handling NULL<\/code> values, plus two new functions that make working with temporal-based data easier than ever.<\/p>\n\n\n\n

Create contiguous ranges with GENERATE_SERIES<\/h2>\n\n\n\n

When analyzing time series data, it\u2019s common to create a contiguous set of datetime<\/strong> values in evenly spaced intervals (for example, every second) within a specific range. One way to accomplish this is by creating a numbers table, also known as a tally table, which contains a set of consecutive numbers between a lower and upper bound. The numbers in the table can then be used in combination with the DATEADD<\/a><\/code> function to create the range of datetime <\/strong>values.<\/p>\n\n\n\n

Prior to SQL Server 2022, creating a numbers table usually involved some form of common table expressions, CROSS JOIN<\/code> of system objects, looping, or some other creative T-SQL. These solutions are neither elegant nor efficient at scale, with additional complexity when the step between interval values is larger than 1.<\/p>\n\n\n\n

The GENERATE_SERIES<\/code><\/a> relational operator in SQL Server 2022 makes creating a numbers table simple by returning a single-column table of numbers between a start<\/strong> and stop <\/strong>value, with an optional parameter defining the number of values to increment\/decrement between steps in the series:<\/p>\n\n\n

\nGENERATE_SERIES (start, stop [, step ])\n<\/pre><\/div>\n\n\n
This example creates a series of numbers between 1 and 100 in steps of 5:<\/p>\n\n\n
\nSELECT value\nFROM GENERATE_SERIES(1, 100, 5);\n<\/pre><\/div>\n\n\n
Taking this concept one step further, the next example shows how GENERATE_SERIES is used with DATEADD to create a set of values between 1:00 PM and 2:00 PM in 1-minute intervals:<\/p>\n\n\n
\nSELECT DATEADD(minute, s.value, 'Dec 10, 2022 1:00 PM') AS [Interval]\nFROM GENERATE_SERIES(0, 59, 1) AS s;\n<\/pre><\/div>\n\n\n
If the step argument is omitted, a default value of 1 is used when computing interval values. GENEATE_SERIES also works with decimal values, with a requirement that the start, stop, and step arguments must all be the same data type. If start <\/strong>is greater than stop <\/strong>and the step is a negative value, then the resulting series will be a decrementing set of values. If start<\/strong> is greater than stop <\/strong>and the step is positive, an empty table will be returned.<\/p>\n\n\n\n
Finally, GENERATE_SERIES<\/code> requires a compatibility level of 160 or higher.<\/p>\n\n\n\n
Group data in intervals with DATE_BUCKET<\/h2>\n\n\n\n
Time series data is often grouped into fixed intervals, or buckets, for analytical purposes. For example, sensor measurements taken every minute may be averaged over 15-minute or 1-hour intervals. While GENERATE_SERIES<\/code> and DATEADD<\/code> are used to create the buckets, we need a way to determine which bucket\/interval a measurement belongs to.<\/p>\n\n\n\n
The DATE_BUCKET<\/code> function returns the datetime <\/strong>value corresponding to the start of each datetime <\/strong>bucket for an arbitrary bucket size, with an optional parameter to define the origin from which to calculate each bucket. If no origin is provided, the default value of Jan 1, 1900, will be used as the origin date:<\/p>\n\n\n
\nDATE_BUCKET (datepart, number, date, origin)\n<\/pre><\/div>\n\n\n
The following example shows the buckets for Dec 10, 2022, for several date parts with a bucket size of 1 and an origin date of Jan 1, 2022:<\/p>\n\n\n
\nDECLARE @date DATETIME = 'Dec 10, 2022 12:05 PM';\nDECLARE @origin DATETIME = 'Jan 1, 2022 12:00 AM';\n\nSELECT 'Now' AS [BucketName], @date AS [DateBucketValue]\nUNION ALL\nSELECT 'Year', DATE_BUCKET (YEAR, 1, @date, @origin)\nUNION ALL\nSELECT 'Quarter', DATE_BUCKET (QUARTER, 1, @date, @origin)\nUNION ALL\nSELECT 'Month', DATE_BUCKET (MONTH, 1, @date, @origin)\nUNION ALL\nSELECT 'Week', DATE_BUCKET (WEEK, 1, @date, @origin)\nUNION ALL\nSELECT 'Day', DATE_BUCKET (DAY, 1, @date, @origin)\nUNION ALL\nSELECT 'Hour', DATE_BUCKET (HOUR, 1, @date, @origin)\nUNION ALL\nSELECT 'Minutes', DATE_BUCKET (MINUTE, 1, @date, @origin)\nUNION ALL\nSELECT 'Seconds', DATE_BUCKET (SECOND, 1, @date, @origin)\n<\/pre><\/div>\n\n\n
Notice how the date bucket value for the Week <\/strong>date part is Dec 10, 2022, which is a Saturday. That\u2019s because the provided origin date (Jan 1, 2022) is also a Saturday. (Note the default origin date of Jan 1, 1900, is a Monday). Therefore, when working with the Week <\/strong>date part, if you want your Week <\/strong>bucket to begin on a Sunday then be sure to use a known origin that falls on a Sunday.<\/p>\n\n\n\n
Where DATE_BUCKET<\/code> becomes especially useful is for bucket sizes larger than 1, for example when grouping data in 5-minute or 15-minute buckets.<\/p>\n\n\n
\nSELECT 'Now' AS [BucketName], GETDATE() AS [BucketDate]\nUNION ALL\nSELECT '5 Minute Buckets', DATE_BUCKET (MINUTE, 5, GETDATE())\nUNION ALL\nSELECT 'Quarter Hour', DATE_BUCKET (MINUTE, 15, GETDATE());\n<\/pre><\/div>\n\n\n
DATE_BUCKET<\/code> provides an easy way to determine which time-based interval a timestamped measurement belongs to using any arbitrary-sized interval.<\/p>\n\n\n\n
Gap analysis with FIRST_VALUE and LAST_VALUE<\/h2>\n\n\n\n
FIRST_VALUE<\/code> and LAST_VALUE<\/code> are not new functions to SQL Server 2022; what is <\/em>new is how NULL<\/code> values are handled. In previous versions of SQL Server, NULL<\/code> values are preserved.<\/p>\n\n\n\n
When working with time series data, it\u2019s possible to have gaps between measurements. Ideally, gaps are filled in with an imputed value. When using FIRST_VALUE<\/code> and LAST_VALUE<\/code> to compute the value corresponding to an interval, preserving NULL<\/code> values isn\u2019t ideal.<\/p>\n\n\n\n
In the following example, a series of sensor readings taken at 15-second intervals has some gaps:<\/p>\n\n\n\n
If analyzing the data in 1-minute intervals (using DATE_BUCKET<\/code>), the default value returned by FIRST_VALUE<\/code> will include the null values:<\/p>\n\n\n
\nSELECT [timestamp]\n   , DATE_BUCKET(MINUTE, 1, [timestamp]) AS [timestamp_bucket]\n   , SensorReading\n   , FIRST_VALUE (SensorReading) OVER ( \nPARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp]) \nORDER BY [timestamp] \nROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\n) AS [Default (RESPECT NULLS)]\nFROM MachineTelemetry\nORDER BY [timestamp];\n<\/pre><\/div>\n\n\n
FIRST_VALUE<\/code> and LAST_VALUE<\/code> include new syntax (IGNORE NULLS<\/code> or RESPECT NULLS<\/code>) in SQL Server 2022 which allows you to decide how NULL<\/code> values should be handled:<\/p>\n\n\n
\nFIRST_VALUE ( [scalar_expression ] )  [ IGNORE NULLS | RESPECT NULLS ]\n    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )\n<\/pre><\/div>\n\n\n
RESPECT NULLS<\/code> is the default behavior and will include null values in the result when computing the first or last value within a partition. Specifying IGNORE NULLS<\/code> will cause NULL<\/code> values to be excluded when computing the first or last value over a partition.<\/p>\n\n\n
\nSELECT [timestamp]\n   , DATE_BUCKET(MINUTE, 1, [timestamp]) AS [timestamp_bucket]\n   , SensorReading\n   , FIRST_VALUE (SensorReading) IGNORE NULLS OVER ( \nPARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp]) \nORDER BY [timestamp] \nROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\n) AS [Default (RESPECT NULLS)]\nFROM MachineTelemetry\nORDER BY [timestamp];\n<\/pre><\/div>\n\n\n
The new options for IGNORE NULLS<\/code> and RESPECT NULLS<\/code> allow you to decide how null values should be treated when analyzing your data.<\/p>\n\n\n\n
Learn more<\/h2>\n\n\n\n
Get started with SQL Server 2022<\/a> today. For more information and additional examples, visit the following resources:<\/p>\n\n\n\n
Microsoft Learn<\/h3>\n\n\n\n