With the assistance of ChatGPT we have produced the following code:
Working days: Monday to Friday, Saturday morning
Working Hours 08:00 – 11:00, 13:00 – 16:00
Saturday 08:00 – 11:00
Create Tables
Before we can populate the tables, we must create them:
CREATE TABLE time_dimwnsion (
id INT AUTO_INCREMENT PRIMARY KEY,
slot_date DATE NOT NULL,
slot_start_time TIME NOT NULL,
slot_end_time TIME NOT NULL,
is_working_day BOOLEAN NOT NULL,
is_working_hour BOOLEAN NOT NULL,
day_of_week VARCHAR(10),
week_of_year INT,
month INT,
year INT
);
Create Indexes
No table is complete without judicious indexes:
ALTER TABLE time_dimension
ADD INDEX idx_slot_date (slot_date),
ADD INDEX idx_is_working_day (is_working_day),
ADD INDEX idx_day_of_week (day_of_week);
It should be noted that last time around, the AI only suggested 2 indexes so table may need to be updated.
— Set the starting point:
— Use 1 hour after the latest slot_datetime in the table,
— or default to the current date/time (rounded to the hour) if the table is empty.
SET @start := (
SELECT IFNULL(MAX(slot_datetime), DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:00:00’)) + INTERVAL 1 HOUR
FROM time_dimension
);
— Initialise a counter for generating sequential hourly offsets (0 to 167)
SET @row := -1;
— Insert 168 hourly time slots (7 days’ worth) into the time_dimension table
INSERT INTO time_dimension (
slot_datetime, — The exact datetime the slot begins
slot_end_time, — 1 hour after slot_datetime
slot_date, — Date part only, for filtering/grouping
slot_hour, — Hour of the day (0–23)
day_of_week, — Name of the day (e.g. Monday)
is_working_day, — 1 if Mon–Sat, 0 for Sunday
is_working_hour, — 1 for hours between 8–12 and 13–15 on Mon–Sat
slot_label — A user-friendly label like Mon-08
)
SELECT
dt AS slot_datetime,
DATE_ADD(dt, INTERVAL 1 HOUR) AS slot_end_time,
DATE(dt) AS slot_date,
HOUR(dt) AS slot_hour,
DAYNAME(dt) AS day_of_week,
CASE
WHEN DAYOFWEEK(dt) BETWEEN 2 AND 7 THEN 1 — Monday (2) to Saturday (7)
ELSE 0
END AS is_working_day,
CASE
WHEN DAYOFWEEK(dt) BETWEEN 2 AND 6 AND HOUR(dt) BETWEEN 8 AND 11 THEN 1 — Morning block
WHEN DAYOFWEEK(dt) BETWEEN 2 AND 6 AND HOUR(dt) BETWEEN 13 AND 15 THEN 1 — Afternoon block
ELSE 0
END AS is_working_hour,
CONCAT(LEFT(DAYNAME(dt), 3), ‘-‘, LPAD(HOUR(dt), 2, ‘0’)) AS slot_label
FROM (
— Generate 168 sequential hourly datetimes starting from @start
SELECT DATE_ADD(@start, INTERVAL seq HOUR) AS dt
FROM (
— Generate a sequence from 0 to 167 using cross join (10 x 17 = 170 rows)
SELECT @row := @row + 1 AS seq
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) t1,
(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
UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
UNION ALL SELECT 15 UNION ALL SELECT 16) t2
LIMIT 168 — Limit to one full week (7 days x 24 hours)
) AS hours
) AS time;
This code produces a record for every hour in the period, currently a week. Marks each hour as a working_day and / or a working hour
I have been impressed with the accuracy of AI generated code. It didn’t work first time but a little debugging with AI assistance soon redolved the teething problems,
