How can I generate days from the range of dates with the help of MySQL views?

To illustrate it we are creating the following views −

mysql> CREATE VIEW digits AS
    -> SELECT 0 AS digit 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;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE VIEW numbers AS SELECT ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number FROM digits as ones, digits as tens, digits as hundreds, digits as thousands;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE VIEW dates1 AS SELECT SUBDATE(CURRENT_DATE(), number) AS date FROM numbers UNION ALL SELECT ADDDATE(CURRENT_DATE(), number + 1) AS date FROM numbers;
Query OK, 0 rows affected (0.09 sec)

mysql> Select date from dates1 where date between '2017-11-15' and '2017-11-30'order by date;
+------------+
| date       |
+------------+
| 2017-11-15 |
| 2017-11-16 |
| 2017-11-17 |
| 2017-11-18 |
| 2017-11-19 |
| 2017-11-20 |
| 2017-11-21 |
| 2017-11-22 |
| 2017-11-23 |
| 2017-11-24 |
| 2017-11-25 |
| 2017-11-26 |
| 2017-11-27 |
| 2017-11-28 |
| 2017-11-29 |
| 2017-11-30 |
+------------+
16 rows in set (0.05 sec)

karthikeya Boyini

Updated on 22-Jun-2020 14:11:56

• Related Questions & Answers
• How do I generate days from the range of dates in MySQL?
• How can I count the number of days in MySQL?
• How can we know about the starting range of TIMESTAMP data type with the help of MySQL FROM_UNIXTIME() function?
• How can we filter data with the help of MySQL subquery?
• How can I calculate full 24hour days between two specified dates in MySQL?
• How do I get the number of days between two dates in JavaScript?
• How can we display all the records from MySQL table with the help of PHP script?
• How can INTERSECTION between tables be implemented with the help of MySQL joins?
• How DIFFERENCES between tables can be implemented with the help of MySQL joins?
• Which MySQL query can be used with the help of which we can see the list of MySQL databases?
• Python Pandas - Generate dates in a range
• How do I calculate number of days between two dates using Python?
• With the help of function, how can we return the difference in Year, Month and Days between two date values?
• How can we subtract values in MySQL table with the help of LEFT JOIN?
• How can we display all the records from MySQL table with the help of PHP script that uses mysql_fetch_assoc() function?