Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
can you describe some use cases of this data –  miracle173 May 25 '12 at 11:37
    
@miracle173 This would be for a tourism related module for my webiste. A User would click on the map, and find some points, A graph will be shown, showing max & min temperature throughout the year. User can also select by month & week, as well as zoom into the graph to show data for any arbitrary subset of the year –  Devdatta Tengshe May 25 '12 at 13:25

4 Answers 4

One idea:

Use "day of year", but offset

  • March 1st is 1
  • Feb 28th is 365
  • Feb 29th is 366
share|improve this answer

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

share|improve this answer
    
Why use DateID and not the ([Month],[Day]) combination? –  ypercube May 25 '12 at 10:22
    
you can do that, but how will you differentiate between leap and non leap year. –  AmmarR May 25 '12 at 10:27
    
If you want to store year, fine. The OP states however that he wants to keep Month and Day, independent of Year. –  ypercube May 25 '12 at 10:29
    
Firstly, I do not have 29Feb as a date. Secondly, I don't have a year in my data; I feel that this might be over-engineering, and it still doesn't tell me what I should store for the year –  Devdatta Tengshe May 25 '12 at 13:23
    
why do you want to store the year if its useless, and if you insist on having the year just store he current year.., this way i agree with @ypercube just take in month and day from the table and or have a table just month,week and day. its standard for any year –  AmmarR May 25 '12 at 16:50

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.

share|improve this answer
    
the problem i foresee with this approach, is that I can use any date functions in my query, for example, get me all values before March-25 and so on –  Devdatta Tengshe May 25 '12 at 7:18
    
@DevdattaTengshe - yes, your ordinary date operators won't work, but you can easily do SELECT something FROM weather_data WHERE month * 100 + day < 325 -- for 25 March –  dezso May 25 '12 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.