{"id":981,"date":"2019-01-04T06:00:12","date_gmt":"2019-01-04T14:00:12","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=981"},"modified":"2019-01-04T06:52:44","modified_gmt":"2019-01-04T14:52:44","slug":"handling-dates-in-query-store","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/","title":{"rendered":"Handling Dates in Query Store"},"content":{"rendered":"

Query Store retains query performance data at the plan level.\u00a0 This data is then broken out into intervals of time, determined by the INTERVAL_LENGTH_MINUTES setting<\/a>.\u00a0 The time intervals are found in the sys.query_store_runtime_stats_interval system view, and the start_time and end_time columns are of the DATETIMEOFFSET<\/a> data type.\u00a0 This means that the date is time-zone aware, and in Query Store the data is stored as UTC.\u00a0 Now why does all of this matter?\u00a0 Because handling dates in Query Store is important if you’re going to query the data directly.<\/p>\n

Here’s what we get with a simple query against the runtime stats interval view:<\/p>\n

\r\nSELECT *\r\nFROM sys.query_store_runtime_stats_interval\r\nORDER BY runtime_stats_interval_id;\r\nGO\r\n<\/pre>\n
\"sys.query_store_runtime_stats_interval<\/a>
sys.query_store_runtime_stats_interval output<\/figcaption><\/figure>\n
These are 10 minute intervals and notice that they all have +00:00 for the offset, which is\u00a0 +\/- UTC.\u00a0 If you want to display data based on the intervals, and convert it to your local timezone, you need to use AT TIME ZONE.\u00a0 We can use a variation of a query from my last post, Finding the Slowest Query in a Stored Procedure<\/a>, to see this in action.<\/p>\n
\r\nSELECT\r\n   [qsq].[query_id],\r\n   [qsp].[plan_id],\r\n   [qsq].[object_id],\r\n   [rs].[runtime_stats_interval_id],\r\n   [rsi].[start_time] AT TIME ZONE 'Eastern Standard Time' [EST StartTime],\r\n   [rsi].[end_time] AT TIME ZONE 'Eastern Standard Time' [EST EndTime],\r\n   [rs].[count_executions],\r\n   [rs].[avg_duration],\r\n   [rs].[avg_cpu_time],\r\n   [rs].[avg_logical_io_reads]\r\nFROM [sys].[query_store_query] [qsq]\r\nJOIN [sys].[query_store_query_text] [qst]\r\n   ON [qsq].[query_text_id] = [qst].[query_text_id]\r\nJOIN [sys].[query_store_plan] [qsp]\r\n   ON [qsq].[query_id] = [qsp].[query_id]\r\nJOIN [sys].[query_store_runtime_stats] [rs]\r\n   ON [qsp].[plan_id] = [rs].[plan_id]\r\nJOIN [sys].[query_store_runtime_stats_interval] [rsi]\r\n   ON [rs].[runtime_stats_interval_id] = [rsi].[runtime_stats_interval_id]\r\nWHERE [qsq].[query_id] = 1421\r\n   AND [qsp].[plan_id] = 1426\r\n   AND [rsi].[end_time] > DATEADD(HOUR, -1, GETUTCDATE())\r\nORDER BY [rs].[runtime_stats_interval_id];\r\nGO\r\n<\/pre>\n
\"StartTime<\/a>
StartTime and EndTime converted<\/figcaption><\/figure>\n
Since I’m in chilly Cleveland, OH, which is in the Eastern Time Zone, I’ve converted the data as such.\u00a0 If you’re not familiar with the exact time zone text you can use, query sys.time_zone_info<\/a>.\u00a0 Note that DATETIMEOFFSET does not<\/strong> account for Daylight Savings Time, so you’ll have to handle that in your code (yay for writing this post in January).<\/p>\n
Separate from displaying the date, you may want to query for a particular range, as I’ve done in the above query with AND [rsi].[end_time] > DATEADD(HOUR, -1, GETUTCDATE()).\u00a0 Notice that I’m using GETUTCDATE(), which is due to an interesting behavior with DATEADD which Aaron Bertrand has detailed in his post, Performance Surprises and Assumptions: DATEADD<\/a>.\u00a0 There are other date fields you could use here, such as last_execution_time, and it, as well as every other date column in the Query Store system views, is DATETIMEOFFSET.\u00a0 Of note: last_execution_time exists in in sys.query_store_query, sys.query_store_plan, and<\/em> sys.query_store_runtime_stats but they can and will be different between a query, its plan(s), and the runtime stats intervals.<\/p>\n
Hopefully this helps the next time you’re writing queries against the Query Store system views!<\/p>\n","protected":false},"excerpt":{"rendered":"
Query Store retains query performance data at the plan level.\u00a0 This data is then broken out into intervals of time, determined by the INTERVAL_LENGTH_MINUTES setting.\u00a0 The time intervals are found in the sys.query_store_runtime_stats_interval system view, and the start_time and end_time columns are of the DATETIMEOFFSET data type.\u00a0 This means that the date is time-zone aware, […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46],"tags":[],"yoast_head":"\nHandling Dates in Query Store - Erin Stellato<\/title>\n<meta name=\"description\" content=\"Dates in Query Store are stored as DATETIMEOFFSET and if you're going to query the system views directly, understanding this data type is important.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Handling Dates in Query Store - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"Dates in Query Store are stored as DATETIMEOFFSET and if you're going to query the system views directly, understanding this data type is important.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-04T14:00:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-01-04T14:52:44+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/01\/1_QS.jpg\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/\",\"name\":\"Handling Dates in Query Store - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2019-01-04T14:00:12+00:00\",\"dateModified\":\"2019-01-04T14:52:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Dates in Query Store are stored as DATETIMEOFFSET and if you're going to query the system views directly, understanding this data type is important.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Handling Dates in Query Store\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Handling Dates in Query Store - Erin Stellato","description":"Dates in Query Store are stored as DATETIMEOFFSET and if you're going to query the system views directly, understanding this data type is important.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/","og_locale":"en_US","og_type":"article","og_title":"Handling Dates in Query Store - Erin Stellato","og_description":"Dates in Query Store are stored as DATETIMEOFFSET and if you're going to query the system views directly, understanding this data type is important.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/","og_site_name":"Erin Stellato","article_published_time":"2019-01-04T14:00:12+00:00","article_modified_time":"2019-01-04T14:52:44+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/01\/1_QS.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/","name":"Handling Dates in Query Store - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2019-01-04T14:00:12+00:00","dateModified":"2019-01-04T14:52:44+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Dates in Query Store are stored as DATETIMEOFFSET and if you're going to query the system views directly, understanding this data type is important.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/handling-dates-in-query-store\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Handling Dates in Query Store"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/981"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=981"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/981\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=981"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=981"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=981"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}