4

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!

3
  • So you are only interested in the "normal" temperatures? No need to compare one year against another, perhaps for tracking trends? Yes, you could write code to give you 01 DEC 2015 to 30 Jan 2016, but you are just making it harder on yourself. DATETIME or TIMESTAMP can be used quite easily to support ranges, comparisons, etc. You can also aggregate the data to give you the 365(ish) data you want.
    – RLF
    Nov 13, 2015 at 21:43
  • 1
    Well I need the normal temperatures, but at a daily level where I know what day goes with what temperature. I'm basically treating them as "the temperature" to display for that future date. I realized I can likely work around this on the client-side by getting back all 365 days and parsing through it in a different manner, though I suppose this is still an interesting question if there's a MySQL answer to it.
    – Devin
    Nov 13, 2015 at 22:06
  • Use DATETIME; don't split up datetime into fields except during SELECT.
    – Rick James
    Dec 3, 2015 at 5:05

2 Answers 2

2

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);
0
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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