MySQL: inserting datetime.date(2023,6,22) into a table

Question

I have an sql file containing, say,

INSERT INTO mytable (ID, Date) VALUES (1, datetime.date(2023, 2, 22));

How do I create a table that will accept this? More explicitly, in

CREATE TABLE mytable(ID INT NOT NULL, Date ???)) ENGINE=INNODB;

what should I put in place of '????'?

When I substitute varchar(12) for ????, I get the error message:

mysql.connector.errors.ProgrammingError: 1630 (42000): FUNCTION datetime.date does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual.

Checking doesn't reveal any insights, at least for me. What should I be doing?

Answer 1

You can directly pass a date in string format (ex. '2023-02-22'), notice the quotes since it should be a string. Also, the data type of the column should simply be a DATE.

Create table: CREATE TABLE mytable(ID INT NOT NULL, Date DATE) ENGINE=INNODB;

Insert values: INSERT INTO mytable (ID, Date) VALUES (1, '2023-02-22');

Answer 2

The error message says that MySQL is interpreting datetime.date(2023, 2, 22) as a function call, which doesn't exist in MySQL, hence the error. This is because you used datetime.date(2023, 2, 22) inside your INSERT INTO statement, and it seems that the MySQL connector is treating it as a function rather than a date value.

CREATE TABLE mytable (
  ID INT NOT NULL,
  Date DATE,
  PRIMARY KEY (ID)
) ENGINE=INNODB;

So ideally you should convert the date objects to the desired MySQL date format and then use that in your INSERT queries.

Answer 3

datetime.date(2023, 2, 22) fails because there is no function date in the datetime schema/database. One "dirty workaround", to avoid doing a mass string replace on your SQL file, is to create a date function in the datetime schema. MySQL will raise a 1585 warning, due to their being a native function with the same name, but it will allow you to create it. This is a lazy version as I have not done any validation on the inputs, but hopefully you get the idea:

CREATE SCHEMA `datetime`;
USE `datetime`;

CREATE FUNCTION `datetime`.`date` (y SMALLINT UNSIGNED, m TINYINT UNSIGNED, d TINYINT UNSIGNED)
  RETURNS DATE DETERMINISTIC
  RETURN DATE(CONCAT_WS('-', y, m, d));

SELECT datetime.date(2023, 2, 22);

A better option would be to modify your SQL file with sed:

sed -i -E "s/datetime.date\(([0-9]{4}), ([0-9]{1,2}), ([0-9]{1,2})\)/'\1-\2-\3'/" your_sql_file.sql

which will rewrite your_sql_file.sql from:

INSERT INTO mytable (ID, Date) VALUES (1, datetime.date(2023, 2, 22));
INSERT INTO mytable (ID, Date) VALUES (1, datetime.date(2023, 3, 23));

to:

INSERT INTO mytable (ID, Date) VALUES (1, '2023-2-22');
INSERT INTO mytable (ID, Date) VALUES (1, '2023-3-23');

Answer 4

Create the schema named datetime and switch to the datetime schema:

CREATE SCHEMA datetime;
USE datetime;

Create the function date in the datetime schema:

DELIMITER //

CREATE FUNCTION date(year_val INT, month_val INT, day_val INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE date_str VARCHAR(10);

    SET date_str = CONCAT(
        year_val,
        '-',
        LPAD(month_val, 2, '0'),
        '-',
        LPAD(day_val, 2, '0')
    );

    RETURN date_str;
END //

DELIMITER ;

So the script to create the mytable table in the datetime schema would be:

CREATE TABLE mytable (
    ID INT NOT NULL,
    `date` DATE,
    PRIMARY KEY (ID)
) ENGINE=INNODB;

and the insert query would be:

INSERT INTO mytable (ID, Date) VALUES (1, datetime.date(2023, 2, 22));