One of the powerful aspects of Query Store is the ability to directly query the DMVs for details of historical executions and performance.
A key view for this is sys.query_store_runtime_stats (but also sys.query_store_runtime_stats_interval).
If you’re querying these views to look at what was happening during a particular time period then it’s important to understand that the dates and times are stored according to UTC time (which is equivalent to GMT with no adjustment for daylight savings times).
You can see this if you look at a few rows from the view:
SELECT runtime_stats_id, first_execution_time, last_execution_time FROM sys.query_store_runtime_stats;
Though it will be more obvious to you if you’re in a time zone other than the UK.
The datetimes are stored as DATETIMEOFFSET which you can see from the +00:00 at the end of each entry. DATETIMEOFFSET allows you to store both the datetime as well as the timezone being used.
This means that if you’re querying against these columns you need to convert the values you are looking for to UTC first. You can do that my making sure you use GETUTCDATE() instead of GETDATE(), or if you are using specific local times you can use the AT TIME ZONE function e.g.
SELECT CAST('2019-08-21 11:50:40.400 +9:00' AS datetimeoffset) AT TIME ZONE('UTC');
I’m a big fan of storing all datetimes as the UTC version. It avoids things going out of sequence due to daylight saving changes – and can be helpful in mitigating problems when you have application users in multiple countries accessing the same database.
I’ll admit I might be biased though as – being based in the UK – UTC is the same as my local time half the year.