postgresql,sqlalchemy 高级搜索1
高级搜索之字段重组,“变形”,对比
背景:
许多查询会存在数据库数据类型与查询的不一致(如将时间类型的数据以字符串存在与json中)。此时搜索时将会造成"9:00:00" > "12:00:00"的情况,亦或是将时间拆成多个部分进行存储,此时将会有两部分数据进行联合对比,如果分开将造成数据筛选不准确的情况。此时需要将多部分数据进行拼接构成新的字段进行查询(将字符串转成时间格式进行查询)。以达到筛选准确的目的。
sqlalchemy的高级查询:
使用的内置函数有:
func.concat() ,func.to_timestamp()
函数介绍:
concat(): 将数据里面的多条数据拼接、可自由拼接 eg:
sql.func.concat(OeeMachineLogV2.log_data['MF_BDE_STAT']['bdefile_date'].as_string(), ' ',
OeeMachineLogV2.log_data['MF_LASER_STAT']['processStartTime'].as_string())
注释:将log_data -> 'MF_BDE_STAT' ->> 'bdefile_date' 与 log_data ->'MF_LASER_STAT' ->> 'processStartTime' 拼接并在中间添加一个空格
to_timestamp(): 将字符串转成时间格式,使用方法:
OeeMachineLogV2.log_data->'test_time' = "25.01.2022 12:15:30"
func.to_timestamp(OeeMachineLogV2.log_data['test_time'], "dd.MM.yyy hh24:mi:ss")
注释:将test_time转成标准postgresql时间格式, to_timestamp的第二个参数需要与数据库原字符串格式一致
联合使用:
func.to_timestamp(func.concat(getattr(table.columns, self.column)['MF_BDE_STAT']['bdefile_date'].as_string(), ' ', col.as_string()), "dd.MM.yyy hh24:mi:ss") > "2022-01-25 12:15:00"
postgresql 的高级查询:
使用的内置函数有:
concat_ws(), to_timestamp()
函数介绍:
concat_ws():将数据里面的多个字段进行拼据拼接、可自由拼接 eg:
SELECT concat_ws(' ', log_data->'MF_BDE_STAT'->>'bdefile_date', log_data->'MF_LASER_STAT'->>'processStartTime') FROM "oee_machine_log_v2" WHERE workcenter_id = 15
注释:将oee_machine_log_v2表log_data -> 'MF_BDE_STAT' ->> 'bdefile_date' 与 log_data ->'MF_LASER_STAT' ->> 'processStartTime' 拼接并在中间添加一个空格
效果展示:
to_timestamp(): 将字符串转成时间格式,使用方法eg:
SELECT to_timestamp(concat_ws(' ', log_data->'MF_BDE_STAT'->>'bdefile_date', log_data->'MF_LASER_STAT'->>'processStartTime'), 'dd.MM.yyyy hh24:mi:ss') FROM "oee_machine_log_v2" WHERE workcenter_id = 15
注释:将上图时间进行格式化,格式化字符串的时候,第二个参数需要与字符串的格式一致
效果展示:
联合使用:
SELECT to_timestamp(concat_ws(' ', log_data->'MF_BDE_STAT'->>'bdefile_date', log_data->'MF_LASER_STAT'->>'processStartTime'), 'dd.MM.yyyy hh24:mi:ss') FROM "oee_machine_log_v2" WHERE workcenter_id = 15 and to_timestamp(concat_ws(' ', log_data->'MF_BDE_STAT'->>'bdefile_date', log_data->'MF_LASER_STAT'->>'processStartTime'), 'dd.MM.yyyy hh24:mi:ss') > '2022-01-25 16:00:00'
注释:and后面先进性拼接,然后进行类型转换,再做筛选
效果展示:
番外:
to_date(): 将字符转转为时间的年月日, eg:
SELECT to_date('25.10.2055', 'dd.MM.yyyy')
效果展示:
mysql: 转换函数 func.date_format