Ignition Postgresql语句查询

时间戳转换

select *,to_timestamp(t_stamp/1000) as t_stampNew from sqlt_data_2_2021_11 order by  t_stamp

select d.tagid,d.floatvalue,to_timestamp(d.t_stamp/1000) as t_stamp,d.dataintegrity  
, t.id,t.tagpath,t.created,t.retired from sqlt_data_3_2020_06 as d,sqlth_te as t 
where d.tagid=t.id and t.id=880
order by t_stamp desc  limit 100

联合多个历史数据表查询

SELECT tagid, intvalue, floatvalue, stringvalue, datevalue, dataintegrity, t_stamp,to_timestamp(t_stamp/1000) as new_t_stamp
FROM (
    SELECT * FROM public.sqlt_data_1_2023_01
    UNION ALL
    SELECT * FROM public.sqlt_data_1_2023_02
  
) AS combined_data where tagid=109

范围查询 tagid in

select d.tagid,d.floatvalue,to_timestamp(d.t_stamp/1000) as t_stamp,d.dataintegrity , t.id,t.tagpath,t.created,t.retired from sqlt_data_3_2021_04 as d,sqlth_te as t where d.tagid=t.id and tagid in (7662,6964) order by t_stamp desc limit 1000

根据tagpath查询历史数据

select d.tagid,d.floatvalue,to_timestamp(d.t_stamp/1000) as t_stamp,d.dataintegrity , t.id,t.tagpath,t.created,t.retired from sqlt_data_3_2021_04 as d,sqlth_te as t where d.tagid= t.id and tagpath = 'item1/sensors/edtlit0201/sensorstatus/value' order by t_stamp desc limit 50000

select to_timestamp(d.t_stamp/1000) as t_stamp,d.floatvalue as upData from sqlt_data_3_2021_04 as d,sqlth_te as t 
where d.tagid=t.id and tagpath='item1/sensors/edtlit0201/sensorstatus/value' 
order by t_stamp desc  limit 1000

select to_timestamp(d.t_stamp/1000) as t_stamp,d.floatvalue as data1 from sqlt_data_3_2021_04 as d,sqlth_te as t 
where d.tagid=t.id and tagpath='item1/sensors/edtlit0201/sensorstatus/value' and to_timestamp(t_stamp/1000)>'2021-04-19 17:56:34 ' and to_timestamp(t_stamp/1000)< ' 2021-04-19 17:56:34  ' order by t_stamp desc

查询记录个数

select t.tagpath ,d.tagid, count(*) cnt from sqlt_data_1_2022_09 as d ,sqlth_te as t  where d.tagid=t.id  group by d.tagid,t.tagpath order by cnt desc;

factor表

postgres数据库

sql ='''SELECT id, "pName", "pN", "pLevel", f1, f2, f3, "qcDate", "qcNo", "qcPeople" FROM public.qc;'''
table = system.db.runQuery(sql)
event.source.parent.getComponent('Chart').InputData=table

sqltie数据库

SELECT datetime(qcDate/1000, 'unixepoch', 'localtime')as qcDate from qc

删除记录，ID从1开始

子增加字段从1开始，但是会删除所有
TRUNCATE qc_factor RESTART IDENTITY;