I have a table in my MySQL database that stores daily data (an entry for each day of the year), but does not use a DATETIME
column for this. This is because the data is the same for every year (it's climatology data, so the "normal" high temperature for Jan 7, 2015 is the same as for Jan 7, 2016 and so on). I have three fields that represent the day this data is valid for, so my table looks like:
month, day_of_month, day_of_year, value
1 1 1 23
1 2 2 22.95
...
12 31 365 23.12
Say I then want to get the values from this table from a certain date, like 01 Dec 2015 to 60 days from then, which would be 30 Jan 2016. Is there a way to get back the date and value for all of those days, working okay across the new year? Ideally I'd love results that flow like:
valid_date, value
...
2015-12-31 23.12
2016-01-01 23
2016-01-02 22.95
...
(hopefully you get the idea).
If you can think of a better way to store this static daily data so that I can achieve the above goal, I'm open to that as well! Thanks for your help!
DATETIME
; don't split up datetime into fields except duringSELECT
.