探索Holo_java操作日期

1、探索Holo

BEGIN; --事务开始
create table public.dim_terminal_mt (
	"statdate" text NOT NULL,
	"terminal" text NOT NULL,
	"operation_bd_user_id" int4,
	"operation_bd_user_name" text,
	"operation_bd_user_mobile" text,
	"org_level_1_id" int4,
	"org_level_2_id" int4,
	"org_level_1_name" text,
	"org_level_2_name" text,
PRIMARY KEY (statdate,terminal)
)
PARTITION BY LIST (statdate);
--存储类型设置
CALL SET_TABLE_PROPERTY('public.dim_terminal_mt', 'orientation', 'column');
--聚簇索引 Clustering key,建立聚簇索引能够加速用户在索引列上的range和filter查询
--CALL SET_TABLE_PROPERTY('public.dim_terminal_mt', 'clustering_key', 'operation_bd_user_id:asc,org_level_1_id:asc,org_level_2_id:asc');
--报错 ：commit ddl phase1 failed: the index key "operation_bd_user_id" should not be nullable 解决方法：删除聚簇索引
--比特编码列bitmap columns
CALL SET_TABLE_PROPERTY('public.dim_terminal_mt', 'bitmap_columns', 'statdate,terminal,operation_bd_user_name,operation_bd_user_mobile,org_level_1_name,org_level_2_name');
--字典编码列设置
--目前Hologres会默认所有text列都会被隐藏式地设置到bitmap_columns中

--分部键distribution key
--对于有pk的表，其分布键默认就是pk

--数据生命周期管理time_to_live_in_seconds

comment on table public.dim_terminal_mt is 'xxx';
comment on column public.dim_terminal_mt.statdate is '统计日期(yyyyMMddHHmm)';
comment on column public.dim_terminal_mt.terminal is '设备号';
comment on column public.dim_terminal_mt.operation_bd_user_id is '设备归属人ID';
comment on column public.dim_terminal_mt.operation_bd_user_name is '设备归属人姓名';
comment on column public.dim_terminal_mt.operation_bd_user_mobile is '设备归属人电话';
comment on column public.dim_terminal_mt.org_level_1_id is '设备归属人一级组织id';
comment on column public.dim_terminal_mt.org_level_2_id is '设备归属人二级组织id';
comment on column public.dim_terminal_mt.org_level_1_name is '设备归属人一级组织名称';
comment on column public.dim_terminal_mt.org_level_2_name is '设备归属人二级组织名称';
COMMIT; --事务结束

在Hologres中表默认为列存（column store）形式。列存对于OLAP场景较为友好，适合各种复杂查询，行存对于kv场景比较友好，适合基于primary key的点查和扫描scan。这里的场景是维表应该改为行存。

1.1、CREATE TABLE LIKE语句用于创建一个同Select Query结果相同的表

CALL hg_create_table_like('public.dim_terminal_mt_202104081100', 'select * from public.dim_terminal_mt_202104081030');

public.dim_terminal_mt 是分区表，而这种创建方式，不会识别dim_terminal_mt_202104081100是 dim_terminal_mt下的分区表。

1.2、Holo 没有 create table as select 语法

create table public.dim_terminal_mt_202104081100 partition of public.dim_terminal_mt for values in('202104081100') as 
select * from public.dim_terminal_mt_202104081030;

有insert into select 语法，注意dim_terminal_mt_202104081100的分区字段必须是“202104081100”，因为前面创建表已说明分区是“202104081100”，所以不能是其它值，不然会报 执行失败，失败原因：ERROR: internal error: The input violates constraint "Table partition check". Table partition key (statdate)=(202104081030). Where: [query_id:10221254267657614][query_id:10221254267657614]

insert into public.dim_terminal_mt_202104081100 
--select '202104081100',2,2,2,2,2,2,2,2,2,2,2,2,2,2;
select '202104081100',terminal,operation_bd_user_id,operation_bd_user_name,operation_bd_user_mobile
                    ,org_level_1_id,org_level_2_id,org_level_1_name
                    ,org_level_2_name
from public.dim_terminal_mt where statdate='202104081030' limit 10;

2、java操作日期

//1
        //构造特定格式
        SimpleDateFormat from_simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");

        //字符串转换为时间
        Date parse = from_simpleDateFormat.parse("2021-04-08 15:43");

        Calendar c1 = Calendar.getInstance();
        c1.setTime(parse);
        //时间操作
        c1.add(Calendar.MINUTE,30);
        Date time = c1.getTime();
        System.out.println(from_simpleDateFormat.format(time));
//2
        //构造特定格式
        SimpleDateFormat to_simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmm");
        //字符串转换为时间
        Date parse1 = to_simpleDateFormat.parse("202104081530");
        c1.setTime(parse1);
        //时间操作
        c1.add(Calendar.MINUTE,30);
        time = c1.getTime();
        System.out.println(to_simpleDateFormat.format(time));

结果：

2021-04-08 16:13
202104081600