按时间分区表正确创建方式

1.错误的创建方式

CREATE TABLE TAB
(
	ID INT,
	NAME VARCHAR(10),
	BIRTHDAY DATE
) DISTRIBUTED BY ('ID')
PARTITION BY RANGE (TO_DAYS(BIRTHDAY))
(
PARTITION P2022 VALUES LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD')),
PARTITION P2023 VALUES LESS THAN (TO_DATE('2023-01-01','YYYY-MM-DD')),
PARTITION P2024 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')),
PARTITION PMAX VALUES LESS THAN MAXVALUE
);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your GBase server version for the right syntax to use near ')),
PARTITION P2023 VALUES LESS THAN (TO_DATE('2023-01-01','YYYY-MM-DD')),
PARTI' at line 7

错误原因是因为VALUES不支持日期格式只能支持数值型，解决方式为将日期转换为数字

gbase> select TO_DAYS('2022-01-01'),TO_DAYS('2023-01-01'),TO_DAYS('2024-01-01');
+-----------------------+-----------------------+-----------------------+
| TO_DAYS('2022-01-01') | TO_DAYS('2023-01-01') | TO_DAYS('2024-01-01') |
+-----------------------+-----------------------+-----------------------+
|                738521 |                738886 |                739251 |
+-----------------------+-----------------------+-----------------------+
1 row in set (Elapsed: 00:00:00.00)


CREATE TABLE TAB
(
	ID INT,
	NAME VARCHAR(10),
	BIRTHDAY DATE
) DISTRIBUTED BY ('ID')
PARTITION BY RANGE (TO_DAYS(BIRTHDAY))
(
PARTITION P2022 VALUES LESS THAN (738521),
PARTITION P2023 VALUES LESS THAN (738886),
PARTITION P2024 VALUES LESS THAN (739251),
PARTITION PMAX VALUES LESS THAN MAXVALUE
);


Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.11)

修改后创建成功