Silverterra 3d blog

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 to fill the table properly.The minimum is a TIME DIM table protector mechanism.
Just 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.
Imagine what happening if the ETL processes use this TIME DIM table as well. Better to not imagine this…

2. dynamically created range – method one
In this method the is no physical table just a dynamically created record set.
(replace the dates with variables if you want, such as v_start_date, v_end_date or something like this)

select *
from (
  SELECT H+T+U+1 as daynum, CAST(( '2019-1-1' + INTERVAL (H+T+U) DAY) AS date) as date 
  FROM ( SELECT 0 H 
    UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
    UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600
    UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900 ) H
  CROSS JOIN ( SELECT 0 T
    UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30
    UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60
    UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90 ) T
  CROSS JOIN ( SELECT 0 U
    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 ) U
  WHERE CAST(('2019-1-1' + INTERVAL (H+T+U) DAY ) AS date) <= ( '2019-12-1' )
) a
order by a.date

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 to start date + 999. Of course you can extend the inner union with the thousands if you want.

3. dynamically created range – method two – more flexible
Use an incremental variable in the select like this.
(replace the dates with variables if you want, such as v_start_date, v_end_date or something like this)

select @num + 2 as daynum, date_format( adddate('2019-1-1', @num:=@num+1), '%Y-%m-%d' ) as date
  from information_schema.COLUMNS, (select @num:=-1) num
 where @num < (select DATEDIFF('2019-12-1', '2019-1-1'))

This method is shorter, more readable and flexible enough. One more thing: there is a fake table reference in the select information_schema.COLUMNS. Use like this or replace one of your tables.
In this query the maximal range is from start date to start date + record number of the fake table.

4. dynamically created range – method two – mariaDB based
The magical sequence engine. “MariaDB 10.0.3 introduces a new Storage Engine: Sequence. It isn’t 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’s bounds and increment depend from the table name.”

select @num + 2 as daynum, date_format( adddate('2019-1-1', @num:=@num+1), '%Y-%m-%d' ) as date
  from seq_1_to_365, (select @num:=-1) num
 where @num < (select DATEDIFF('2019-12-31', '2019-1-1'))

In this query the maximal range is from start date to start date + based on the seq table definition (name).

seq table name:
seq_1_to_100 -> 1 to 100
seq_1_to_365 -> 1 to 365
seq_1_to_1000 -> 1 to 1000
seq_1_to_10000 -> 1 to 10000
… and so on

5. mariaDB like method on MySQL
We don’t have sequence engine and we can not install the plugin? No problem. Let’s create a table instead to store the numbers

CREATE TABLE `t_seq_1_to_1000` (`seq` INT(11) NOT NULL,	PRIMARY KEY (`seq`)) ENGINE=InnoDB;

Fill this table with numbers.

INSERT INTO t_seq_1_to_1000(seq) 
SELECT @row := @row + 1 FROM 
(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,
(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, 
(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, 
#(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, 
#(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, 
#(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, 
#(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, 
#(SELECT 0 UNION ALL SELECT 1) t8, 
(SELECT @row:=0) tn;

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

select @num + 2 as daynum, date_format( adddate('2019-1-1', @num:=@num+1), '%Y-%m-%d' ) as date
  from t_seq_1_to_1000, (select @num:=-1) num
 where @num < (select DATEDIFF('2019-12-1', '2019-1-1'))

We are using the t_seq… table just for the rows not for the real values. The real counter is the @num variable itself.
In this query the maximal range is from start date to start date + 1000 days.

This is not the end of the story just a quick thought provoking post.

Saturday, January 12th, 2019 at 11:36.
Filed under database, mysql.