MySQL - Comparing two dates in the WHERE clause

Question

I have a table, links that links two categories (parent & child) together. The table has five fields:

autoinc
parent_category_name year(4) NOT NULL
parent_category_year varchar(255) NOT NULL
child_category_name year(4) NOT NULL
child_category_year varchar(255) NOT NULL

I am trying to write an INSERT SELECT that grabs both the parent & child category_id, and inserts it into a temp table.

INSERT INTO temp (parent_category_id, child_category_id)
SELECT parent.parent_category_id, child.child_category_id
  FROM links
  JOIN categories AS parent
       ON   parent.name = link.parent_category_name
       AND  parent.year = link.parent_category_year
  JOIN categories AS child
       ON   child.name = link.child_category_name
       AND  child.year = link.child_category_year

This query works fine, but I need to apply some business rules. The rules are:

The parent year must be the same as the child year

OR

The parent year must be one year less than the child year

I've added a WHERE clause to my query:

WHERE link.child_category_year = link.parent_category_year
   OR link.child_category_year - link.parent_category_year = 1

When this INSERT statement executes in my Perl code, I get the following exception:

DBI Exception: DBD::mysql::db do failed: BIGINT UNSIGNED value is out of range in '(`my_database`.`links`.`child_category_year` - `my_database`.`links`.`parent_category_year`)' [for Statement "

So, I take it that the INSERT does not like my date subtraction in the WHERE clause. I explored using the DATEDIFF function, but I am not simply looking for a one year difference in dates, but rather one year less on the parent than the child.

How can I accomplish this without the insert error?

Does this answer your question? [BIGINT UNSIGNED VALUE IS out of range My SQL](https://stackoverflow.com/questions/11698613/bigint-unsigned-value-is-out-of-range-my-sql) — FanoFN, Aug 04 '21 at 04:14

score 2 · Answer 1 · answered Aug 04 '21 at 04:44

OR link.child_category_year - link.parent_category_year = 1 may produce negative substraction result. Of course negative value cannot be stored as UNSIGNED.

Simply convert your expression to

OR link.child_category_year = link.parent_category_year + 1

score 1 · Accepted Answer · answered Aug 04 '21 at 11:56

Totally unclear why "year" would be stored as an unsigned bigint. That is way overkill for my understanding of "year".

In any case, why not rephrase the logic to:

WHERE link.parent_category_year = link.child_category_year
      link.parent_category_year = link.child_category_year + 1

In general, you should not have a problem adding 1 to an unsigned value.

score 0 · Answer 3 · answered Aug 04 '21 at 06:58

This is my first time knowing that there's a YEAR datatype and after reading the official documentation I understand that:

MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, and 0000.

Although, it doesn't specifically said that the type is UNSIGNED but the last line in the documentation says:

If strict SQL mode is not enabled, MySQL converts invalid YEAR values to 0000. In strict SQL mode, attempting to insert an invalid YEAR value produces an error.

That means you received the error because one (or more) of the subtracted value in the condition of OR link.child_category_year - link.parent_category_year = 1 is returning negative.

Now, there are a few options you could try:

You can use CAST function and change the year data as SIGNED like :

WHERE link.child_category_year = link.parent_category_year
   OR CAST(link.child_category_year AS SIGNED) - CAST(link.parent_category_year AS SIGNED) = 1

Or you can set NO_UNSIGNED_SUBTRACTION sql_mode and run the query as is:

SET sql_mode='NO_UNSIGNED_SUBTRACTION'

You can also consider changing the YEAR datatype to INTEGER and run the query as is:

ALTER TABLE links 
        MODIFY parent_category_year INT NOT NULL,
        MODIFY child_category_year INT NOT NULL;

MySQL - Comparing two dates in the WHERE clause

3 Answers3