How to generate a range of dates in MariaDB?

Question

I want to insert the date and day of the week for the whole of 2019-01-01 to 2019-12-31. but I`m unable to.

I try date_format function.

This is my table. table name is food_user and I want to insert the date and day in re_date, re_day 2019-01-01~2019-12-31

INSERT INTO food_user (NAME,re_date,re_day) VALUES ('Alex' , ? , ?);

Answer 1

If you're using MariaDB, try this code to get all dates in year:

SELECT '2019-01-01' + INTERVAL seq DAY FROM seq_0_to_364;

or 

SELECT '2018-12-31' + INTERVAL seq DAY FROM seq_1_to_365;

To undestand this, MariaDB has a built in SEQUENCE Engine - reference: https://mariadb.com/kb/en/library/sequence-storage-engine/ .

Basically typing SELECT seq FROM seq_1_to_10; will return you a number seq from 1 to 10. In this case, a whole year is 365 days hence seq_1_to_365 will return number from 1 to 365. IF you're counting from zero (0), then you'll need to consider the last value as 364, hence changing the sequence to seq_0_to_364 as the example above. From your query, you can do something like this:

INSERT INTO food_user (NAME,re_date,re_day)
SELECT 'Alex','2019-01-01' + INTERVAL seq DAY,DAYNAME('2019-01-01' + INTERVAL seq DAY) FROM seq_0_to_364;

Here's an update, a few months ago I've discovered a way of using recursive statement to generate date ranges. The sequence engine is quite useful but it's only specific to MariaDB. WITH RECURSIVE is supported on both MySQL & MariaDB, although it's also version specific; MySQL from version 8 & MariaDB from version 10.2.2. This is the query:

WITH RECURSIVE date_ranges AS (
SELECT '2019-01-01' dt UNION ALL
SELECT dt + INTERVAL 1 DAY FROM date_ranges 
WHERE dt + INTERVAL 1 DAY <= '2019-12-31')

SELECT dt FROM date_ranges;

Other than that, there's a simple yet important reason why I include this option; it's because of leap years. We had one in 2020 so the total days in that year is actually 366 days instead of 365 days.

Here is an updated fiddle showing the difference using sequence engine and with recursive.

As you can see in the fiddle, the sequence engine query (for leap year) still return 365 rows and the last date of the year is 2020-12-30.. we're missing a day. That is caused by the numbering sequence constraint that we've defined (seq_1_to_365). Therefore, it might not be the best option to use sequence engine unless you're only using it on non-leap years..

Here is the complete query for the INSERT operation according to the question:

INSERT INTO food_user(NAME,re_date,re_day)
/*recursive statement here*/
  WITH RECURSIVE date_ranges AS (
   SELECT '2019-01-01' dt UNION ALL
   SELECT dt + INTERVAL 1 DAY FROM date_ranges 
   WHERE dt + INTERVAL 1 DAY <= '2019-12-31')
/*select statement here*/
SELECT 'Alex',dt, dayname(dt) FROM date_ranges;

Demo fiddle

Answer 2

So I wanted the answer to this question also for a custom date/time range for plotting graph lines and decided to work it out myself.

I didn't go down the seq table route as I needed datetime values and I didn't want the headache of converting a sequence number into various datetime values.

I also wanted it temporary so you don't have to create extra tables to handle it.

So here it is below:

DELIMITER |
BEGIN NOT ATOMIC
    DECLARE `start_time` DATETIME DEFAULT '2024-04-11 14:00:00';
    DECLARE `end_time` DATETIME DEFAULT '2024-04-18 14:00:00';
    DECLARE `inc_interval_mins` INT DEFAULT 60;
    DECLARE `time_value_start` INT DEFAULT 0;
    DECLARE `inc_time_value` INT DEFAULT 4;
    CREATE OR REPLACE TEMPORARY TABLE `datetimes` (a DATETIME, b int);
    WHILE start_time < end_time DO
        SET start_time = start_time + INTERVAL inc_interval_mins MINUTE;
        SET time_value_start = time_value_start + inc_time_value;
        INSERT INTO `datetimes` VALUES (start_time, `time_value_start`);
    END WHILE;
    SELECT * FROM `datetimes`;
END|
DELIMITER ;

Just change the 5 declared variables to match what datetime range you need and you can change the starting value & increment amounts.

I am sure it can be built on to do a lot more - I'll update it if there is demand for it.

EDIT:

So after trying to implement it in Grafana I had to turn it into a Stored Procedure which makes it much more user friendly, so I will share it here:

DELIMITER |
CREATE PROCEDURE `p_graph_quota_line`(
    IN `start_time` DATETIME
    ,IN `end_time` DATETIME
    ,IN `inc_interval` INT
    ,IN `time_value_start` DECIMAL(12,2)
    ,IN `inc_time_value` DECIMAL(10,2)
)
BEGIN
    CREATE OR REPLACE TEMPORARY TABLE `datetimes` (a DATETIME, b int);
    WHILE start_time < end_time DO
        SET start_time = start_time + INTERVAL inc_interval MINUTE;
        SET time_value_start = time_value_start + inc_time_value;
        INSERT INTO `datetimes` VALUES (start_time, `time_value_start`);
    END WHILE;
    SELECT * FROM `datetimes`;
END|

Run that on your Database to create the procedure, then the user needs CREATE TEMPORARY TABLES & EXECUTE permission to call the procedure and you run it like so:

-- start time, end time, increment in minutes, start value, increment value
call p_graph_quota_line('2024-04-11 14:00:00', '2024-04-18 14:00:00', 60, 0, 8);

call p_graph_quota_line('2024-04-11 14:00:00', '2024-04-18 14:00:00', 60, 0, 1.5);

call p_graph_quota_line('2024-04-11 14:00:00', '2024-04-11 14:00:00' + INTERVAL 4 WEEK, 60*24, 0, 50);

"a" "b"
"2024-04-11 15:00:00"   "8.00"
"2024-04-11 16:00:00"   "16.00"
"2024-04-11 17:00:00"   "24.00"
"2024-04-11 18:00:00"   "32.00"
...

"a" "b"
"2024-04-12 14:00:00"   "50.00"
"2024-04-13 14:00:00"   "100.00"
"2024-04-14 14:00:00"   "150.00"
"2024-04-15 14:00:00"   "200.00"
"2024-04-16 14:00:00"   "250.00"
...

The stored procedure method works in Grafana

Answer 3

maybe this can help

insert into food_user (name,re_date,re_day) values ('Alex' , DATE_FORMAT(NOW(), "%Y-%m-%d") , DATE_FORMAT(NOW(), "%W"));

from https://www.w3schools.com/sql/func_mysql_date_format.asp

Answer 4

If you use MySQL, You can try insert into food_user(name, re_date, re_day) values("Hello", "2019-01-01", dayofweek("2019-01-01"));

If you want to insert all days from 2019-01-01 to 2019-12-31, you can use php or any other server side language to insert records.

If you are using Oracle, you can use PL/SQL feature to insert all of them