困った

JSON型に突っ込んだDATE型形式のデータと、クエリで突っ込んだDATETIME型のデータを比較したらドツボにはまりました。

結論

MySQL

> select "2018-07-01" > "2018-06-30 23:59:59.999999";
→true

> select "2018-07-01" = "2018-07-01 00:00:00.000000";
→false

> select "2018-07-01" < "2018-07-01 00:00:00.000000";
→true

DATETIME型の昨日<DATE型の今日<DATETIME型の今日ってなってる！！

DATE型→DATETIME型変換って00:00:00付与じゃないの！？
DATETIME型→DATE型変換ってミリ秒を四捨五入して日付を取得するんじゃないの！？

参考:https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-conversion.html

11.3.7 Conversion Between Date and Time Types
To some extent, you can convert a value from one temporal type to another. However, there may be some alteration of the value or loss of information. In all cases, conversion between temporal types is subject to the range of valid values for the resulting type. For example, although DATE, DATETIME, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. TIMESTAMP values cannot be earlier than 1970 UTC or later than '2038-01-19 03:14:07' UTC. This means that a date such as '1968-01-01', while valid as a DATE or DATETIME value, is not valid as a TIMESTAMP value and is converted to 0.

Conversion of DATE values:

Conversion to a DATETIME or TIMESTAMP value adds a time part of '00:00:00' because the DATE value contains no time information.

Conversion to a TIME value is not useful; the result is '00:00:00'.

Conversion of DATETIME and TIMESTAMP values:

Conversion to a DATE value takes fractional seconds into account and rounds the time part. For example, '1999-12-31 23:59:59.499' becomes '1999-12-31', whereas '1999-12-31 23:59:59.500' becomes '2000-01-01'.

Conversion to a TIME value discards the date part because the TIME type contains no date information.

最後に

これって常識？？