I have to store climate (temperature) data for a few cities. This would mean that each city will have 365 values (ex: The value for Jan 1, is the average temperature for that city for all Jan 1 for 50 years (1955-2004).
Hence I am not at all concerned with year. How do I store the date in a date column? Do I store Jan 1 as Jan 1 2009 ( a non-leap year)? Or is there any better way out?
One idea:
Use "day of year", but offset
a different approach
you can have a separate table to store all the dates, just like a Date dimension table and then in your main table have a foreign key pointing to it
CREATE TABLE YourDatesTable (
DateID int NOT NULL IDENTITY(1, 1),
[Date] datetime NOT NULL,
[Year] int NOT NULL,
[Month] int NOT NULL,
[Day] int NOT NULL
CONSTRAINT PK_Dates PRIMARY KEY CLUSTERED (DateID)
)
Set the Dates Start and End
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '01/01/1955'
SET @EndDate = '12/31/2004'
Generate the Date Records
DECLARE @LoopDate datetime
SET @LoopDate = @StartDate
WHILE @LoopDate <= @EndDate
BEGIN
INSERT INTO YourDatesTable VALUES (
@LoopDate,
Year(@LoopDate),
Month(@LoopDate),
Day(@LoopDate)
)
SET @LoopDate = DateAdd(d, 1, @LoopDate)
END
View the Dates and notice that leap years have feb 29 and non leap years have only till feb 28
a non leap year
SELECT * FROM YourDatesTable
where Month = 2
and Year = 2003
a leap year
SELECT * FROM YourDatesTable
where Month = 2
and Year = 2004
you can use the DateID as a foreign key in your main table
This way you have day, month and year seperate and you also have the Date in dateformat for any date related functions
for reference the script to generate a date dimension i used this site http://www.sqlbook.com/Data-Warehousing/Date-Dimension-SQL-script-18.aspx
DateID and not the ([Month],[Day]) combination?
Commented
May 25, 2012 at 10:22
In similar cases I store month and day (well, typically year and month) separately, which later can be manipulated easily. Leap day in such series can be tricky since the average will be computed from much less data than ordinary days.
SELECT something FROM weather_data WHERE month * 100 + day < 325 -- for 25 March
Commented
May 25, 2012 at 7:22
Given the information you provided, I think you have already determined the best way.
...store Jan 1 as Jan 1 2009 ( a non-leap year)
This method allows you to use date types as dates when doing comparisons or date arithmetic without having to convert them. For most scenarios it would be the simplest solution.
The solution is also flexible in that it could later be enhanced to store new averages every year (rather than replacing the old averages). The year would then be the year in which the average was calculated. Of course that would introduce complexity, but the complexity would be deferred until it is necessary.
