pajama

안녕하세요. 데이터베이스 사랑넷에 두 날짜사이에서 주말을 제외한 일수를 구하는 방법에 대한 질문이 있어서 이것 저것 찾아보고 시험해봤습니다.

기능에 대해서 잘 모르다보니 working day sql postgresql 키워드로 구글에 검색해보니 비슷한 기능을 구현한 예제가 많이 있었습니다. 먼저 질문 내용입니다.

아래 쿼리는 mysql에서
두 날짜사이 주말일수를 제외한 일수를 구하는 sql문인데
PostgreSQl에서 적용되게 변환하고 싶습니다.
부탁드립니다.
select ABS(DATEDIFF('2020-04-23', '2020-04-26')) + 1 - ABS(DATEDIFF(ADDDATE('2020-04-23', INTERVAL 1 - DAYOFWEEK('2020-04-23') DAY),
ADDDATE('2020-04-26', INTERVAL 1 - DAYOFWEEK('2020-04-26') DAY))) / 7 * 2
- (DAYOFWEEK(IF('2020-04-26' < '2020-04-23', '2020-04-26', '2020-04-23')) = 1)
- (DAYOFWEEK(IF('2020-04-26' > '2020-04-23', '2020-04-26', '2020-04-23')) = 7);

찬찬히 살펴보면 주말일수를 먼저 빼고 그외의 경우에 대한 조건이 포함되어 있습니다. 이것 저것 경우의 수를 고려한 것인데 mysql에서 날짜를 바꾸어서 실행해보아도 맞는 결과가 나오는 것 같습니다.

그래서 최대한 위의 것을 참고해서 PostgreSQL 문법에 맞게 만들어 보았습니다.

SELECT ABS(DATE '2020-04-04' - DATE '2020-04-26') - 
       ABS((DATE '2020-04-04' - EXTRACT(DOW FROM DATE '2020-04-04')::INT) -
           (DATE '2020-04-26' - EXTRACT(DOW FROM DATE '2020-04-26')::INT))/7*2 -
       CASE WHEN EXTRACT(DOW FROM DATE '2020-04-04') = 0 THEN 1 ELSE 0 END +
       CASE WHEN EXTRACT(DOW FROM DATE '2020-04-26') = 0 THEN 1 ELSE 0 END

질문하신 분의 계산식과 유사하게 만들었지만, 여기서는 빠른 날짜에서 이후 날짜를 빼는 식이라 날짜 순서가 바뀌면 쿼리도 바뀌어야 합니다. 이런식으로도 불가능하진 않지만 상당히 복잡해질 수 있습니다.

그래서 좀더 나은 방법이 없을까 해서 구글을 찾아보니 액셀의 NETWORKDAYS라는 함수가 이것과 동일한 기능인 것을 알게 됐습니다. 그럼 더 나아가 PostgreSQL에 NETWORKDAYS를 구현한 사례도 있겠거니해서 찾아보니 아래와 같은 예제를 찾았습니다.

select start_date, end_date, 
       sum(case when extract (dow from dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select start_date, end_date, 
              generate_series(start_date, end_date, '1 day'::interval) as dt
       from   tbl
     ) t
group by start_date, end_date;

PostgreSQL에서 요일을 확인하려면 extract (dow from date) 와 같은식으로 사용하는데 0(일요일)에서 6(토요일)까지 규정되어 있습니다. 이것 저것 따질 것도 없이 날짜를 쭉 나열하고 요일을 판별하는 식으로 아주 직관적입니다.

다만 날짜 구간이 길면 성능이 어떨지 모르겠군요.

우선 여기까지 질문에 대해서는 해결이 되었는데 인포믹스에서 사용하는 방법도 정리해두면 좋을 것 같아 위의 방법을 따라 해보았습니다. 인포믹스도 0은 일요일을 나타내고 1은 월요일을 나타내는 식입니다.

select sum(case when weekday(dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select '2020-04-04'::date + level units day as  dt connect by level < abs('2020-04-04'::date - '2020-04-26'::date)
     ) t
         thediff
              15
1 row(s) retrieved.

참고사이트

https://www.postgresql.org/docs/8.1/functions-datetime.html

https://dba.stackexchange.com/questions/207701/count-business-days-between-2-dates-in-postgresql

https://stackoverflow.com/questions/7388420/get-datediff-excluding-weekends-using-sql-server

https://stackoverflow.com/questions/1964544/timestamp-difference-in-hours-for-postgresql

환경

MySQL : 5.0.89, AIX 6.1

Informix : 11.50.FC9, AIX 6.1

MySQL의 BLOB데이터를 Informix로 마이그레이션 해보았습니다.

샘플 테이블 및 데이터 생성 (MySQL)

echo "create table blotest(a int, b blob)" | mysql -u root -p1nfra test

echo "insert into blobtest values (2,load_file('/home/mysql/logo.gif'))" | mysql -u user -ppasswd test

처음에는 mysqldump의 --hex-blob, --tab 옵션으로 데이터를 추출하였으나 구분자가 있는 형태로 추출했을 때 hex형태로 추출되지 않아

아래와 같이 OUTFILE문으로 추출했습니다.

echo "SELECT a,hex(b) INTO OUTFILE '/home/mysql/blobtest.txt'  FIELDS TERMINATED BY '|'      LINES TERMINATED BY '|\n'     FROM blobtest;" | mysql -u user -ppasswd test

위와 같이 MySQL에서 OUTFILE로 데이터를 추출하면 NULL값은 \N으로 추출되기 때문에 이에대한 처리가 필요합니다.

그리고 인포믹스의 구분자 파일에는 레코드 끝에 구분자가 있으므로 LINES TERMINTAED BY를 '|\n' 으로 했습니다.

MySQL에서는 hex값의 알파벳이 대문자로 추출되는 반면 인포믹스는 소문자로 추출되는데, 로드할 때 문제는 없었습니다.

인포믹스에 BLOB 데이터를 입력할 테이블 생성 (Informix)

echo "create table blobtest (a byte)" | dbaccess stores_demo

echo "create table blobtest1 (a blob)" | dbaccess stores_demo

인포믹스에 BLOB 타입에 입력할때는 구분자 파일과 BLOB파일이 별도로 존재하므로 우선 BYTE 타입에 먼저 입력후 BLOB으로 변환하여

다시 입력하는 순서로 진행했습니다.

echo "load from /home/mysql/blobtest.txt insert into blobtest" | dbaccess stores_Demo

echo "insert into blobtest1 select b::blob from blobtest" | dbaccess stores_demo

GUI 툴로 확인한 결과 이미지가 잘 입력되었음을 확인할 수 있었습니다.

How to migrate large blob table from mysql to postgresql?

http://dba.stackexchange.com/questions/4211/how-to-migrate-large-blob-table-from-mysql-to-postgresql

Migrate a database from MySQL to IBM Informix Innovator-C Edition, Part 1: Comparing MySQL to IBM Informix Innovator-C Edition

http://www.ibm.com/developerworks/data/library/techarticle/dm-1102mysqltoinnovatorc/

Migrate a database from MySQL to IBM Informix Innovator-C Edition, Part 2: Step-by-step walk-through of the migration process

http://www.ibm.com/developerworks/data/tutorials/dm-1102mysqltoinnovatorc2/index.html