1.PostgreSql 查询一个月内的天数
SELECT to_char( generate_series(date_trunc('month',now())+ interval '-1 month',
date_trunc('month',now())+ interval '-1 day', '1 day'),'yyyy-mm-dd');
SELECT to_char( generate_series( to_timestamp((to_char((now() + interval '-1 month'), 'YYYY-MM-01')), 'YYYY-MM-DD 00:00:00'), date_trunc('month',now())+ interval '-1 day', '1 day'),'yyyy-mm-dd');
-- 根据当前时间查询当月天数数据 毫秒时间戳需除以1000 转成北京时间
select date_trunc('month',to_timestamp(1680918153389/1000) AT TIME ZONE 'UTC-8');
select date_trunc('month',to_timestamp(1680918153389/1000) AT TIME ZONE 'UTC-8')+ interval '1 month -1 day';
select to_char( generate_series(date_trunc('month',to_timestamp(1680918153389/1000) AT TIME ZONE 'UTC-8'),
date_trunc('month',to_timestamp(1680918153389/1000) AT TIME ZONE 'UTC-8')+ interval '1 month -1 day', '1 day'),'yyyy-mm-dd') days;
2.PG执行脚本
do
$$
DECLARE
unid varchar;
newId varchar;
date timestamp;
icount numeric;
cur_list CURSOR FOR
select id
from f_and_tpm a
where created_by = 'shw000';
BEGIN
date = now();
OPEN cur_list;
LOOP
FETCH cur_list INTO unid;
EXIT WHEN NOT FOUND;
/*select count(1)
into icount
from f_and_tpm
where id = unid;
IF (icount > 1)
THEN
CONTINUE ;
end if;*/
newId = gen_random_uuid();
update f_and_tpm
set id=newId,
created_time=date,
--created_by='shw',
updated_time=date,
updated_by='shw'
where id = unid;
--插入数据
INSERT INTO public.f_and_tpm_class_correlation (id, created_by, created_time, delete_flag, updated_by,
updated_time, equipment_id, remark, tpm_class_id, tpm_id)
VALUES (gen_random_uuid(), 'shw000', date, 0, 'shw000',
date, null, null, 'ff8080818726933d0187272f21d902df',
newId);
END LOOP;
CLOSE cur_list;
END;
$$
3.分组排序取最新数据
select * from (
select *,row_number() over (partition by code order by created_time desc) rt from f_sys_user) as t
where t.rt=1
4.PG删除多表关联数据(比较少用,记录下以便回忆)
delete from f_qms_process_quality a using (
SELECT C.* FROM f_prm_product_route A ,f_mdm_factory B ,f_prm_route_step C
WHERE A.inner_product_no='6D3' AND A.part_type='MC'
AND A.org_id =B.ID and b.CODE='DC'
and c.route_id=a.unid AND a.delete_flag=0 AND b.delete_flag=0 AND c.delete_flag=0
) t where a.process_id=t.unid;
5.查询当前时间所在第几周
select 1 + date_part('week', dt) - date_part('week', dt - ((date_part('day', dt) - 1) || ' day')::interval)
from (values (now())) data(dt);
PG多表关联更新数据
update f_weekly_maintenance a set serial_number=t.rn
from (select id,to_char(week_start_date,'yyyymm')||'0'||row_number() over (partition by months order by weeks ) rn
from f_weekly_maintenance ) as t
where t.id= a.id