Linear Regression With Dates

Most of the math libraries I use don’t support date values for linear regression. This was my work around.

1. Convert date / times to Unix time.

2. Take the earliest date and subtract that time value from all the other values.
3. Run linear regression using the modified Unix times.
4. Add the earliest time value back to each Unix time when stating results.

Simple Date Table Script: MS SQL

This script creates a date table for Microsoft SQL 2008 and higher to be used for joins in certain types of date related queries. The script was based on this post but I made several changes including adding ISO week and ISO year fields, converting the key to a Unix Time calculation, and adding an “end of month” flag. The script is not particularly efficient, but it is only intended to be run once so that was not a concern.

Table Structure

CREATE TABLE [dbo].[DateLookup](
[UnixTime] [bigint] NOT NULL,
[DateFull] [datetime] NULL,
[USRegionFormat] [varchar](10) NULL,
[FullYear] [char](4) NULL,
[QuarterNumber] [tinyint] NULL,
[WeekNumber] [tinyint] NULL,
[WeekDayName] [varchar](10) NULL,
[MonthDay] [tinyint] NULL,
[YearDay] [smallint] NULL,
[WeekDay] [tinyint] NULL,
[MonthNumber] [tinyint] NULL,
[ISOYear] [smallint] NULL,
[ISOWeek] [tinyint] NULL,
[MonthName] [nvarchar](12) NULL,
[EndOfMonth] [bit] NULL,
PRIMARY KEY CLUSTERED
(
[UnixTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Script

DECLARE @Date DATETIME
SET @Date = '1/1/1990'

WHILE @Date < '1/1/2100'
BEGIN
INSERT INTO DateLookup
(
UnixTime,
DateFull,
FullYear,
QuarterNumber,
WeekNumber,
WeekDayName,
MonthDay,
YearDay,
USRegionFormat,
[WeekDay],
MonthNumber,
ISOWeek,
ISOYear,
[MonthName],
EndOfMonth
)
SELECT
CAST(DATEDIFF(MINUTE,{d '1970-01-01'}, @Date) AS bigint) * 60, --UnixTime
@Date, -- DateFull
YEAR(@Date), -- FullYear
DATEPART(qq, @Date), --QuarterNumber
DATEPART(ww, @Date), --WeekNumber
DATENAME(dw, @Date), --WeekDayName
DATEPART(dd, @Date), --MonthDay
DATEPART(dy, @Date), --YearDay
CONVERT(VARCHAR(10), @Date, 101), --CharacterDate
DATEPART(dw, @Date), --[WeekDay]
DATEPART(mm, @Date), --MonthNumber

DATEPART(ISO_WEEK,@Date), --ISOWeek

CASE --ISOYear
WHEN DATEPART(ISO_WEEK, @Date) = 1 and DATEPART(MONTH, @Date) = 12
THEN DATEPART(year, @Date) + 1
WHEN DATEPART(ISO_WEEK, @Date) = 53 and DATEPART(MONTH, @Date) = 1
THEN DATEPART(year, @Date) -1
ELSE DATEPART(year, @Date)
END,

DATENAME(month, @Date), --MonthName

CASE --EndOfMonth
WHEN CONVERT(VARCHAR(10), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Date)+1,0)), 101) = CONVERT(VARCHAR(10), @Date, 101)
THEN 1
ELSE 0
END

SET @Date = DATEADD(dd, 1, @Date)
END