4. 날짜함수

■ 날짜함수(154페이지)

 

1. months_between : 두 날짜 간의 개월 수

2. add_months : 특정 날짜에서 특정 개월을 더한 날짜

3. next_day : 지정된 날짜에서 다음에 돌아오는

                특정 요일의 날짜를 출력

4. last_day : 특정 날짜의 달의 마지막 날짜를 출력

 

 

 

■ 날짜의 산술 연산

1. 날짜 - 날짜 = 숫자

2. 날짜 - 숫자 = 날짜

3. 날짜 + 숫자 = 날짜

 

 

■ 오늘 날짜를 보는 키워드

select sysdate

  from dual;

**sysdate 오늘, 현재날짜

 

 

문제70. 이름, 입사한 날짜부터 오늘까지 총 몇 일 근무했는지

출력하시오

select ename,  sysdate-hiredate from emp;

**빼기 연산한거임

select ename, sysdate-hiredate as "총 근무"

from emp;

*** 작은 따옴표로 쓰면 답 안 나옴.

***sysdate 랑 연산할 때는

    그냥 hiredate 로 뽑으면 되는구만.....

 

 

문제71. 이름, 내가 태어난 날부터 오늘까지 총 몇일 살았는지

출력하시오

select ename, round(sysdate-birth) from emp2

where ename = '오세희';

 

 select ename, sysdate-to_date('1988/04/01','rrrr/mm/dd')

 from emp2

 where ename = '오세희';

 

**날짜랑 문자는 싱글쿼테이션으로 감싸시지요

 

 

 

문제72. 이름, 내가 태어난 날부터 오늘까지 총 몇 달 살았는지

출력하시오

select ename, round(months_between(sysdate , birth))

from emp2

where ename= '오세희';

**최근날짜가 먼저 와야 한다.

 

설명: months_between 은 날짜와 날짜 사이의 개월수를

출력하는 함수

months_between(   ,   ) : 두 날짜간의 개월 수

 

또다른 답

select ename,

 months_between

           (sysdate, to_date('1988/04/01','rrrr/mm/dd'))

 from emp2

 where ename = '오세희';

 

 

 

 

문제73. 위의 결과를 다시 출력하는데 개월수가 높은 학생부터

출력하시오

 

select ename,

round(months_between(sysdate, birth)) 개월수

from emp2

order by 개월수 desc;

**오라클은

as 안붙여도 인식되게끔 한다.

근데 나중에 코드 길어지면

 as 없어서 더 헷갈리고 그런다

 

 

 

문제74. 오늘 날짜에서 100일 뒤의 날짜를 출력하시오

select sysdate + 100

  from dual;

 

 

문제75. 오늘날짜에서 100달 뒤의 날짜를 출력하시오

select add_months(sysdate, 100)

  from dual;

***add_months : 특정 날짜에서 특정 개월을 더한 날짜

 

 

 

 

문제76. 오늘날짜에서 앞으로 돌아올 금요일의 날짜를

출력하시오

select next_day(sysdate, '금요일')

from dual;

 

 

 

 

문제77. 오늘날짜에서 100달 뒤에 돌아올

월요일의 날짜를 출력하시오

select next_day(add_months(sysdate, 100), '월요일')

from dual;

 

 

 

문제78. 요번달의 마지막 날짜를 출력하시오

select last_day(sysdate) from dual;

 

***

last_day : 특정 날짜의 달의 마지막 날짜를 출력

 

***저번달의 마지막 날짜를 출력하시오

 

select last_day(add_months(sysdate, -1)) from dual;

 

 

 

■ 변환 함수

 "데이터의 유형을 변환하는 함수"

*변환 함수의 종류

1. to_char : 날짜형 데이터------>문자형 데이터 변환(ex:10.15->월요일)

              숫자형 데이터------>문자형 데이터로 변환

                          to_char(sysdate, 'day') //// to_char(sysdate, 'd')

2. to_number : 문자형 데이터--->숫자형 데이터로 변환

3. to_date :  문자형 데이터------>날짜형 데이터로 변환

 

문제79. 오늘이 무슨 요일인지 출력하시오

select to_char(sysdate, 'day')

from dual;

 

 

 

문제80. 내가 무슨요일에 태어났는지 확인하시오

select to_char(birth, 'day')

from emp2

where ename = '오세희';

 

또는

select to_char(to_date('1988/04/01', 'rrrr/mm/dd'), 'day')

from dual;

 

※설명: to_char 의 format 의 종류(페이지 172)

 

년도: RRRR, RR, YYYY, YY

달: MM, MON

일: DD

시간: HH, HH24

[HH: 0시부터 12시까지

HH24: 0시부터 24시까지]

분: MI

초: SS

요일: DAY, D

주: IW, WW

 

select ename, to_char(birth, 'day'), to_char(birth, 'd')

from emp2;

 

d는 defalt 값

day 에 대한 오라클 지정 defalt 값

일 월 화 수 목 금 토

1   2     3     4   5    6    7

 

 

 

화면 캡처: 2018-10-16 오후 4:35

D= 일 월 화 수 목 금 토

    1  2  3   4  5   6  7

 

 

문제81. 이름, 태어난 요일을 출력하는데

일월화수목금토 순으로 출력하시오

(요일 디폴트값도 함께)

 

select ename, to_char(birth, 'day'), to_char(birth, 'd')

from emp2

order by to_char(birth, 'd') asc;

 

 

문제82. (오늘의 마지막 문제)

이름, 태어난 요일을 출력하는데

월화수목금토일 순으로 출력하시오

(replace 로도 해보시오)

 

d=defalt=값

select ename, to_char(birth, 'day'), to_char(birth, 'd')

from emp2

order by to_char(birth-1, 'd') asc;

 

 

select ename, to_char(birth, 'day')요일

from emp2

 order by to_char((birth-1), 'd') asc;

 

 

다른답

select to_char(birth, 'day'), to_char(birth, 'd')

from emp2

order by replace(to_char(birth, 'd'), 1, 8);

 

 

select to_char(birth, 'day'), to_char(birth,'d')

from emp2

order by replace(to_char(birth, 'd'),'1','8') asc;

 

 

 

■ to_char 를 사용할 때 날짜 -> 문자로 변경할 시

                                          날짜 포멧

1. 년도: RRRR, RR, YYYY, YY

2. 달: MM, MON

3. 일: DD

4. 시간: HH, HH24

5. 분: MI

6. 초: SS

7. 요일: DAY, DY, D

 예) DAY(금요일), DY(금), D(5)

8. 주: WW, IW

 

문제83. 이름과 입사일, 입사한 년도를 출력하는데

입사한 년도를 아래와 같이 4자리로 출력하시오

 

SCOTT 1981/12/11           1981

SMITH 1980/11/17           1980

          .                                    .

          .                                    .

 

select ename|| to_char(hiredate, 'rrrr/mm/dd'),

to_char(hiredate, 'rrrr')

from emp;

 

select ename, hiredate, to_char(hiredate, 'RRRR')

   from emp;

날짜를 문자로 변환하는데 문자로 변환할 때에

년도 4자리로 출력해라 라는 뜻

 

*RRRR 로 하면 '년도'만 나오고

RRRR, MM 으로 하면 '년도+달'

YYYY 로 쓰면 '년월일시간' 다 나옴.

 

■RR과 YY의 차이

문제 84. 현재 세션의 날짜 포멧을 확인하시오

**세션이란? 지금 내가 접속한 그 프로그램 창을 뜻함

select * from nls_session_parameters;

 

DD     일

MON 달

RR      년도 = RR 뿐만 아니라 YY 도 있음.

 

문제85. 81년 11월 17일에 입사한 사원의

이름과 입사일을 출력하시오(R,Y 으로 시도할 것)

 

**to_char 를 이용해서,  to_date 를 이용해서 풀기

 

select ename, hiredate

  from emp

  where hiredate = to_date('81/11/17', 'RR/MM/DD');

 

 to_date :  문자형 데이터------>날짜형 데이터로 변환

 

 

select ename, hiredate

  from emp

  where hiredate = to_date('81/11/17', 'YY/MM/DD');

 

YY로 하면 값이없다고 나옴.

왜냐하면 년도가 두자리로 설정되어있으면 YY는

안된다.

 

4자리로 해보면

select ename, hiredate

  from emp

  where hiredate = to_date('1981/11/17', 'YYYY/MM/DD');

RRRR로 해도 똑같이 나온다.

 

즉 Y는 2자리수 년도는 인식못하고

    R 은 2, 4자리수 년도 다 인식함.

 

             RR                              YY

             81                              81

       1981  2081                   1981  2081

현재연도에서 가장           현재세기(2000년대)를 인식

가까운 연도를 인식         

*아직까지는 1981년이

가깝다.

그래서 입사일에

RR을 넣었을때 81년이

검색된게

1981로 알아서 찾았기 때문.

 

 

데이터베이스에 년도를 2자리로 입력해서 저장했다면

반드시 RR 을 사용해야 한다.

 

번거롭다아 그래서 코딩할 때

4자리수 입력이 기본이다. RRRR, YYYY

 

 

문제86. 1993년도에 태어난 학생들의 이름과 생일을

 출력하는데 2가지 방법으로 수행하시오

1. to_char

2. substr

 

1. select ename, birth, to_char(birth, 'RRRR,MM,DD')

from emp2

where to_char(birth, 'RRRR') = '1993';

                문자        =          문자

 

2. select ename, birth

   from emp2

   where substr(birth,-2,2)='93';

 

코딩해석순서대로 해설하자면

emp2 테이블에서 자료갖고와서[from]

birth 의 끝에서 2번째, 2개 문자를 뽑는데

그 값이 '93'과 일치하는[where]

birth 와 ename 를 출력하라.[select]

 

DD-MON-RR

라고 데이터가 저장되어 있다.

 

0  7  -  4  월  -  9   3

                     -2  -1 substr(birth, -2, 2)

                      1   2 substr(birth, -2, 2)

뒤에서 위치 잡아 잘라내려면

 

 

**내가

to_date 를 이용해서 뽑아 보려고 시도했음

select ename, birth

from emp2

where birth = to_date('1993', 'rrrr');

근데 안됨

왜냐면

birth 에는 년,월,일 정보가 다 들어있어서

년만 뽑아내려는 명령과 불일치.**************

 

문제87. 12월달이 생일인 학생들의 이름과 생일을

출력하시오

select ename, birth

from emp2

where to_char(birth,'MM')='12';

 

substr 로도 해보시오

select ename, birth

from emp2

where substr(birth, 4,2)='12';

 

**to_date 는 안되는 이유

where birth = to_date('1993', 'rrrr');

**birth는 dd-mm-rr 형식으로 저장되어있다

등호연산자를 썼으니 형식도 같아야 한다

물론 in 을 써도 답은 안 나온다.

 

 

 

문제88. ww와 iw의 차이를 알아내시오

 

select to_char(sysdate, 'ww'), to_char(sysdate, 'iw')

from dual;

 

WW 또는 W - 연 또는 월의 주

IW                      ISO  표준에 따른 연의 주

 

달력상에 나오는 그 주가 몇 번째냐의 차이와

1년 365일로 봤을 때

 

7일씩 끊었을 때 3번째 주

01~07일 까지 1주차

08~14일 까지 2주차

15~21일 까지 3주차

 

~> 요일에 관계 없이 7일을 기준으로 주차를 구분

 그게 ww

 

요일을 살펴보면, 일~월요일 넘어가면서 주차가 바뀐다.

즉, 일(dd)에 관계 없이 월요일부터 일요일까지가  한 주차가 된다.

 

표에서 나타난 것처럼, 1월 1일이 목요일부터 1월 4일 일요일까지 첫 번째 주차가 된다. 

 

또한 1월 5일 월요일부터 1월 11일 일요일까지가 두 번째 주차가 되고,

1월 12일은 일요일에서 넘어간 후의 월요일이므로 그 다음 기준 주차인 세 번째 주차가 된다.

 

~> 요일(월화수목금토일 순)을 기준으로 주차를 구분

이게 iw

 

달력상으로 이해하면 1/6일은 2째 주고 = iw

               (참고로 월요일부터 시작)

7일로 끊어 이해하면 1/6일은 첫째 주다. = ww

 

※ 설명: iw 는 iso 표준에 따른 연의 주를 의미하고

         (한 주의 시작을 iso 표준은 월요일로 본다.)

          ww 는 7일씩 끊어서 주를 표시한다.

 

 

■ 형변환의 종류 2가지

1. 명시적 형변환 :아래의 3가지 형변환 함수를 사용해서

                       형변환을 하는 것.

                            to_char

                            to_number

                            to_date

 

2. 암시적 형변환: 오라클이 자동으로 형변환을 수행하는 것을

                       말한다.

  예) select ename, sal

         from emp

         where sal = 3000;

                 숫자   숫자                 

그래서 데이터가 잘 뽑힘.

그런데

     select ename, sal

        from emp

       where sal = '3000';

               숫자    문자  **스몰 쿼테이션=문자취급**

                           ↓

                       암시적으로 문자를 숫자로 형변환을 해줬다

다른 제품들을 에러난다.

그런데 오라클은 에러안남.

내부적으로 형변환 해줌.

(근데 좋은건 아니다.)

 

※오라클이 내부적으로 수행한 sql 을 확인하고 싶다면?

(sqlplus 에서 시행)

SQL> set autot on

select ename, sal

        from emp

       where sal = '3000';

 

*끌땐 set autot off

 

 

 

내부적으로 3000에 싱글쿼테이션 안 붙인걸 알 수 있다.

오라클은 숫자가 우선순위가 높기 때문에

문자를 숫자로 형변환 한다.

 

 

SQL> select ename, sal

       from emp

       where sal like '30%';

   우선순위: 숫자 >  문자

 %를 문자로 인식 안해버림.

 

나는 문자로 쓰고 싶은데

오라클이 문자->숫자로 바꿔버림

그래서 에러 안 나고 값 나옴.

*에러가 생기는게 정상. 숫자를 문자로 뽑으라고

시켰으니까*

안 좋음...문제 생김

 

설명: 오라클은 암시적 형변환을 수행을 해서

       에러가 나지 않고

       결과를 출력하게 해주지만

       암시적 형변환이 성능을 느리게 하는

       원인이 될 수 있다.

       그래서 가급적 암시적 형변환이

       발생하지 않도록 SQL 을 작성하는 것이

       중요하다.

 

예: <숫자를 문자화해서 입력하는 과정>

  create   table   emp05

  ( ename       varchar2(20),

      sal          varchar2(20) );

 

insert into emp05 values('scott', '3000');

insert into emp05 values('smith', '1500');

insert into emp05 values('allen', '1200');

 

commit;

 

select * from emp05;

 

이제 넣은 자료 출력

 

select ename, sal

from emp05

where ename='3000';

 

 

select enaem, sal

from emp05

where sal    = 3000;

            문자 <숫자

               ↓

 문자를 숫자로 형변환을 한다.

그래서 역시나

문제없이 값이 나온다.(잘못된 값인뎅)

이런 오류가 안나게 하려면

 to_number 함수를 사용한다.

 

■ 형변환 함수 3가지

1. to_char

2. to_number : 숫자로 형변환 하는 함수

3. to_date

 

■to_date :  날짜를 형변환하는 함수

문제89.  81년 11월 17일에 입사한

사원의 이름과 입사일을

출력하시오

select ename, hiredate

from emp

where to_char(hiredate, rrrr,/mm/dd)='1981'

 

select ename, hiredate

 from emp

 where hiredate = to_date('81/11/17', 'rr/mm/dd');

 

 

문제90.  81년 11월 17일에 입사한 사원들의

이름과 입사일을 출력하는데 아래의 공란을 채우시오

        select ename, hiredate

          from emp

          where       = '81/11/17';

 

             to_char('rr/mm/dd')

 

그러나

악성sql 임

  =를 기준으로

※설명: 좌변을 함수로 가공하면 검색속도가 느려진다.

 

튜닝하면

select ename, hiredate

  from emp

  where hiredate = to_date('81/11/17', 'rr/mm/dd');

 

 

where to_char('rr/mm/dd') = '81/11/17';

 

                                    ↓   

where hiredate = to_date('81/11/17', 'rr/mm/dd');

 

 

 

문제91. 아래의 SQL을 튜닝하시오

   튜닝전

   select ename, sal

    from emp

    where sal * 12 = 36000;

 

   튜닝후(=답)

   select ename, sal

     from emp

     where sal = 36000/12 ;

 

문제92. 아래의 SQL 을 튜닝하시오

   튜닝전

   select ename, job

     from emp

     where substr(job, 1,5) = 'SALES';

 

   튜닝후

   select ename, job

    from emp

    where job like '%SALES%';

 

 

문제93. (점심시간 문제)

         1994년에 태어났고 sk 통신사를 쓰는

학생들의 이름과 나이와 통신사를 출력하는데

생일이 빠른 학생부터 출력하고

컬럼명이 한글로 이름, 나이, 통신사 라고

출력되게 하시오

 

select ename 한글, age 나이, telecom 통신사

from emp2

where birth like '%94' and lower(telecom) = 'sk'

order by birth asc;

또는

select ename 이름, age 나이, telecom 통신사

from emp2

where to_char(birth,'rrrr')='1994' and

      lower(telecom) = 'sk'

order by birth asc;

 

select ename 이름, age 나이, telecom 통신사

from emp2

where substr(birth,-2,2)='94' and lower(telecom)='sk'

order by birth asc;

 

 

■to_char 를 이용해서 숫자---->문자로 변환

select ename, sal, to_char(sal, '999,999')

 from emp;

천 단위 표시하려고 문자화 시켜서 컴마 찍히게 함

 

※설명:

 9는 이 자리에 0~9까지 어떤 숫자가 와도

관계 없다.

 

select '38468454',

     to_char('38468454', '999,999,999,999,999')

     from dual;

 

숫자에 컴마찍어서 출력하는거 필수 !!!!!!

***'L999,999,999,999,999'

숫자 앞에 L 붙이면 화폐단위 나옴($나 \)

 

 

■  다른 시간대에서의 데이터 관리

 

-EXTRACT

"그 동안 배우지 않았던 다른 날짜함수들"

 

1. extract 함수 (3권 214페이지)

예제:

 오늘 날짜에서 년도만 추출하시오

 

select extract(year from sysdate )

from dual;

 

 

 

문제499.

오늘 날짜에서 달을 추출하시오

select extract(month from sysdate)

 from dual;

 

 

문제500.

입사한 년도, 입사한 년도별 토탈월급을 출력하시오!

(extract 써서)

 

select extract(year from hiredate), sum(Sal)

from emp

 group by extract(year from hiredate);

 

같은값

select to_char(hiredate, 'rrrr'), sum(sal)

 from emp

 group by to_char(hiredate, 'rrrr');

(이건 년도지정 안해주면

날짜별로 나와서 많이 출력됨)

 

 

■ -tz_offset 함수

 "영국의 그리니치 천문대를 기준으로

   각 나라의 시간대를 확인하는 함수"

 

우리나랑 영국은 8시간정도 차이남.

 

select tz_offset('Asia/Seoul')

 from dual;

 

select * from v$timezome_names

where lower(tzname) like '%seoul%';

이건 테이블없다고 뜸.(이유 모름)

*중요한건 아님. 책에있어서 수업에 다뤄봄.

 

 

■ -to_timestamp 함수 (3권 218페이지)

 

-falshback query 시에 활용

-flashback table 시에 활용

 

 

문제501.

지금부터 10분 전에 emp 테이블의 king 의 월급이

얼마였는지 확인하시오

*일단 킹월급 변경

update emp

set sal = 0

where ename ='KING';

 

select * from emp

as of timestamp to_timestamp('2018/11/05:10:45:00',

                                                        'RRRR/MM/DD:HH24:MI:SS')

where ename = 'KING';

               ↑

**XE 에서 플래시백 쿼리는 됨.(플래시백 테이블은 안됨)

 

**이건 플래시백 테이블**

          ↓

alter table dept enable row movement;  

flashback table dept on timestamp

to_timestamp('2018/11/02:11:17:00','RRRR/MM/DD:HH24:MI:SS');

 

 

 

■-interval 필드( 3권 209페이지)

예:

약 10분 전에 emp 테이블에 king 의 월급은 무엇이었는가?

(INTERVAL 써서)

 

update emp

 set sal = 9000

 where ename = 'KING';

(굳이 업데이트 안해 됨)

 

select ename, sal

 from emp

 as of timestamp (systimestamp - interval '10' minute)

 where ename = 'KING';

 

일일이 시분초를 써서 해주는거 보다

interval 을 쓰는게 더 편리.

 

 

 

■ -to_yminterval 함수

예:

 오늘부터 1년2개월 후의 날짜가 어떻게 되는가?

 

select sysdate + to_yminterval('01-02')

from dual;

 

to_yminterval('01-02')

                  ↑   ↑

                  년   월

 

 

문제502.

위의 문제를 to_yminterval 사용하지 않고

해결하시오.

 

select add_months(sysdate, 14)

from dual;

 

 

 

■ -to_dsinterval (days ~ seconds)

예:

 지금부터 100일 10시간 뒤의 날짜가 어떻게 되는가?

select sysdate + to_dsinterval('100 10:00:00')

from dual;

 

to_dsinterval('일 시:분:초') 라는 식으로 작성.