How to create a 10 year calendar in MariaDB with 30 minute time slots

Question

First time posting here, I need to create a table in MariaBD which will to the following:

Add dates for the next 10 years for Mon - Sat with 30 minute time slots between 9am and 9pm

Does anyone have an idea how to do this, I have had a few people who deal with SQL but it always fails. The last code used was:

BEGIN
DECLARE @Daily_Start_Time DateTime;
   DECLARE @Daily_End_Time DateTime;
   DECLARE @Increment_In_Mins Int;
   DECLARE @TempDate Datetime;
SET @Daily_Start_Time = '09:00:00'; -- First booking slot for the day
   SET @Daily_End_Time = '16:30:00'; -- Last booking slot for the day
   SET @Increment_In_Mins = 30;
IF ISNULL(@For_Year,0) = 0 THEN
          SET @For_Year = YEAR(DATE_ADD(YEAR,1,NOW()));
   SET @TempDate = CONCAT('01','Jan',CONVERT(VARCHAR,@For_Year),' ',@Daily_Start_Time);

SET @TempDate = CONCAT('01 ', 'Jan ', CAST(@For_Year AS CHAR(4)), ' ',      @Daily_Start_Time);
IF (SELECT COUNT(*) FROM tbl_Calendar WHERE YEAR(CalDateTime) = @For_Year) = 0
BEGIN
WHILE (YEAR(@TempDate) <= @For_Year)
BEGIN
IF NOT DATEPART(dw, @TempDate) = 1 --No Sunday entries
BEGIN
WHILE TIME_FORMAT(@TempDate, '%H:%i:%s') <= TIME_FORMAT(@Daily_End_Time,   '%H:%i:%s')
BEGIN
SELECT CONCAT('Date Is = ', CAST(@TempDate AS CHAR(20)));
SET @TempDate  = DATEADD(MINUTE,@Increment_In_Mins,@TempDate);
END;
SET @TempDate = DATEADD(DAY,1,@TempDate);
SET @TempDate  = CONVERT(VARCHAR(10),@TempDate, 120) + @Daily_Start_Time;
END;
END;
END;
END;

I there are any smart people that can help with this is would be appreciated beyond all words. It is the first time I have tried something like this so please be gentle.

This is a one-off. Did you consider creating the time slots in a spreadsheet, then importing it? — Mike Sherrill 'Cat Recall', Dec 08 '22 at 14:43
Your code looks like SQL server-specific and is not correct for MariaDB. Check your DBMS and its version carefully. Provide complete output for select @@version;. PS. In MariaDB you'd use Sequence Storage Engine for generation. — Akina, Dec 08 '22 at 17:26

score 2 · Answer 1 · answered Dec 08 '22 at 19:46

It's much simpler in MariaDB. Use a sequence-table like this

SELECT '2022-01-01'    -- the start date of your choosing
       + INTERVAL 30*seq MINUTE AS slot
    FROM seq_0_to_200000       -- (I checked, this is plenty)
    WHERE slot <= '2022-01-01' -- same as start
          + INTERVAL 10 YEAR   -- how many years

Try that with LIMIT 10 tacked on so see how it works.

(Or seq_0_to_6000000_step_30 and skip the "30*".)

Then you can CREATE TABLE ... SELECT ... or INSERT INTO ..., etc.

How to create a 10 year calendar in MariaDB with 30 minute time slots

1 Answers1