oracle pgsql 对比

直接上比较结果：

一.关于时间函数：

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_var);