0

I am wondering if there is a method to do this by strict SQL query.

Suppose I want to INSERT a series of records into a MySQL table based on a range of dates, but with no corresponding "table" for the dates something like:

INSERT INTO scheduleable( available_date, status ) 
SELECT DATE, 'open' FROM DATES BETWEEN '2017-01-15' AND '2017-05-01'

Only, obviously, DATES is not a real table.

Is something like this possible? The obvious benefit is being able to insert records that have the right day values for months like February and April.

Oliver Williams
  • 5,966
  • 7
  • 36
  • 78
  • 1
    Possible duplicate of [Generating a series of dates](https://stackoverflow.com/questions/14105018/generating-a-series-of-dates) – Jorge Campos Jul 23 '17 at 14:20
  • You can just use the above suggestion with your insert. – Jorge Campos Jul 23 '17 at 14:20
  • 1
    [This one](https://stackoverflow.com/questions/2157282/generate-days-from-date-range) is even better, no need to create any table. – Jorge Campos Jul 23 '17 at 14:22
  • @JorgeCampos, You just mentioned the link till the time I was typing another answer. It is not easy to find out the correct and exact answer from the Ocean of SO. So i was just providing the correct answer. Now you have mentioned the correct link so i have deleted my answer. – Ankit Bajpai Jul 23 '17 at 14:31
  • @AnkitBajpai if you find a correct answer for a question you just flag it as a duplicate (as I did). Don't copy the answer you find (just because you did a better search) in hope to receive points. Doing so you will even teach the OP what he should have searched. – Jorge Campos Jul 23 '17 at 14:33
  • 1
    @JorgeCampos, Flagged it and will remember it for future. – Ankit Bajpai Jul 23 '17 at 14:36
  • A table with 100K dates (1900-01-01 - 2173-10-15) consumes about 2.5MB, is easy to create and very handy for such tasks. – Paul Spiegel Jul 23 '17 at 15:05
  • @PaulSpiegel is it really as much as that? – Strawberry Jul 23 '17 at 23:03
  • 1
    @Strawberry Using [this answer](https://stackoverflow.com/a/9620273/5563083) - 2.52MB for InnoDB or 1.55MB for MyISAM - 100K dates as PK. – Paul Spiegel Jul 24 '17 at 07:40

1 Answers1

0

I used the solution provided here - however, I chose to turn the query given into a VIEW - which is far superior to actually creating a physical table of dates!

However, I was told by MySQL that you can't have a subquery in the 'FROM' clause, so my solution is not completely elegant, but perhaps the "10 list" could be useful for other applications. Here's my solution:

First create this view:

CREATE OR REPLACE VIEW `_v_list_10` AS 
SELECT 0 AS `a` union all select 1 AS `1` union all select 2 AS `2` union all select 3 AS `3` union all select 4 AS `4` 
union all select 5 AS `5` union all select 6 AS `6` union all select 7 AS `7` union all select 8 AS `8` union all select 9 AS `9`;

Then create this view:

CREATE OR REPLACE VIEW _v_dates_1000 AS 
SELECT CURDATE() + INTERVAL 365 DAY - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Date
FROM _v_list_10 AS a
CROSS JOIN _v_list_10 AS b
CROSS JOIN _v_list_10 AS c

NOTE that my date range starts 365 days in the future, and goes back a thousand from there. You could easily build it to 10,000 or 100,000 or etc.

As I have an information database that solely contains near-constant things like state and country names, US counties, etc., this was a good candidate for that database. And, it takes NO SPACE. Hope this helps someone else out!

Oliver Williams
  • 5,966
  • 7
  • 36
  • 78