IT 땅그지

// epoch를 이용하여 초 단위로 계산

// 60을 나눈 이유는 분으로 계산하기 위하여

select round(extract(epoch from ('2200'::time - '1800'::time))::numeric/60);

 select round(extract(epoch from ('2019-06-07 2200'::timestamp - '2019-06-06 1800'::timestamp))::numeric/60);

출처 :  https://ntalbs.github.io/2010/postgresql-date/

간단한 날짜 관련 함수 및 날짜 연산:

-- 오늘 (date) select current_date; -- 현재시각 (timestamp) select now(); select current_timestamp; -- 어제/오늘/내일 select current_date - 1 "어제", current_date "오늘", current_date + 1 "내일" ; -- day of week select extract(dow from current_date); -- 일요일(0) ~ 토요일(6) select extract(isodow from current_date); -- 월요일(1) ~ 일요일(7) -- day of year select extract(doy from current_date); -- week of year select extract(week from current_date); -- 두 날짜 사이의 날수 select '2010-07-05'::date - '2010-06-25'::date;

한 주의 첫날, 마지막 날 구하기:

-- (주 첫 날을 월요일로 할 때 주) 첫날, 마지막 날 -- date_trunc() 함수의 리턴 타입은 timestamp임 -- 이번 주 select date_trunc('week', current_date)::date "이번 주 첫날", date_trunc('week', current_date)::date+6 "이번 주 마지막 날" ; -- 전 주 select date_trunc('week', current_date-7)::date "전 주 첫날", date_trunc('week', current_date-7)::date+6 "전주 마지막 날" ; -- 다음 주 select date_trunc('week', current_date+7)::date "다음 주 첫날", date_trunc('week', current_date+7)::date+6 "다음주 마지막 날" ; -- (주 첫 날을 일요일로 할 때) 주 첫날/마지막 날 -- week로 date_trunc를 하는 경우 결과가 월요일 날짜가 되기 때문에 -- 한 주를 일요일~토요일까지로 하는 경우는 -1 필요 -- 이번 주 select date_trunc('week', current_date)::date-1 "이번 주 첫날", date_trunc('week', current_date)::date+6-1 "이번 주 마지막 날" ; -- 전 주 select date_trunc('week', current_date-7)::date-1 "전 주 첫날", date_trunc('week', current_date-7)::date+6-1 "전주 마지막 날" ; -- 다음 주 select date_trunc('week', current_date+7)::date-1 "다음 주 첫날", date_trunc('week', current_date+7)::date+6-1 "다음주 마지막 날" ;

한 달의 첫날, 마지막 날 구하기:

-- 한 달 전/후 날짜 select current_date - interval '1 months' "전 달", current_date + interval '1 months' "다음 달" ; -- 이번 달 첫날, 마지막 날 select date_trunc('month', current_date)::date "첫날", date_trunc('month', current_date + interval '1 months')::date - 1 "마지막 날" ; -- 전달 첫날, 마지막 날 select date_trunc('month', current_date - interval '1 months')::date "첫 날", date_trunc('month', current_date)::date - 1 "마지막 날" ; -- 다음 달 첫날, 마지막 날 select date_trunc('month', current_date + interval '1 months')::date "첫 날", date_trunc('month', current_date + interval '2 months')::date - 1 "마지막 날" ;

이번 주 첫날부터 마지막 날까지 날짜들:

-- 이번 주 날짜 select date_trunc('week', current_date)::date -1 + i "일~토", date_trunc('week', current_date)::date + i "월~일" from generate_series(0,6) as t(i);

이번 달 첫날부터 마지막 날까지 날짜들:

generate_series() 함수를 사용한다. 한 달이 28일, 29일, 30일, 31일 중 어떤 것이 될지 알 수 없기 때문에 월의 마지막날을 구해 generate_series()의 두번째 인수로 넣어준다.

-- 이번 달 날짜 (첫날 ~ 마지막 날) select date_trunc('month', current_date)::date + (i - 1) from generate_series( 1, extract(day from date_trunc('month', current_date + interval '1 months')::date - 1)::integer ) as t(i); select date_trunc('month', current_date)::date + (i - 1) from generate_series( 1, extract(day from date_trunc('month', current_date) + interval '1 months' - interval '1 days')::integer ) as t(i);

week of month:

이번 달의 첫날부터 마지막 날까지의 날짜와 week of month를 구하는 쿼리인데, 1일~7일까지는 첫째 주, 8일~14일까지는 둘째 주와 같은 식으로 된다. 역시 generate_series() 함수를 사용했다. 위와 같이 첫 날과 마지막 날의 차를 구해 수열을 만들지 않고, 0~30까지 만들어 무조건 더하면서 이번 달에 속하는 날짜만 WHERE 조건으로 추려내게 했다.

select dt, to_char(dt, 'W') "day of week" from ( select date_trunc('month', current_date)::date + i dt from generate_series(0, 30) as t(i) ) t where extract(month from dt) = extract(month from current_date) ;

PostgreSQL 매뉴얼 참조 URL:

http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html http://www.postgresql.org/docs/8.4/interactive/functions-formatting.html

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

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

(SELECT LTRIM(TO_CHAR((COALESCE(MAX(CODE),'0')::integer)+1,'0000')) FROM tabe);

Oracle INSTR

SELECT INSTR('안녕|하세요', '|') FROM DUAL

Postgresql position

select position('|' in '안녕|하세요');

결과 = 3

WITH ... AS

WITH TEST AS (

UPDATE TABLE(테이블) SET 컬럼 = 값 RETURNING * )

INSERT INTO TABLE(테이블) (컬럼) 

SELECT (값)  WHERE NOT EXISTS 

(SELECT * FROM TEST);

INSERT INTO ... ON CONFLICT ... DO UPDATE SET ...

INSERT INTO TABLE

                (   A

                  , B

                  , C

                  , D)

            VALUES( 

                   'A'

                  , 'B'

                  , 'C'

                  , 'D' )

ON CONFLICT (A, B, C,D)

DO UPDATE SET A = '0'

PostgreSQL\9.6\data   pg_hba.conf

 # IPv4 local connections:

host     all        all        0.0.0.0/0        md5

추가

Oracle

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10 ;

Postgresql

SELECT LEVEL FROM GENERATE_SERIES(1,10) LEVEL;

CREATE OR REPLACE FUNCTION public.sp2_test(test character varying, OUT out_msg character varying)

RETURNS character varying

LANGUAGE sql

AS $function$

declare

BEGIN

EMD;

EXCEPTION

  WHEN OTHERS THEN

       OUT_MSG:=SQLERRM;

END;

$function$