Datums samenstellen

Tijdreeksanalyse in SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Wat je leert

Werken met datumonderdelen
Strings naar datums omzetten, incl. offsets en ongeldige datums
Filteren, groeperen en aggregeren per periode
Downsamplen
Aggregaties over vensters
Upsamplen
Lopende totalen en voortschrijdende gemiddelden
Overlap in datumbereiken vinden

Een datum samenstellen

SELECT
    GETDATE() AS DateTime_LTz,
    GETUTCDATE() AS DateTime_UTC;

SELECT
    SYSDATETIME() AS DateTime2_LTz
    SYSUTCDATETIME() AS DateTime2_UTC;

Results:

DateTime_LTz	DateTime_UTC	DateTime2_LTz	DateTime2_UTC
2019-03-07 21:21:33.670	2019-03-08 02:21:33.670	2019-03-07 21:21:33.6716402	2019-03-08 02:21:33.6716402

Een datum ontleden

DECLARE
    @SomeDate DATETIME2(3) = '2019-03-01 08:17:19.332';

SELECT YEAR(@SomeDate);

SELECT MONTH(@SomeDate);

SELECT DAY(@SomeDate);

YEAR = 2019

MONTH = 3

DAY = 1

Datums parseren met onderdelen

Functies

DATEPART()

SELECT
  DATEPART(YEAR, @dt) AS TheYear;

DATENAME()

SELECT
  DATENAME(MONTH, @dt) AS TheMonth;

Onderdelen

Jaar / Maand / Dag
Dag van het jaar
Dag van de week
Week van het jaar
ISO-week van het jaar
Minuut / Seconde
Milliseconde / Nanoseconde

Optellen en aftrekken met datums

DECLARE
    @SomeTime DATETIME2(7) = '1992-07-14 14:49:36.2294852';

SELECT
    DATEADD(DAY, 1, @SomeTime) AS NextDay,
    DATEADD(DAY, -1, @SomeTime) AS PriorDay;

SELECT
    DATEADD(HOUR, -3, DATEADD(DAY, -4, @SomeTime)) AS Minus4Days3Hours;

NextDay	PriorDay
1992-07-15 14:49:36.2294852	1992-07-13 14:49:36.2294852

Minus4Days3Hours
1992-07-10 11:49:36.2294852

Datums vergelijken

DECLARE
    @StartTime DATETIME2(7) = '2012-03-01 14:29:36',
    @EndTime DATETIME2(7) = '2012-03-01 18:00:00';

SELECT
    DATEDIFF(SECOND, @StartTime, @EndTime) AS SecondsElapsed,
    DATEDIFF(MINUTE, @StartTime, @EndTime) AS MinutesElapsed,
    DATEDIFF(HOUR, @StartTime, @EndTime) AS HoursElapsed;

SecondsElapsed	MinutesElapsed	HoursElapsed
12624	211	4

Datums afronden

DECLARE @SomeTime DATETIME2(7) = '2017-05-12 16:25:16.2248991';

SELECT
    DATEADD(DAY, DATEDIFF(DAY, 0, @SomeTime), 0) AS RoundedToDay,
    DATEADD(HOUR, DATEDIFF(HOUR, 0, @SomeTime), 0) AS RoundedToHour,
    DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @SomeTime), 0) AS RoundedToMinute;

RoundedToDay          RoundedToHour         RoundedToMinute
2017-05-12 00:00:00   2017-05-12 16:00:00   2017-05-12 16:25:00

Laten we oefenen!

Tijdreeksanalyse in SQL Server