How to create a column in MySQL8 to hold time in 24 hour format

Question

I am trying to INSERT some data into a COLUMN in a MySQL 8 Database. The table is meant to hold an id for a time slot for each hour of each day of the week. So I have an id per hour (tod) for each day.

It is my own database and I am learning as I go.

I cannot work out how to just add the 24-hour clock part into the column

I am new to this so be gentle

Here is my CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS `ctg`.`sections` (
    `id` INT NOT NULL,
    `day_of_week` VARCHAR(10) NULL DEFAULT NULL,
    `tod` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY(`id`))
    ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci

I am propagating data to the table via a very simple python script

    sqlstuff = "INSERT INTO sections(id,day_of_week,tod) VALUES (%s, %s, %s)"
    records = [
        (1,"Sunday",00.00),
        (2,"Sunday",01.00),
        (3,"Sunday",02.00),
        (4,"Sunday",03.00),
        (5,"Sunday",04.00),
        (6,"Sunday",05.00),
        ]

    db.executemany(sqlstuff, records)

    # Ensure we commit the tables
    mydb.commit()

    # Close the database
    db.close()

The Python script runs to 168 ID's. Probably a quicker way but I'm not caring at this point. I have tried tod formatted as '01:00:00' and the above but I keep getting a SQL error stating "Incorrect datetime value.

I have read a lot of the MySQL 8 stuff on time, datetime, timestamp and time_format() but can not work out what I need in my statement to make it insert the 1-hour timeslots I need.

If I need to change the CREATE table statement I can as this is all just on my test setup. I just need some pointers as to what I am blatantly missing.

Answer 1

You can define the column tod's data type to be TIME.
Also the column id can be AUTO_INCREMENT so you don't need to supply a value for it:

CREATE TABLE IF NOT EXISTS `sections` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `day_of_week` VARCHAR(10) NULL DEFAULT NULL,
    `tod` TIME NULL DEFAULT NULL
)ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci

To fill the table with 168 rows, 24 for each day of the week, you need 2 recursive CTEs.
The 1st will return the days of the week and the 2nd all the hours of the day.
These CTEs must be CROSS joined to get all the combinations:

INSERT INTO sections(day_of_week, tod) 
WITH recursive 
  days AS (
    SELECT CURRENT_DATE date 
    UNION ALL
    SELECT date + interval 1 day 
    FROM days
    WHERE date < CURRENT_DATE + interval 6 day 
  ),
  hours AS (
    SELECT '00:00:00' time
    UNION ALL
    SELECT ADDTIME(time, '01:00:00') 
    FROM hours
    WHERE time < '23:00:00' 
  )
SELECT DAYNAME(d.date), h.time
FROM days d CROSS JOIN hours h
ORDER BY DAYOFWEEK(d.date), h.time

See the demo.