MONTHS_BETWEEN ORACLE -> POSTGRESQL 변경하기

oracle

 

select MONTHS_BETWEEN(TO_DATE('20190401','YYYYMMDD'),TO_DATE('20190101','YYYYMMDD')) FROM dual;  

 

 

 

 

postgresql 

 

SELECT EXTRACT(YEAR FROM AGE(TIMESTAMP '2013-12-11', TIMESTAMP '2010-09-17')) * 12 +
              EXTRACT(MONTH FROM AGE(TIMESTAMP '2013-12-11', TIMESTAMP '2010-09-17'));

 

 

출처 :http://www.sqlines.com/postgresql-to-oracle/get_interval_in_months

Get Interval In Months - PostgreSQL to Oracle Migration - SQLines Open Source Tools

 

 

 

In PostgreSQL, you can use EXTRACT and AGE functions to get the interval between 2 timestamps in months.

PostgreSQL:

-- AGE function returns year-month-day interval between 2 timestamps SELECT AGE(TIMESTAMP '2013-03-11 00:00:00', TIMESTAMP '2010-09-17 00:00:00'); # 2 years 5 mons 24 days

Now you can extract years, multiple by 12 and extract months to get the interval in months:

-- Get interval in months SELECT EXTRACT(YEAR FROM AGE(TIMESTAMP '2013-03-11', TIMESTAMP '2010-09-17')) * 12 + EXTRACT(MONTH FROM AGE(TIMESTAMP '2013-03-11', TIMESTAMP '2010-09-17')); # 29

Using EXTRACT in Oracle

In Oracle you can use datetime arithmetic subtraction operator - instead of AGE function to get the interval between 2 timestamps:

Oracle:

-- Subtraction operator - returns interval (DAY TO SECOND by default) SELECT TIMESTAMP '2013-03-11 00:00:00' - TIMESTAMP '2010-09-17 00:00:00' FROM dual; # +000000906 00:00:00.000000000   -- Convert to YEAR TO MONTH interval SELECT (TIMESTAMP '2013-03-11 00:00:00' - TIMESTAMP '2010-09-17 00:00:00') YEAR TO MONTH FROM dual; # +02-06

Note that the interval was rounded to 2 years 6 months when converted to YEAR TO MONTH interval in Oracle, while the actual interval is 2 years, 5 months and 24 days. 

Using EXTRACT function you can get interval between 2 timestamps in months:

-- Get interval in months SELECT EXTRACT(YEAR FROM (TIMESTAMP '2013-03-11 00:00:00' - TIMESTAMP '2010-09-17 00:00:00') YEAR TO MONTH) * 12 + EXTRACT(MONTH FROM (TIMESTAMP '2013-03-11 00:00:00' - TIMESTAMP '2010-09-17 00:00:00') YEAR TO MONTH) FROM dual; # 30

You can see that using EXTRACT in PostgreSQL and Oracle brings different results as Oracle rounds the month part.

Using MONTHS_BETWEEN In Oracle

Oracle provides the built-in function MONTHS_BETWEEN to get the number of months between 2 timestamp and date values:

Oracle:

-- MONTHS_BETWEEN returns a decimal number SELECT MONTHS_BETWEEN(TIMESTAMP '2013-03-11 00:00:00', TIMESTAMP '2010-09-17 00:00:00') FROM dual; # 29.8064516   -- Use FLOOR function to get the same result as PostgreSQL SELECT FLOOR(MONTHS_BETWEEN(TIMESTAMP '2013-03-11 00:00:00', TIMESTAMP '2010-09-17 00:00:00')) FROM dual; # 29