There is a timezone issue with TiDB partitioned tables, causing data to be inserted into the wrong partition

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

Original topic: tidb分区表存在时区问题,导致insert时候数据落错分区

| username: TI表弟

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] v5.4.0
[Reproduction Path] Create table + Insert data + Query
Create table SQL

CREATE TABLE `history_wti_alarm_test` (
`id` bigint not null,
`alarm_id` varchar(64) NOT NULL  COMMENT 'unique alarm id',
`wti_code` varchar(16) NOT NULL DEFAULT '' COMMENT 'wti code',
`start_time` timestamp(3) NOT NULL,
`end_time` timestamp(3) NULL DEFAULT NULL COMMENT 'Last sample timestamp',
`adc_version_s` varchar(32) DEFAULT NULL COMMENT 'adc software version',
`adc_version_h` varchar(32) DEFAULT NULL COMMENT 'adc hardware version',
`cdc_version_s` varchar(32) DEFAULT NULL COMMENT 'cdc software version',
`cdc_version_h` varchar(32) DEFAULT NULL COMMENT 'cdc hardware version',
`cgw_version_s` varchar(32) DEFAULT NULL COMMENT 'cgw software version',
`cgw_version_h` varchar(32) DEFAULT NULL COMMENT 'cgw hardware version',
`latitude` decimal(9,6) DEFAULT 0.000000 NOT NULL COMMENT 'latitude(0-90) : north (plus), south(minus)',
`longitude` decimal(9,6) DEFAULT 0.000000 NOT NULL COMMENT 'longitude(0-180) : east (plus), west(minus)',
`city_code` char(6) DEFAULT NULL COMMENT 'poi city code',
`model` varchar(32) DEFAULT NULL COMMENT 'vehicle model',
PRIMARY KEY (`alarm_id`,`start_time`),
KEY `idx_start_time` (`start_time`),
KEY `idx_wti_st` (`wti_code`,`start_time`),
) PARTITION BY RANGE(floor(UNIX_TIMESTAMP(start_time))) (
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-10 00:00:00')),
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-20 00:00:00')),
PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-31 00:00:00')),
PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-01 00:00:00')),
PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-10 00:00:00')),
PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-20 00:00:00')),
PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-28 00:00:00')),
PARTITION p9 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-01 00:00:00')),
PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-10 00:00:00')),
PARTITION p11 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-20 00:00:00')),
PARTITION p12 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-31 00:00:00')),
PARTITION p13 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-01 00:00:00')),
PARTITION p14 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-10 00:00:00')),
PARTITION p15 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-20 00:00:00')),
PARTITION p16 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-30 00:00:00')),
PARTITION p17 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-01 00:00:00')),
PARTITION p18 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-10 00:00:00')),
PARTITION p19 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-20 00:00:00')),
PARTITION p20 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-31 00:00:00')),
PARTITION p21 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-01 00:00:00')),
PARTITION p22 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-10 00:00:00')),
PARTITION p23 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-20 00:00:00')),
PARTITION p24 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-30 00:00:00')),
PARTITION p25 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-01 00:00:00')),
PARTITION p26 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-10 00:00:00')),
PARTITION p27 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-20 00:00:00')),
PARTITION p28 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-31 00:00:00')),
PARTITION p29 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-01 00:00:00')),
PARTITION p30 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-10 00:00:00')),
PARTITION p31 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-20 00:00:00')),
PARTITION p32 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-31 00:00:00')),
PARTITION p33 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-01 00:00:00')),
PARTITION p34 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-10 00:00:00')),
PARTITION p35 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-20 00:00:00')),
PARTITION p36 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-30 00:00:00')),
PARTITION p37 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-01 00:00:00')),
PARTITION p38 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-10 00:00:00')),
PARTITION p39 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-20 00:00:00')),
PARTITION p40 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-31 00:00:00')),
PARTITION p41 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-01 00:00:00')),
PARTITION p42 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-10 00:00:00')),
PARTITION p43 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-20 00:00:00')),
PARTITION p44 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-30 00:00:00')),
PARTITION p45 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-01 00:00:00')),
PARTITION p46 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-10 00:00:00')),
PARTITION p47 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-20 00:00:00')),
PARTITION p48 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-31 00:00:00')),
PARTITION p49 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01 00:00:00')),
PARTITION p50 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-10 00:00:00')),
PARTITION p51 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-20 00:00:00')),
PARTITION p52 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-31 00:00:00')),
PARTITION p53 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-01 00:00:00')),
PARTITION p54 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-10 00:00:00')),
PARTITION p55 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-20 00:00:00')),
PARTITION p56 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-28 00:00:00')),
PARTITION p57 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-01 00:00:00')),
PARTITION p58 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-10 00:00:00')),
PARTITION p59 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-20 00:00:00')),
PARTITION p60 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-31 00:00:00')),
PARTITION p61 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-01 00:00:00')),
PARTITION p62 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-10 00:00:00')),
PARTITION p63 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-20 00:00:00')),
PARTITION p64 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-30 00:00:00')),
PARTITION p65 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-01 00:00:00')),
PARTITION p66 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-10 00:00:00')),
PARTITION p67 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-20 00:00:00')),
PARTITION p68 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-31 00:00:00')),
PARTITION p69 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-01 00:00:00')),
PARTITION p70 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-10 00:00:00')),
PARTITION p71 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-20 00:00:00')),
PARTITION p72 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-30 00:00:00')),
PARTITION p73 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-01 00:00:00')),
PARTITION p74 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-10 00:00:00')),
PARTITION p75 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-20 00:00:00')),
PARTITION p76 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-31 00:00:00')),
PARTITION p77 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-01 00:00:00')),
PARTITION p78 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-10 00:00:00')),
PARTITION p79 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-20 00:00:00')),
PARTITION p80 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-31 00:00:00')),
PARTITION p81 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-01 00:00:00')),
PARTITION p82 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-10 00:00:00')),
PARTITION p83 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-20 00:00:00')),
PARTITION p84 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-30 00:00:00')),
PARTITION p85 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-01 00:00:00')),
PARTITION p86 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-10 00:00:00')),
PARTITION p87 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-20 00:00:00')),
PARTITION p88 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-31 00:00:00')),
PARTITION p90 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-01 00:00:00')),
PARTITION p91 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-10 00:00:00')),
PARTITION p92 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-20 00:00:00')),
PARTITION p93 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-30 00:00:00')),
PARTITION p94 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-01 00:00:00')),
PARTITION p95 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-10 00:00:00')),
PARTITION p96 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-20 00:00:00')),
PARTITION p97 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-31 00:00:00')),
PARTITION p1024 VALUES LESS THAN MAXVALUE
);

Insert data

 insert into history_wti_alarm set start_time = "2022-11-10 00:58:59.996",id=1,alarm_id = "abc";
 insert into history_wti_alarm set start_time = "2022-11-10 07:58:59.996",id=1,alarm_id = "abc";

Query

select start_time from history_wti_alarm_test where start_time > "2022-11-10 00:00:00';

[Encountered Problem: Problem Phenomenon and Impact]
The time zone is always UTC.
select start_time from history_wti_alarm_test where start_time > "2022-11-10 00:00:00’;
Cannot query data. The time zone is UTC, and data from 0-8 should fall into p43, but it falls into the adjacent partition p42, resulting in partition pruning and no data being found.
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

| username: xfworld | Original post link

Refer to it yourself. To achieve partition pruning, you can only consider V6.5.X, but partitions do not yet support secondary indexes.

| username: TI表弟 | Original post link

I upgraded from 5.4.0 to 6.1.2 and then to 6.5.0. Does 6.5.0 support it?

| username: TI表弟 | Original post link

I can’t use floor, the timestamp of UNIX_TIMESTAMP is accurate, and from the query phenomenon, it looks like a time zone issue.

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

SELECT @@global.time_zone, @@session.time_zone, @@global.system_time_zone;---Check the execution result
| username: TI表弟 | Original post link

SELECT @@global.time_zone, @@session.time_zone, @@global.system_time_zone;