Elastic Search同步postgre数据时候，遇见的时间类型的问题。

淡淡的烟草味﹌ 2024-02-19 13:09 15阅读 0赞

由于现在的需求，postgre进行全文检索的复杂性，时效性，和匹配不准确性，采用elastic search来进行全文检索。

首先是同步数据，在同步数据过程中，之前created\_at字段是timestamp类型，导致死活同步不过去，

第一版是同步为text类型，但是我们要进行时间的检索，text格式的时间就不能满足要求，放弃。

第二版是查阅资料，得知es的date格式默认是'yyyy-MM-dd'T'HH:mm:ssZ'，我是采取的postgre外部表的方式同步数据的（不了解的同学可以搜搜资料。，我这里不再多说），建立外部表的时候，原先的created\_at要设置成text类型。![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhb2x2Ymln_size_16_color_FFFFFF_t_70][]

然后创建索引的时候字段按照图中设计，type是“date”,format是'yyyy-MM-dd'T'HH:mm:ssZ',

{
        "mappings": {
            "internalnews": {
                "properties": {
                    "title": {
                        "type": "text",
    					"analyzer": "ik_max_word"
                    },
                    "url": {
                        "type": "text"
                    },
                    "news_id": {
                        "type": "keyword"
                    },
                    "content": {
                        "type": "text",
    					"analyzer": "ik_max_word"
                    },
                    "author": {
                        "type": "text"
                    },
                    "u_datetime": {
                        "type": "date",
                        "format": "yyyy-MM-dd'T'HH:mm:ssZ"
                    },
                    "publish_time": {
                        "type": "text"
                    },
                    "read_num": {
                        "type": "long"
                    },
                    "comment_num": {
                        "type": "long"
                    },
                    "created_at": {
                        "type": "date",
                        "format": "yyyy-MM-dd'T'HH:mm:ssZ"
                    }
                }
            }
        }
    }

然后写了一个触发器，原表新增的时候给外部表进行新增，函数中的内容是：要按照之前创建索引的格式进行格式化数据，to\_char(NEW.u\_datetime,'YYYY-MM-DD"T"HH24:MI:SSZ')

CREATE OR REPLACE FUNCTION "public"."index_intetnal_news"()
      RETURNS "pg_catalog"."trigger" AS $BODY$
        BEGIN
    	
            INSERT INTO internal_news_es (id, title, url, news_id, content,author,u_datetime,read_num,comment_num,created_at) VALUES
                (NEW.id, NEW.title, NEW.url, NEW.news_id, NEW.content,NEW.author,to_char(NEW.u_datetime,'YYYY-MM-DD"T"HH24:MI:SSZ'),
    						NEW.read_num,NEW.comment_num,to_char(NEW.created_at,'YYYY-MM-DD"T"HH24:MI:SSZ'));
            RETURN NEW;
        
        END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100

这样数据是能同步过去，也是时间格式，但是在es数据中存储的就是这种时间格式，所以在检索时候，java代码中也要对界面传过来的时间参数进行这种格式化。

第三版偶然看到：在创建index的时候，可以指定多个date的format格式，

"u_datetime": {
                        "type": "date",
                        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd'T'HH:mm:ssZ"
                    },

这样，在向外部表插入数据的时候，无论是之前的**to\_char(NEW.created\_at,'YYYY-MM-DD"T"HH24:MI:SSZ')**，还是我新写的**to\_char(NEW.created\_at,'YYYY-MM-DD HH24:MI:SS')都可以。**

boolQueryBuilder.must(QueryBuilders.rangeQuery("created_at").gte(startTime).lte(endTime));

而且在写接口进行检索时间的时候也不用在对时间格式进行转化了。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhb2x2Ymln_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8ed4089ad86e4a01b09a6be1703a131a.png