{"id":1112,"date":"2019-01-12T11:36:49","date_gmt":"2019-01-12T11:36:49","guid":{"rendered":"http:\/\/blog.silverterra.net\/?p=1112"},"modified":"2024-12-08T20:32:34","modified_gmt":"2024-12-08T20:32:34","slug":"mysql-date-range-creation","status":"publish","type":"post","link":"https:\/\/blog.silverterra.net\/?p=1112","title":{"rendered":"mysql date range creation"},"content":{"rendered":"

Goal: create a date range<\/p><\/blockquote>\n

Simple question to create a date range from a start date to an end date.<\/p>\n

1. date dimension table – previously created range<\/b>
\nSimple, let’s create a date dimension table filled up with the days from a specified day to the future(?). This is a very dangerous if we forget to fill the table properly.The minimum is a TIME DIM table protector mechanism.<\/span>
\nJust imagine a situation it is actually happened: one day the data-warehouse reports appeared with no data (this is the better case), or with incorrect data (this is worst because of investigation process). So the root cause was the time dim table. The automated filler process failed days before and the time dim table was incorrect.<\/i>
\nImagine what happening if the ETL processes use this TIME DIM table as well. Better to not imagine this…<\/span><\/p>\n

2. dynamically created range – method one<\/b>
\nIn this method the is no physical table just a dynamically created record set.
\n(replace the dates with variables if you want, such as v_start_date, v_end_date or something like this)<\/p>\n

\r\nselect *\r\nfrom (\r\n  SELECT H+T+U+1 as daynum, CAST(( '2019-1-1' + INTERVAL (H+T+U) DAY) AS date) as date \r\n  FROM ( SELECT 0 H \r\n    UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\r\n    UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600\r\n    UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900 ) H\r\n  CROSS JOIN ( SELECT 0 T\r\n    UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\r\n    UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\r\n    UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90 ) T\r\n  CROSS JOIN ( SELECT 0 U\r\n    UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\r\n    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6\r\n    UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) U\r\n  WHERE CAST(('2019-1-1' + INTERVAL (H+T+U) DAY ) AS date) <= ( '2019-12-1' )\r\n) a\r\norder by a.date\r\n<\/pre>\n
This is not the most flexible solution because you have to create the numbers for the date stepping. In this query the maximal range is from start date<\/b> to start date + 999<\/b>. Of course you can extend the inner union with the thousands if you want.<\/p>\n
3. dynamically created range – method two – more flexible<\/b>
\nUse an incremental variable in the select like this.
\n(replace the dates with variables if you want, such as v_start_date, v_end_date or something like this)<\/p>\n
\r\nselect @num + 2 as daynum, date_format( adddate('2019-1-1', @num:=@num+1), '%Y-%m-%d' ) as date\r\n  from information_schema.COLUMNS, (select @num:=-1) num\r\n where @num < (select DATEDIFF('2019-12-1', '2019-1-1'))\r\n<\/pre>\n
This method is shorter, more readable and flexible enough. One more thing: there is a fake table reference in the select information_schema.COLUMNS<\/b>. Use like this or replace one of your tables.
\nIn this query the maximal range is from start date<\/b> to start date + record number of the fake table<\/b>.<\/span><\/p>\n
4. dynamically created range – method two – mariaDB based<\/b>
\nThe magical sequence engine. “MariaDB 10.0.3 introduces a new Storage Engine: Sequence. It isn\u2019t in MySQL or in older MariaDBs versions. Sequence is a special engine, which does not create or read any table. It only generates on the fly a sequence of integers which is returned to your query, and then dies. The sequence\u2019s bounds and increment depend from the table name.”<\/p>\n
\r\nselect @num + 2 as daynum, date_format( adddate('2019-1-1', @num:=@num+1), '%Y-%m-%d' ) as date\r\n  from seq_1_to_365, (select @num:=-1) num\r\n where @num < (select DATEDIFF('2019-12-31', '2019-1-1'))\r\n<\/pre>\n
In this query the maximal range is from start date<\/b> to start date + based on the seq table definition (name)<\/b>.<\/span><\/p>\n
seq table name:
\nseq_1_to_100   -> 1 to 100
\nseq_1_to_365   -> 1 to 365
\nseq_1_to_1000  -> 1 to 1000
\nseq_1_to_10000 -> 1 to 10000
\n… and so on<\/p>\n
5. mariaDB like method on MySQL<\/b>
\nWe don’t have sequence engine and we can not install the plugin? No problem. Let’s create a table instead to store the numbers<\/p>\n
\r\nCREATE TABLE `t_seq_1_to_1000` (`seq` INT(11) NOT NULL,\tPRIMARY KEY (`seq`)) ENGINE=InnoDB;\r\n<\/pre>\n
Fill this table with numbers.<\/p>\n
\r\nINSERT INTO t_seq_1_to_1000(seq) \r\nSELECT @row := @row + 1 FROM \r\n(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t,\r\n(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2, \r\n(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3, \r\n#(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4, \r\n#(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t5, \r\n#(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t6, \r\n#(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t7, \r\n#(SELECT 0 UNION ALL SELECT 1) t8, \r\n(SELECT @row:=0) tn;\r\n<\/pre>\n
This command will insert 1 000 numbers to our new table. The commented lines can extend the row numbers like t4: 10 000, t5: 1 000 00 … and the date range creator select like this<\/p>\n
\r\nselect @num + 2 as daynum, date_format( adddate('2019-1-1', @num:=@num+1), '%Y-%m-%d' ) as date\r\n  from t_seq_1_to_1000, (select @num:=-1) num\r\n where @num < (select DATEDIFF('2019-12-1', '2019-1-1'))\r\n<\/pre>\n
We are using the t_seq… table just for the rows not for the real values. The real counter is the @num<\/b> variable itself.
\nIn this query the maximal range is from start date<\/b> to start date + 1000 days<\/b>.<\/span><\/p>\n
This is not the end of the story just a quick thought provoking post.<\/p>\n","protected":false},"excerpt":{"rendered":"
Goal: create a date range Simple question to create a date range from a start date to an end date. 1. date dimension table – previously created range Simple, let’s create a date dimension table filled up with the days from a specified day to the future(?). This is a very dangerous if we forget […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,9],"tags":[],"class_list":["post-1112","post","type-post","status-publish","format-standard","hentry","category-database","category-mysql"],"_links":{"self":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/1112","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1112"}],"version-history":[{"count":2,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/1112\/revisions"}],"predecessor-version":[{"id":1186,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/1112\/revisions\/1186"}],"wp:attachment":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1112"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}