《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之五:函数的差异(七)-阿里云开发者社区

开发者社区> 瀚高大李> 正文

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

简介:
+关注继续查看

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"

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Oracle字符乱码、数据越界访问典型Bug分析
作为乙方,在甲方客户那里验收阶段发现两个诡异Bug。以下就问题来源、问题根因、解决方案、如何避免做详细描述。
334 0
差异分析①
加载数据 setwd("D:\\diff") # Reading in count data files
1087 0
理解和使用Oracle分析工具LogMiner
用LogMiner             理解和使用Oracle   8i分析工具LogMiner                 Oracle   LogMiner   是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具,使用该工具可以轻松获得Oracle   重作日志文件(归档日志文件)中的具体内容,特别是,该工具可以分析出所有对于数据库操作的DML(insert、update、delete等)语句,另外还可分析得到一些必要的回滚SQL语句。
1169 0
【我们都爱Paul Hegarty】斯坦福IOS8公开课个人笔记24 popovers弹窗
上几话中我们详细了解了几种segue,我们也了解到了多MVC模式的几种控制器,比如导航、选项卡和分栏,除了这三种多MVC的模式之外,还有一种popover,它跟其他三种不太一样。
837 0
PostGIS学习笔记(开篇)
PostGIS事实上算是笔者开始写博客的第一篇内容。而事实上那篇博文的内容并不丰富,笔者对PostGIS的了解仍然不多,然而17年在OSGeo课程学习时对PostGIS又有了进一步了解,并逐步发现它的强大。刚好最近又遇上一个问题,也使我萌发了开坑PostGIS的想法。
1546 0
差异分析②
样品的无监督聚类 检查基因表达分析最重要的探索性策略之一是多维定标(MDS)图或类似的图。该图以无监督的方式显示了样本之间的相似性和不相似性,以便人们可以了解在进行正式测试之前可以检测差异表达的程度。
865 0
+关注
16
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载