tidb 对应时间点的数据在所在时间范围内查询不到

xfworld · 2022 年12 月 2 日 08:34

select floor(UNIX_TIMESTAMP(“2022-11-10 00:59:59.996”) * 1000)

放大就可以了，按照你的数据时间精度，必须放大，你按这个重建下结构… 就应该可以了

Billdi表弟 · 2022 年12 月 2 日 08:35

有道理呀

Billdi表弟 · 2022 年12 月 2 日 08:39

分析步骤:

SELECT * FROM employees PARTITION (p1) where 查看数据到底在哪一个分区，定位到是真的分区问题.
有可能浮点数精度问题。

感谢大佬们，特别是xfworld大佬的支持

Billdi表弟 · 2023 年2 月 11 日 09:04

大佬看起来是时区的原因导致分区落错的，八点前和八点后的分区不一样，建表的sql里面按照utc来的，查询的时候按照上海时间来的

@xfworld @我是咖啡哥 @裤衩儿飞上天求救

考试没答案 · 2023 年2 月 13 日 02:09

要是UTC 您的这个时间戳是不是也得改改。

考试没答案 · 2023 年2 月 13 日 02:20

mongo的时间类型会直接把时间转换成iso时间也就是UTC时间。所有的操作都必须关注下时间。不然怎么都操作不成功。

假如一：
对于UTC时间的时区？对于字符串类型的时间是不是会自动将时间 -8小时呢？？。

考试没答案 · 2023 年2 月 13 日 02:21

select start_time from history_wti_alarm_test where start_time=‘2022-11-10 00:58:59.996’;

帮忙查询下这个数据能查询出来不？？

Billdi表弟 · 2023 年2 月 13 日 02:32

select start_time from history_wti_alarm_test where start_time=‘2022-11-10 00:58:59.996’ 也查不出来

Billdi表弟 · 2023 年2 月 13 日 02:48

Billdi表弟:

CREATE TABLE `history_wti_alarm` (
  `id` bigint not null,
  `alarm_id` varchar(64) NOT NULL  COMMENT 'unique alarm id',
  `start_time` timestamp(3) NOT NULL,
  `end_time` timestamp(3) NULL DEFAULT NULL COMMENT 'Last sample timestamp',
  `
  PRIMARY KEY (`alarm_id`,`start_time`),
  UNIQUE KEY `uniq_alarm_id` (`alarm_id`,`start_time`),
  KEY `idx_start_time` (`start_time`),
  KEY `idx_wti_st` (`wti_code`,`start_time`),
  KEY `idx_end_time` (`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
);

删除p42之后，可以查找到了

考试没答案 · 2023 年2 月 13 日 03:12

时间都是东八区，查询和展示都没问题吧。

Billdi表弟 · 2023 年2 月 13 日 03:16

数据库里面的是utc的时区

考试没答案 · 2023 年2 月 13 日 04:21

假如：您看到的时间和insert的时间都是insert时间。但是您在DDL创建表的时候使用了UNIX_TIMESTAMP.转换成了时间戳，就是数字。

Billdi表弟 · 2023 年2 月 13 日 04:42

ddl内的转换的时间戳从建表语句上的整型验证了是正确的utc时区转换的，现在怀疑insert有bug导致数据入错时区了。

Billdi表弟 · 2023 年2 月 13 日 04:44

这和现象删除了p42数据又回到了p43时区是吻合的，稍后我验证下其他分区上是否存在这个问题我快哭了

Billdi表弟 · 2023 年2 月 13 日 06:11

在每一个分区上都有这个问题

考试没答案 · 2023 年2 月 13 日 06:35

这就确定是时区的问题了。业务问题说下？？？

考试没答案 · 2023 年2 月 14 日 02:17

您看下这个有问题没？？

考试没答案 · 2023 年2 月 14 日 02:18

我使用的也是UTC 的时间

Billdi表弟 · 2023 年2 月 14 日 02:42

大佬你好。我大概看了下tidb的源码，有不少地方时区采用的是system