时间戳转换
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;