《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之五：函数的差异(七)

2021-07-22 23:57:16

PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先是迁移数据，然后就是SQL、存储过程、序列等程序中不同的数据库中数据的使用方式的转换。下面根据自己的理解和测试，写了一些SQL以及数据库对象转换方面的文章，不足之处，尚请多多指教。

1、ADD_MONTHS函数

ADD_MONTHS(date,integer) 是Oracle的一个时间运算函数。返回参数date日期的integer个月后的日期。是Oracle日期运算的一个很基本的函数。
PostgreSQL里面没有这个函数。可以使用Interval数据类型的加减来模拟。返回的是一个timestamp的值。也可以自己创建函数来实现它。

Oracle ADD_MONTHS

SQL> select sysdate  from dual;

SYSDATE
--------------
24-8月 -16

SQL> select add_months(sysdate, 2) from dual;

ADD_MONTHS(SYS
--------------
24-10月-16

SQL> select add_months(sysdate, -2) from dual;

ADD_MONTHS(SYS
--------------
24-6月 -16

SQL> select add_months(to_date('2016/08/01', 'YYYY/MM/DD'), -1) from dual;

ADD_MONTHS(TO_
--------------
01-7月 -16

PostgreSQL ADD_MONTHS

postgres=# select now();
             now
------------------------------
 2016-08-24 09:30:10.87367+08
(1 行记录)

postgres=# select add_months(now(),2);
错误:  函数 add_months(timestamp with time zone, integer) 不存在
第1行select add_months(now(),2);
            ^
提示:  没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select now() + '2 month';
           ?column?
-------------------------------
 2016-10-24 09:30:35.284043+08
(1 行记录)

postgres=# select now() + '-2 month';
           ?column?
------------------------------
 2016-06-24 09:30:53.23845+08
(1 行记录)

postgres=# select to_timestamp('2016-08-01','YYYY-MM-DD') + '-1 month';
        ?column?
------------------------
 2016-07-01 00:00:00+08
(1 行记录)

postgres=# CREATE FUNCTION add_months(date, int)
postgres-#  RETURNS date AS
postgres-#  'SELECT ($1 + ( $2::text || ''months'')::interval)::date;'
postgres-#  LANGUAGE 'sql';
CREATE FUNCTION
postgres=# select add_months(current_date, 2);
 add_months
------------
 2016-10-24
(1 行记录)

postgres=# select add_months(current_date, -2);
 add_months
------------
 2016-06-24
(1 行记录)

postgres=# select add_months('2016/08/01', -1);
 add_months
------------
 2016-07-01
(1 行记录)

2、LAST_DAY 函数

LAST_DAY(date)函数是Oracle的一个取日期的月末日的函数。
PostgreSQL中没有对应的日期函数。可以自己使用Interval来实现。也可以自己创建函数来实现。

Oracle LAST_DAY

SQL> select sysdate from dual;

SYSDATE
--------------
24-8月 -16

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDA
--------------
31-8月 -16

SQL> select last_day(to_date('2016/09/16', 'YYYY/MM/DD')) from dual;

LAST_DAY(TO_DA
--------------
30-9月 -16

PostgreSQL LAST_DAY

postgres=# CREATE FUNCTION LAST_DAY(DATE)
postgres-#  RETURNS DATE AS
postgres-#          'SELECT date(substr(text($1::timestamp +
postgres'#                  ''1 month''),1,7)||''-01'')-1'
postgres-#          LANGUAGE 'sql';
CREATE FUNCTION
postgres=# select current_date;
    date
------------
 2016-08-24
(1 行记录)

postgres=# select last_day(current_date);
  last_day
------------
 2016-08-31
(1 行记录)

postgres=# select last_day('2016/09/16'::date);
  last_day
------------
 2016-09-30
(1 行记录)

3、MONTHS _BETWEEN函数

MONTHS _BETWEEN函数是Oracle的一个判断两个日期之间有几个月的间隔的函数。
PostgreSQL中也没有对应的函数，只能自己通过函数实现。不过由于对于相差月份的算法不一样，结果可能有些差异。下面给出一个例子。

Oracle MONTHS _BETWEEN

SQL> SELECT MONTHS_BETWEEN(TO_DATE('02-02-1995','MM-DD-YYYY'),
  2   TO_DATE('01-01-1995','MM-DD-YYYY')) MONTHS from dual;

    MONTHS
----------
1.03225806

SQL> SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('01-01-2014','MM-DD-YYYY')) MONTHS from dual;

    MONTHS
----------
31.7571192

PostgreSQL MONTHS _BETWEEN

postgres=# CREATE FUNCTION MONTHS_BETWEEN(DATE,DATE)
postgres-#  RETURNS NUMERIC AS
postgres-#          'SELECT to_number(text(date($1)-
postgres'#                             date($2)),''999999999'')/31'
postgres-#          LANGUAGE 'sql';
CREATE FUNCTION
postgres=# SELECT MONTHS_BETWEEN('1995-02-02'::date,'1995-01-01'::date);
   months_between
--------------------
 1.0322580645161290
(1 行记录)

postgres=# SELECT MONTHS_BETWEEN(CURRENT_DATE, '2014-01-01'::DATE) MONTHS;
       months
---------------------
 31.1612903225806452
(1 行记录)

4、EXTRACT函数

EXTRACT是Oracle用于从一个date、timestamp或者interval类型中截取到特定的部分所用的函数。Oracle只支持截取年、月、日、时、分、秒，时区时，时区分、时区等。
PostgreSQL也支持该函数，截取的东西比Oracle的多一些，但是不支持时区时，时区分和时区的截取（文档中说是支持的，但是我的实际测试中不支持）。

Oracle EXTRACT

SQL> select extract(year from date'2011-05-17') year from dual;

      YEAR
----------
      2011

SQL> select extract(month from date'2011-05-17')  from dual;

EXTRACT(MONTHFROMDATE'2011-05-17')
----------------------------------
                                 5

SQL> select extract(day from date'2011-05-17')  from dual;

EXTRACT(DAYFROMDATE'2011-05-17')
--------------------------------
                              17

SQL> select extract(hour from timestamp'2011-05-17 12:56:20.1234') AAA from dual;

       AAA
----------
        12

SQL> select extract(minute from timestamp'2011-05-17 12:56:20.1234') part from dual;

      PART
----------
        56

SQL> select extract(second from timestamp'2011-05-17 12:56:20.1234') part from dual;

      PART
----------
   20.1234

SQL> select extract(timezone_region from systimestamp) part from dual;

PART
----------------------------------------------------------------
UNKNOWN

SQL> select extract(timezone_abbr from systimestamp) part from dual;

PART
----------
UNK

SQL> select extract(timezone_hour from systimestamp) part from dual;

      PART
----------
         8

SQL> select extract(timezone_minute from systimestamp) part from dual;

      PART
----------
         0

PostgreSQL EXTRACT

postgres=# select extract(year from date'2011-05-17') part;
 part
------
 2011
(1 行记录)

postgres=# select extract(month from date'2011-05-17') part;
 part
------
    5
(1 行记录)

postgres=# select extract(day from date'2011-05-17') part;
 part
------
   17
(1 行记录)

postgres=# select extract(week from date'2011-05-17') part;
 part
------
   20
(1 行记录)

postgres=# select extract(century from date'2011-05-17') part;
 part
------
   21
(1 行记录)

postgres=# select extract(hour from '2011-05-17 12:56:20.1234'::timestamp) AAA;
 aaa
-----
  12
(1 行记录)

postgres=# select extract(minute from '2011-05-17 12:56:20.1234'::timestamp) AAA;
 aaa
-----
  56
(1 行记录)

postgres=# select extract(second from '2011-05-17 12:56:20.1234'::timestamp) AAA;
   aaa
---------
 20.1234
(1 行记录)

postgres=# select extract(microsecond from '2011-05-17 12:56:20.1234'::timestamp) AAA;
   aaa
----------
 20123400
(1 行记录)

postgres=# select extract(isodow from '2011-05-17 12:56:20.1234'::timestamp) AAA;
 aaa
-----
   2
(1 行记录)

postgres=# select extract(quarter from '2011-05-17 12:56:20.1234'::timestamp) AAA;
 aaa
-----
   2
(1 行记录)

postgres=# select extract(timezone  from '2011-05-17 12:56:20.1234'::timestamp) AAA;
错误:  不支持时间戳单位 "timezone"

postgres=# select extract(timezone_hour from '2011-05-17 12:56:20.1234'::timestamp) AAA;
错误:  不支持时间戳单位 "timezone_hour"

码农公寓