PGSQL常用SQL整理

TO_DATE

select TO_DATE(‘2023-02-15’, ‘yyyy-mm-dd’) - 30

RIGHT、CONCAT

UPDATE bucket_change_task SET plan_change_time =to_timestamp( CONCAT (
‘2023-02-28’,
RIGHT (
to_char(plan_change_time, ‘yyyy-mm-dd HH24:MI:SS’),
9
)
),‘yyyy-mm-dd HH24:MI:SS’)
WHERE to_char(plan_change_time, ‘yyyy-mm-dd’) = ‘2023-02-27’;

UPDATE maintenance_task SET plan_date =to_timestamp( CONCAT (
‘2023-05’,
RIGHT (
to_char(plan_date, ‘yyyy-mm-dd HH24:MI:SS’),
12
)
),‘yyyy-mm-dd HH24:MI:SS’)
WHERE to_char(plan_date, ‘yyyy-mm’) = ‘2023-04’;

to_timestamp

select to_timestamp(‘2022-12-27 00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)-interval ‘185 day’

interval、count、avg、stddev

select count(),avg(rt.value),stddev(rt.value),avg(rt.value)+3stddev(rt.value) as “3segma上限值”,avg(rt.value)-3*stddev(