How do I full text search on a datetime column?

Question

I created is a DATETIME column. I also tried to change all Collation's of that table to the same utf8 type but it wouldn't stick. Is it possible to do a FULLTEXT search on a DATETIME column? (%like% is way too slow).

The error I get is:

1283 - Column 'created' cannot be part of FULLTEXT index

-- edit:

$add_24 = $i + 1440;

$format_second = date("Y-m-d H:i", strtotime('-'.$add_24.' minutes'));

$p_price = mysqli_fetch_array(mysqli_query($con, "SELECT created FROM products WHERE created LIKE '$format_second%'"));

Answer 1

FULLTEXT only applies to string datatypes, such as VARCHAR and TEXT. After all, it is looking for "words".

The charset/collation of a table is only the default for newly added columns.

You can ALTER TABLE ... CONVERT TO ... to change all the columns in a table. But before doing so, tell us why you are doing it and whether there is already non-English text anywhere in table. Using that ALTER may just make things worse.

What, in particular, are you searching for in the DATETIME? It may be possible to change to a 'range' query that may run efficiently with an index. Let's see the attempted query and SHOW CREATE TABLE.

More

WHERE created = '2018-01-11 20:49:01'

will check for a specific second

or

WHERE created >= '2018-01-11'
  AND created  < '2018-01-11' + INTERVAL 1 DAY

will check for any created in a specific day.

Etc.

Either of these examples may use an INDEX (not FULLTEXT) containing created. I say "may" because there could be other issues. Please provide the SELECT and SHOW CREATE TABLE.

(The API, such as mysqli, is not relevant to the question.)

Still More

Looking at this formulation...

SELECT  symbol,created
    FROM  products
    WHERE  symbol='".$ccc['symbol']."'
      AND  created LIKE '%".$date_format."%'
    ORDER BY  id DESC
    limit  0,48

I see that INDEX(symbol) is likely to be helpful, but nothing having to do with created is useful. This is because of the leading wildcard on the LIKE, and because an index won't be used with a DATETIME and LIKE. The way to get performance via an index is as discussed above (using a range).

Answer 2

Don't compare DATETIME as a string.

First Rick James, answer is right and should be accepted. You have 3 type categories in MySQL, essentially, you can see them here. (Maybe sometime in the future, you'll have a real JSON type.)

• Numeric
• Date/Time
• String

There are types in each of those. Each of those types has it's own set of operators, and functions. You should always use that types operators and functions. Don't think of a DATETIME, as a string. Think of it as an object that is stringified by default. For your use case,

• Date and Time Type Overview
• Special Attention: Date and Time Functions