Trabalhando com offsets
Análise de Séries Temporais no SQL Server
Maham Faisal Khan
Senior Data Science Content Developer
Anatomia de um DATETIMEOFFSET
Componentes
| Parte da data | Exemplo |
|---|---|
| Data | 2019-04-10 |
| Hora | 12:59:02.3908505 |
| Offset de UTC | -04:00 |
Anatomia de um DATETIMEOFFSET
Componentes
| Parte da data | Exemplo |
|---|---|
| Data | 2019-04-10 |
| Hora | 12:59:02.3908505 |
| Offset de UTC | -04:00 |
Exibição
2019-04-10 12:59:02.3908505 -04:00
Alterando 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 |
Convertendo para 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 |
Troca de fusos com 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 |
Descobrindo fusos horários
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 |
Vamos praticar!
Análise de Séries Temporais no SQL Server
