直接上比较结果：
一.关于时间函数：
1.上月的今天：
oracle
SELECT add_months(sysdate,-1) from DUAL
pgsql
select (date_trunc('day', current_date - interval '0' day) + INTERVAL '-1 MONTH') lasttoday
上月今天的当前时刻
select to_char(now() + INTERVAL '-1 MONTH', 'YYYY-MM-DD HH24:MI:SS') lasttoday
2.获取上个月的昨天
oracle
SELECT add_months(trunc(sysdate,'dd'),-1)-1 from DUAL
pgsql
select (date_trunc('day', current_date - interval '1' day) + INTERVAL '-1 MONTH') lastMonthfortoday
trunc(add_months(sysdate,-1), 'dd') -》select (current_date + INTERVAL '-1 MONTH')ORALCE 0-23个小时时段
select to_char((trunc(sysdate,'dd') + (rownum - 1) / 24 ) ,'hh24') times
from dual
connect by level <= 24
pgsql：
with recursive rs as (
select 0 as level
union all
select (rs.level + 1) as level from rs where rs.level < 23
)
select case when length((rs.level::text)) = 1 then '0'||(rs.level::text) else (rs.level::text) end as times from rs;
时间计算：
oracle ： select (SYSDATE - to_date('2020-06-11 12:30:50', 'yyyy-mm-dd hh24:mi:ss'))*24*60*60 from dual
等价于：
pgsql：select EXTRACT(EPOCH FROM(current_timestamp - TIMESTAMP '2020-06-11 12:30:50'))；
pg样例：
SELECT EXTRACT(EPOCH FROM (TIMESTAMP '2017-12-16 12:21:13'- TIMESTAMP '2017-12-15 12:21:00'));
去掉换行符 回车符
oracle translate(customermem,chr(13)||chr(10),' ') 去掉换行符 回车符
pg: select regexp_replace(fieldname, E'[\\n\\r]+', ' ', 'g' )
pg 数据库 计算月份差
SELECT (DATE_PART('year', date_trunc('month',current_date)::DATE) -
DATE_PART('year', to_char(to_date('202303'||'01','yyyymmdd'),'yyyy-mm-dd') ::date)) * 12 +
(DATE_PART('month', date_trunc('month',current_date)::DATE) -
DATE_PART('month', to_char(to_date('202303'||'01','yyyymmdd'),'yyyy-mm-dd') ::date))
pg 存过执行创建动态表
declare
out_var integer;
select to_char(now(), 'yyyymmdd'):: integer into out_var;
execute format('create table if not exists phone_number_arrears_sum_%s (like phone_number_arrears_sum)', out_va
r);