# script: scripts/10_system_objects_utc.sql | database: lab-cst | started (UTC): 2026-10-16 01:50:09
/* 10 - Catalog views, DMVs and Query Store stay in UTC (CST database)
   The session is additionally set to India Standard Time so any accidental "follows the setting"
   behaviour would show up as a +05:30 shift instead of the DB's -05:00. */
SET NOCOUNT ON;
SET TIME ZONE 'India Standard Time';
PRINT '=== 10 System objects on ' + DB_NAME() + ' (session tz: ' + CURRENT_TIMEZONE_ID() + ')';
=== 10 System objects on lab-cst (session tz: India Standard Time)
SELECT 'reference' AS what, GETDATE() AS getdate_local, GETUTCDATE() AS getutcdate_;
what|getdate_local|getutcdate_
----|-------------|-----------
reference|2026-10-16 07:20:09.977|2026-10-16 01:50:09.973

PRINT '--- 10.1 sys.dm_exec_sessions (login_time, last_request_start_time)';
SELECT session_id, login_time, last_request_start_time, time_zone,
       DATEDIFF(MINUTE, last_request_start_time, GETUTCDATE()) AS min_vs_utc,
       DATEDIFF(MINUTE, last_request_start_time, GETDATE())    AS min_vs_local
FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
--- 10.1 sys.dm_exec_sessions (login_time, last_request_start_time)
session_id|login_time|last_request_start_time|time_zone|min_vs_utc|min_vs_local
----------|----------|-----------------------|---------|----------|------------
67|2026-10-16 01:50:09.930|2026-10-16 01:50:10.017|India Standard Time|0|330

PRINT '--- 10.2 sys.dm_exec_requests.start_time for the running request';
SELECT start_time, GETUTCDATE() AS utc_now, GETDATE() AS local_now FROM sys.dm_exec_requests WHERE session_id = @@SPID;
--- 10.2 sys.dm_exec_requests.start_time for the running request
start_time|utc_now|local_now
----------|-------|---------
2026-10-16 01:50:10.063|2026-10-16 01:50:10.067|2026-10-16 07:20:10.067

PRINT '--- 10.3 sys.objects.create_date for an object created right now';
DROP TABLE IF EXISTS tzlab.CreateDateProbe;
CREATE TABLE tzlab.CreateDateProbe (id int);
SELECT name, create_date, modify_date, GETUTCDATE() AS utc_now, GETDATE() AS local_now
FROM sys.objects WHERE object_id = OBJECT_ID('tzlab.CreateDateProbe');
DROP TABLE tzlab.CreateDateProbe;
--- 10.3 sys.objects.create_date for an object created right now
name|create_date|modify_date|utc_now|local_now
----|-----------|-----------|-------|---------
CreateDateProbe|2026-10-16 01:50:10.117|2026-10-16 01:50:10.117|2026-10-16 01:50:10.120|2026-10-16 07:20:10.120

PRINT '--- 10.4 Query Store intervals (datetimeoffset) and last execution times';
SELECT TOP (3) runtime_stats_interval_id, start_time, end_time FROM sys.query_store_runtime_stats_interval ORDER BY runtime_stats_interval_id DESC;
SELECT TOP (3) q.query_id, q.last_execution_time FROM sys.query_store_query q ORDER BY q.last_execution_time DESC;
--- 10.4 Query Store intervals (datetimeoffset) and last execution times
runtime_stats_interval_id|start_time|end_time
-------------------------|----------|--------
1|2026-10-16 01:00:00.0000000 +00:00|2026-10-16 02:00:00.0000000 +00:00

query_id|last_execution_time
--------|-------------------
90|2026-10-16 01:50:08.9430000 +00:00
88|2026-10-16 01:50:06.7500000 +00:00
86|2026-10-16 01:50:06.1830000 +00:00

PRINT '--- 10.5 sys.dm_db_resource_stats.end_time';
SELECT TOP (1) end_time, GETUTCDATE() AS utc_now, GETDATE() AS local_now FROM sys.dm_db_resource_stats ORDER BY end_time DESC;
--- 10.5 sys.dm_db_resource_stats.end_time
end_time|utc_now|local_now
--------|-------|---------
2026-10-16 01:49:58.183|2026-10-16 01:50:10.230|2026-10-16 07:20:10.230

PRINT '--- 10.6 sys.dm_exec_query_stats.last_execution_time for this statement';
SELECT TOP (1) last_execution_time, GETUTCDATE() AS utc_now, GETDATE() AS local_now
FROM sys.dm_exec_query_stats ORDER BY last_execution_time DESC;
--- 10.6 sys.dm_exec_query_stats.last_execution_time for this statement
last_execution_time|utc_now|local_now
-------------------|-------|---------
2026-10-16 01:50:10.230|2026-10-16 01:50:10.280|2026-10-16 07:20:10.280

PRINT '--- 10.7 sys.database_scoped_configurations / sys.databases have no "local" timestamps; sys.databases.create_date:';
SELECT name, create_date FROM sys.databases WHERE name = DB_NAME();
--- 10.7 sys.database_scoped_configurations / sys.databases have no "local" timestamps; sys.databases.create_date:
name|create_date
----|-----------
lab-cst|2026-10-16 01:29:43.710

SET TIME ZONE LOCAL;
# sqlcmd exit code: 0