Differences in Automatic Conversion of Datetime Field Formats Between Version 3.0 and Version 6.1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 3.0版本和6.1版本,在datetime字段的格式自动转化的区别问题

| username: 海石花47

[TiDB Usage Environment] Production Environment
[TiDB Version] Old Environment v3.0, New Environment v6.1
[Reproduction Path]
The following SQL:

SELECT
	* 
FROM
	inquiry_order 
WHERE
	doctor_receive_time >= 20230313

[Encountered Problem: Phenomenon and Impact]
Premise: The table structure is consistent between the old and new environments, and the time field has an index.
The above SQL in the old environment v3.0 uses the index in the execution plan, as follows:

IndexLookUp_10	613.56	root	
├─IndexScan_8	613.56	cop	table:inquiry_order, index:doctor_receive_time, range:[2023-03-13 00:00:00,+inf], keep order:false
└─TableScan_9	613.56	cop	table:inquiry_order, keep order:false

In the new environment v6.1, the execution plan does not use the index (full table scan), as follows:

TableReader_7	4503096.80	root		data:Selection_6
└─Selection_6	4503096.80	cop[tikv]		ge(cast(dwd.inquiry_order.doctor_receive_time, double BINARY), 2.0230313e+07)
  └─TableFullScan_5	5628871.00	cop[tikv]	table:inquiry_order	keep order:false

Question: The table structure is consistent, the field has an index, and the field is in datetime format. Why is the processing method different between the old and new versions?

| username: 海石花47 | Original post link

It looks like the old version automatically converted 20230313 to datetime format and then used the index? Can’t the new version do this?

| username: 海石花47 | Original post link

It feels like 20230313 and ‘2023-03-13’ are two different values??? The amount of data for these two is different.

| username: 海石花47 | Original post link

Isn’t this automatically converted? Do I have to manually use cast(20230313 as date) to convert it?

| username: 海石花47 | Original post link

It seems that only ‘2023-03-13 00:00:00’ will be recognized as such?? If it’s just 20230313, it might be considered a timestamp from a long time ago?? Why is it possible in version 3.0, where in the old version both 20230313000000 and 20230313 could be directly used as conditions with the same result?

| username: Kongdom | Original post link

I tried version v4.0.16 and it can also automatically convert to use indexes.

| username: tidb菜鸟一只 | Original post link

It should be an issue with strings and numbers, right? If you write a string, it will convert the string to datetime type and compare it with the table data. If you write a number, it will convert the character type of the table data to a numeric type for comparison, which is equivalent to an implicit conversion of the field. After the implicit conversion, the index on the field cannot be used. It’s rather strange that version 3.0 can use the index…

| username: 海石花47 | Original post link

The main issue is that after conversion, 20230313 is not 2023-03-13… time > these two values result in different outcomes, which is very confusing.

| username: tidb狂热爱好者 | Original post link

2023 is a number, while a string is enclosed in quotation marks. Strings are automatically converted to time, but numbers are not.

| username: 海石花47 | Original post link

Off-topic, how do I change my custom avatar?

| username: tidb菜鸟一只 | Original post link

Is it the same?

| username: tidb菜鸟一只 | Original post link

In the post, click on your own avatar, then go to Preferences, and then click the edit button next to the avatar image to upload.

| username: WalterWj | Original post link

Please rephrase it and manually format it.

| username: Billmay表妹 | Original post link

Refer to this QA

| username: Kongdom | Original post link

It still feels like a matter of standardization, use simplified notation less :thinking:

| username: 海石花47 | Original post link

No, what I mean is to remove the quotes from ‘20230313’ in the first SQL, making it a numeric type:

time > 20230313
time > ‘2023-03-13’

The results of the above two statements are inconsistent in v6.1, but consistent in v3.0.

| username: 海石花47 | Original post link

Profile picture updated~ Hahaha

| username: 海石花47 | Original post link

I changed it to cast(20230313 as date) and it worked fine. I’m just curious, why is the handling different between the new and old versions?

| username: 海石花47 | Original post link

That’s true… but it seems like the official documentation doesn’t mention it either, so I’m confused.

| username: tidb菜鸟一只 | Original post link

No, the number should be like this. Using a number to associate with the datetime type, the conversion implicitly converts the data in the table from the datetime type to a number. Using a string datetime type, the conversion implicitly converts your condition ‘2023-03-13’ from a string type to a datetime.