Working with offsets
Time Series Analysis in SQL Server
Maham Faisal Khan
Senior Data Science Content Developer
Anatomy of a DATETIMEOFFSET
Components
| Date Part | Example |
|---|---|
| Date | 2019-04-10 |
| Time | 12:59:02.3908505 |
| UTC Offset | -04:00 |
Anatomy of a DATETIMEOFFSET
Components
| Date Part | Example |
|---|---|
| Date | 2019-04-10 |
| Time | 12:59:02.3908505 |
| UTC Offset | -04:00 |
Display
2019-04-10 12:59:02.3908505 -04:00
Changing offsets
DECLARE @SomeDate DATETIMEOFFSET =
'2019-04-10 12:59:02.3908505 -04:00';
SELECT
SWITCHOFFSET(@SomeDate, '-07:00') AS LATime;
| LATime |
|---|
| 2019-04-10 09:59:02.3908505 -07:00 |
Converting to DATETIMEOFFSET
DECLARE @SomeDate DATETIME2(3) =
'2019-04-10 12:59:02.390';
SELECT
TODATETIMEOFFSET(@SomeDate, '-04:00') AS EDT;
| EDT |
|---|
| 2019-04-10 12:59:02.390 -04:00 |
Time zone swaps with TODATETIMEOFFSET
DECLARE @SomeDate DATETIME2(3) =
'2016-09-04 02:28:29.681';
SELECT
TODATETIMEOFFSET(
DATEADD(HOUR, 7, @SomeDate),
'+02:00') AS BonnTime;
| BonnTime |
|---|
| 2016-09-04 09:28:29.681 +02:00 |
Discovering time zones
SELECT
tzi.name,
tzi.current_utc_offset,
tzi.is_currently_dst
FROM sys.time_zone_info tzi
WHERE
tzi.name LIKE '%Time Zone%';
| name | current_utc_offset | is_currently_dst |
|---|---|---|
| Russia Time Zone 3 | +04:00 | 0 |
| Russia Time Zone 10 | +11:00 | 0 |
| Russia Time Zone 11 | +12:00 | 0 |
Let's practice!
Time Series Analysis in SQL Server
