How can I get dates that wrap around new year from non-datetime columns?

Question

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!

Answer 1

You can use MAKEDATE(), it makes the day_of_year to a date.

Test:

mysql> SELECT * FROM cli_table;
+-------+--------------+-------------+-------+
| month | day_of_month | day_of_year | value |
+-------+--------------+-------------+-------+
|     1 |            1 |           1 | 23.00 |
|     1 |            2 |           2 | 22.95 |
|    12 |           31 |         365 | 23.12 |
+-------+--------------+-------------+-------+
3 rows in set (0.00 sec)

mysql> SELECT 
    -> MAKEDATE(YEAR(CURDATE()),day_of_year) AS Date,
    ->     value AS DayValue
    -> FROM test.cli_table;
+------------+----------+
| Date       | DayValue |
+------------+----------+
| 2015-01-01 |    23.00 |
| 2015-01-02 |    22.95 |
| 2015-12-31 |    23.12 |
+------------+----------+
3 rows in set (0.00 sec)

mysql> 

You can try it in this Fiddle.

EDIT:

According of what you want, you can make it with pure SQL:

mysql> SELECT * FROM test.cli_table;
+-------+--------------+-------------+-------+
| month | day_of_month | day_of_year | value |
+-------+--------------+-------------+-------+
|     1 |            1 |           1 |  5.00 |
|     1 |            2 |           2 |  6.00 |
|     1 |            3 |           3 |  7.00 |
|    12 |           25 |         359 | 23.00 |
|    12 |           26 |         360 | 22.95 |
|    12 |           27 |         361 |  1.00 |
|    12 |           28 |         362 |  2.00 |
|    12 |           29 |         363 |  3.00 |
|    12 |           30 |         364 |  4.00 |
|    12 |           31 |         365 | 23.12 |
+-------+--------------+-------------+-------+
10 rows in set (0.00 sec)

mysql> SELECT 
    -> v.selected_date,
    -> v.dayyear,
    -> c.value AS DayValue,
    -> c.day_of_year 
    -> FROM test.cli_table AS c
    -> JOIN (SELECT 
    -> adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS selected_date,
    -> DAYOFYEAR(adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i)) AS dayyear
    -> FROM 
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) AS v
    -> ON (v.selected_date between '2015-12-25' AND '2016-01-03'
    -> AND v.dayyear=c.day_of_year);
+---------------+---------+----------+-------------+
| selected_date | dayyear | DayValue | day_of_year |
+---------------+---------+----------+-------------+
| 2015-12-25    |     359 |    23.00 |         359 |
| 2015-12-26    |     360 |    22.95 |         360 |
| 2015-12-27    |     361 |     1.00 |         361 |
| 2015-12-28    |     362 |     2.00 |         362 |
| 2015-12-29    |     363 |     3.00 |         363 |
| 2015-12-30    |     364 |     4.00 |         364 |
| 2015-12-31    |     365 |    23.12 |         365 |
| 2016-01-01    |       1 |     5.00 |           1 |
| 2016-01-02    |       2 |     6.00 |           2 |
| 2016-01-03    |       3 |     7.00 |           3 |
+---------------+---------+----------+-------------+
10 rows in set (0.00 sec)

mysql> 

Query Meaning:

SELECT 
    v.selected_date,
    v.dayyear,
    c.value AS DayValue,
    c.day_of_year 
FROM test.cli_table AS c
--- > This JOIN is generating all the dates between the selected_dates ('2015-12-25' AND '2016-01-03')
JOIN (SELECT 
        adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS selected_date,
        DAYOFYEAR(adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i)) AS dayyear
      FROM 
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) AS v
--- > Here I'm matching every dayyear of the generated dates with the cli_table.day_of_year.
ON (v.selected_date between '2015-12-25' AND '2016-01-03'
    AND v.dayyear=c.day_of_year);

Answer 2

I'd suggest you use a proven BI date dimension design. Each reading is a Fact - stored in something like

CREATE TABLE FactDailyTemp
DateSK INT
Temp Numeric(10,5)

DateSK is an FK to DIM.Date. If you need time, simply add another column to the fact TimeSK and a time dimension table. There are plenty of free scripts to load these tables on the net. Here's an example to get you started.

Dim.Date looks like this and allows for all kinds of filtering such as DayOfYear while retaining the actual date. Additional flag columns can help with typical queries... in this example the three bit fields I've tacked onto the end of my dimension.

CREATE TABLE [DIM].[Date]
(
 [DateSK] INT NOT NULL
,[FullDate] DATETIME NULL
,[DateAbbreviation] VARCHAR(11) NULL   --US Date FORMAT, MM/DD/YYYY
,[DayOfWeek] TINYINT NULL
,[DayNameOfWeek] CHAR(10) NULL
,[DayOfMonth] TINYINT NULL
,[DayOfYear] SMALLINT NULL
,[WeekOfYear] TINYINT NULL
,[MonthName] CHAR(10) NULL
,[MonthOfYear] TINYINT NULL
,[CalendarQuarter] TINYINT NULL
,[CalendarYear] SMALLINT NULL
,[CalendarYearMonth] CHAR(7) NULL
,[CalendarYearQtr] CHAR(7) NULL
,[Weekday] bit NULL
,[IsLastDayOfMonth] bit NULL
,[IsCurrentYear] bit NULL
,CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED ( [DateSK] )
)

This approach is called Dimensional Modeling and is suited to long term storage and analysis of large sets of data. If this works for you, I'd suggest investigating in more detail.

Specifically to the query you asked for, you can easily use the FullDate column with a DATE_ADD query.