怎么在postgresql 数据库中使用TimescaleDB 修改分区时间范围
导读:本文共2510字符,通常情况下阅读需要8分钟。同时您也可以点击右侧朗读,来听本文内容。按键盘←(左) →(右) 方向键可以翻页。
摘要: 创建超表 create_hypertable()1.创建普通版表CREATETABLE"超表名"("collect_time"timestamp(6)NOTNULL,"code"varchar(36)COLLATE"pg_catalog"."default"N... ...
目录
(为您整理了一些要点),点击可以直达。创建超表 create_hypertable()
1.创建普通版表
CREATETABLE"超表名"("collect_time"timestamp(6)NOTNULL,"code"varchar(36)COLLATE"pg_catalog"."default"NOTNULL,"value"numeric(14,4)NOTNULL,"create_time"timestamp(6)NOTNULL);
2.改为超表 create_hypertable()
SELECTcreate_hypertable('超表名','collect_time',chunk_time_interval=>INTERVAL'7day');
3.插入数据
INSERTINTO超表名("collect_time","code","value","create_time")VALUES('2020-10-1516:35:00','22255220522','23.4672','2020-10-1416:35:26.659');INSERTINTO超表名("collect_time","code","value","create_time")VALUES('2020-10-1616:35:00','26622569666','0.1085','2020-10-1416:35:27.546');INSERTINTO超表名("collect_time","code","value","create_time")VALUES('2020-10-1316:35:00','525941155555','25.0549','2020-10-1416:35:28.473');INSERTINTO超表名("collect_time","code","value","create_time")VALUES('2020-10-1416:35:00','744445411114','0.0000','2020-10-1416:35:24.01');INSERTINTO超表名("collect_time","code","value","create_time")VALUES('2020-10-1216:35:00','774484457444','0.0000','2020-10-1416:35:23.032');
查看分区,你会发现这些数据在2个分区内
修改分区 set_chunk_time_interval()
一.查看分区情况
1.查看_timescaledb_catalog.dimension 表
SELECT*FROM"_timescaledb_catalog"."dimension"
interval_length上显示 604800000000 (TIMESTAMP类型)意思是一周
2.查看分区块状态
查看 dimension_slice 表
转换时间戳
1602720000000000 2020-10-15 08:00:00
1603324800000000 2020-10-22 08:00:00
这里可以看到分区是7天的
二.修改分区时间 set_chunk_time_interval()
1.修改分区时间
SELECTset_chunk_time_interval('超表名',interval'24hours');
2.插入数据验证
INSERTINTO超表名("collect_time","code","value","create_time")VALUES('2021-1-1416:35:00','375222D001','27.7932','2020-10-1416:35:15.011');INSERTINTO超表名("collect_time","code","value","create_time")VALUES('2021-1-1516:35:00','3715044111','0.0000','2020-10-1416:35:20.389');INSERTINTO超表名("collect_time","code","value","create_time")VALUES('2021-1-1616:35:00','202Q0019QT001','0.3663','2020-10-1416:35:19.087');INSERTINTO超表名("collect_time","code","value","create_time")VALUES('2021-1-1716:35:00','3702000284441','22.2946','2020-10-1416:35:15.035');INSERTINTO超表名("collect_time","code","value","create_time")VALUES('2021-1-1816:35:00','37075225555501','0.3022','2020-10-1416:35:24.041');INSERTINTO超表名("collect_time","code","value","create_time")VALUES('2021-1-1916:35:00','25555222206001','0.0000','2020-10-1416:35:23.956');
三.查看 修改结果
查看_timescaledb_catalog.dimension 表
变成 86400000000 了
2.查看分区
分区也多了
还有第2种(未测试)
我想能不能直接"_timescaledb_catalog".“dimension” 表的 interval_length 字段直接 改为86400000000
</div> <div class="zixun-tj-product adv-bottom"></div> </div> </div> <div class="prve-next-news">
怎么在postgresql 数据库中使用TimescaleDB 修改分区时间范围的详细内容,希望对您有所帮助,信息来源于网络。