DB
0907 DB - 숫자, 날짜 함수
jeoniee
2022. 9. 7. 17:55
728x90
반응형
숫자함수
ROUND(반올림)|TRUNC(버림) 함수
- {ROUND|TRUNC}(연산할숫자, 자리값)
SQL> SELECT ROUND(45.923, 2), ROUND(45.923,0), ROUND(45.923, -1)
2 FROM dual;ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
- 연산할 자리값은 소수점위치를 0으로 하고 양수의 자리값은 소수점 아래 음수의 자리값은 소수점 위 정수부분에 대한 연산을 가리킨다.
- ROUND, TRUNC 함수에서 연산 자리값을 생략하는 경우 소수점 0의 자리를 기준으로 연산하게 된다.
SQL> SELECT TRUNC(45.923, 2), TRUNC(45.923), TRUNC(45.923, -1)
2 FROM dual;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)
--------------- ------------- ----------------
45.92 45 40MOD함수
- 숫자를 나눈 나머지값을 연산하는 함수
- MOD(대상숫자, 나눌단위값)
| SQL> SELECT last_name, salary, MOD(salary, 5000) 2 FROM employees 3 WHERE job_id = 'SA_REP'; |
LAST_NAME SALARY MOD(SALARY,5000)
------------------------- ---------- ----------------
Tucker 10000 0
Bernstein 9500 4500
Hall 9000 4000
Olsen 8000 3000
Cambrault 7500 2500
Tuvault 7000 2000
King 10000 0
Sully 9500 4500
McEwen 9000 4000
Smith 8000 3000
Doran 7500 2500
…
- 몫을 구하는 경우 나눗셈(/)의 연산자를 활용하여 표현식을 작성하면 되는데 MOD함수가 몫을 정수까지 구했을 때 더 나눌수 없을 값을 출력하는 것을 고려한다면 몫은 정수범위까지만 출력되어야 한다. 따라서 그냥 나눗셈의 연산에 버림의 함수를 활용하여 값을 정수까지 정리해버리면 MOD함수의 나머지값과 맞아떨어지는 몫이 출력된다.
| SQL> SELECT last_name, salary, TRUNC(salary/5000, 0), MOD(salary, 5000) 2 FROM employees 3 WHERE job_id = 'SA_REP'; |
LAST_NAME SALARY TRUNC(SALARY/5000,0) MOD(SALARY,5000)
------------------------- ---------- -------------------- ----------------
Tucker 10000 2 0
Bernstein 9500 1 4500
Hall 9000 1 4000
Olsen 8000 1 3000
Cambrault 7500 1 2500
Tuvault 7000 1 2000
King 10000 2 0
Sully 9500 1 4500
McEwen 9000 1 4000
Smith 8000 1 3000
Doran 7500 1 2500
…날짜 함수
- 날짜 데이터는 년, 월, 일, 시, 분, 초 등의 값을 가지고 있는 고정 크기의 데이터타입
SYSDATE 함수(Oracle함수)
- 접속중인 데이터베이스 서버의 현재 시간을 날짜데이터로 돌려주는 함수
- sql*plus에서 국가가 미국으로 설정된 경우 날짜의 양식은 'DD-MON-YY'/'일-월-년도'
- 날짜데이터는 접속 도구에 설정된 양식에 맞춰 출력되며 출력되지 않은 요소값도 출력만 되지 않을 뿐 함수가 돌려준 데이터에는 포함되어 있다.
- 날짜데이터를 정확하게 다루기 위해서는 변환함수를 활용하면 된다.
SQL> SELECT sysdate
2 FROM dual;SYSDATE
---------
06-SEP-22
- 날짜 → 문자열로 변환하는 TO_CHAR의 예제
SQL> SELECT sysdate, TO_CHAR(sysdate, 'YYYY-MM-DD HH:MI:SS')
2 FROM dual;
SYSDATE TO_CHAR(SYSDATE,'YY
--------- -------------------
06-SEP-22 2022-09-06 05:29:10
now()함수(mysql함수)
- sysdate와 동일하게 서버의 날짜데이터를 돌려주는 함수
날짜의 산술연산
- 날짜의 산술연산에서 숫자 1의 크기는 하루와 같다.
날짜 + 숫자
- 날짜에서 숫자를 더하는 경우 숫자만큼의 이후 날짜가 출력된다.
SQL> SELECT sysdate, sysdate + 1, sysdate + 2
2 FROM dual;SYSDATE SYSDATE+1 SYSDATE+2
--------- --------- ---------
06-SEP-22 07-SEP-22 08-SEP-22
날짜 - 숫자
- 날짜에 숫자를 빼는 경우 숫자만큼의 이전 날짜가 출력된다.
| SQL> SELECT sysdate, sysdate - 1, sysdate - 2 2 FROM dual; |
SYSDATE SYSDATE-1 SYSDATE-2
--------- --------- ---------
06-SEP-22 05-SEP-22 04-SEP-22
날짜 - 날짜
- 두 날짜간의 차이나는 크기를 숫자로 돌려주는 함수
- 큰 날짜(이후) 에서 작은 날짜(이전) 를 빼는 경우 양수가 출력된다.
| SQL> SELECT sysdate - (sysdate - 3) 2 FROM dual; |
SYSDATE-(SYSDATE-3)
-------------------
3
- 작은 날짜(이전)에서 큰 날짜(이후)를 빼는 경우 음수가 출력된다.
| SQL> SELECT (sysdate - 7) - sysdate 2 FROM dual; |
(SYSDATE-7)-SYSDATE
-------------------
-7
- 날짜 + 날짜의 연산은 지원하지 않는다.
| SELECT (sysdate - 3) + (sysdate + 5) FROM dual; |
SQL Error [975] [42000]: ORA-00975: 날짜와 날짜의 가산은 할 수 없습니다
날짜 + 1/24
- 하루(1) → 24시간
한시간? → 1/24
1분 → 1/24/60
1초 → 1/24/60/60
- 현재시간 + 3시간
| SELECT sysdate, sysdate + 3/24 FROM dual; |
SYSDATE |SYSDATE+3/24 |
-----------------------+-----------------------+
2022-09-07 15:20:55.000|2022-09-07 18:20:55.000|
- 현재시간 - 30분
| SELECT sysdate, sysdate - 30/24/60 FROM dual; |
SYSDATE |SYSDATE-30/24/60 |
-----------------------+-----------------------+
2022-09-07 15:24:02.000|2022-09-07 14:54:02.000|
- 현재시간 + 5초
| SELECT sysdate, sysdate + 5/24/60/60 FROM dual; |
SYSDATE |SYSDATE+5/24/60/60 |
-----------------------+-----------------------+
2022-09-07 15:25:42.000|2022-09-07 15:25:47.000|
- 오늘 + 50주 뒤
| SELECT sysdate, sysdate + 7*50 FROM dual; |
SYSDATE |SYSDATE+7*50 |
-----------------------+-----------------------+
2022-09-07 15:28:25.000|2023-08-23 15:28:25.000|
- 오늘 + 반년 뒤
| SELECT sysdate, sysdate + 365/2 FROM dual; |
SYSDATE |SYSDATE+365/2 |
-----------------------+-----------------------+
2022-09-07 15:30:04.000|2023-03-09 03:30:04.000|- sysdate - hire_date → 오늘 - 입사일 = 근속 일수
- 근속 일수 / 7 → 근속 주차
| SELECT last_name, (sysdate - hire_date) / 7 AS weeks FROM employees WHERE department_id = 90; |
LAST_NAME|WEEKS |
---------+-----------------------------------------+
King |1003.235687830687830687830687830687830688|
Kochhar | 885.092830687830687830687830687830687831|
De Haan |1129.664259259259259259259259259259259259|
- 근속 주차를 계산하는 표현식의 결과는 숫자값이므로 숫자 함수에 넣어서 사용 가능하다.
| SELECT last_name, TRUNC((sysdate - hire_date) / 7, 0) AS weeks FROM employees WHERE department_id = 90; |
LAST_NAME|WEEKS|
---------+-----+
King | 1003|
Kochhar | 885|
De Haan | 1129|날짜 조작 함수
- 연산이 바로 하기 어려운 월 관련 연산을 해주는 함수들
- 함수 사용 시 날짜의 형식은 접속 도구가 사용하는 날짜 양식의 문자열로 입력하면 DB가 자체적으로 날짜데이터로 데이터타입 변환과정을 거쳐 연산을 수행한다.
MONTHS_BETWEEN
- 두 날짜간의 차이 개월 수를 연산해주는 함수
- 첫 번째 날짜의 값에서 두번째 날짜값을 빼는 연산으로 결과를 출력
| SELECT MONTHS_BETWEEN('1995-09-01', '1994-01-11') FROM dual; |
MONTHS_BETWEEN('1995-09-01','1994-01-11')|
-----------------------------------------+
19.67741935483870967741935483870967741935|
| SELECT MONTHS_BETWEEN('1994-01-11', '1995-09-01') FROM dual; |
MONTHS_BETWEEN('1994-01-11','1995-09-01') |
------------------------------------------+
-19.67741935483870967741935483870967741935|
| SELECT MONTHS_BETWEEN(sysdate, '1960-8-18') FROM dual; |
MONTHS_BETWEEN(SYSDATE,'1960-8-18') |
----------------------------------------+
744.667018742532855436081242532855436081|
ADD_MONTHS
- 실제 날짜를 고려한 월단위로 이전 이후 날짜를 구해주는 함수
- ADD_MONTHS(날짜, 월값)
- 연산할 월단위를 양수 → 이후 날짜, 음수 → 이전 날짜
| SELECT ADD_MONTHS('1996-01-31', 1) FROM dual; |
ADD_MONTHS('1996-01-31',1)|
--------------------------+
1996-02-29 00:00:00.000|
| SELECT ADD_MONTHS(sysdate, -5) FROM dual; |
ADD_MONTHS(SYSDATE,-5) |
-----------------------+
2022-04-07 16:20:00.000|
NEXT_DAY
- 특정 날짜 다음으로 오는 특정 요일을 연산해주는 함수
- NEXT_DAY(날짜, '찾는 요일')
| SQL> SELECT NEXT_DAY('25-NOV-22', 'MONDAY') 2 FROM dual; |
NEXT_DAY(
---------
28-NOV-22
- DBeaver에서는 요일을 문자열이 아닌 숫자로 입력 받는다. (일 = 1, 월 = 2 , ... 토 = 7)
- 22-11-25날짜 다음으로 오는 월요일을 연산
| SELECT NEXT_DAY('2022-11-25', 2) FROM dual; |
NEXT_DAY('2022-11-25',2)|
------------------------+
2022-11-28 00:00:00.000|LAST_DAY
- 입력한 날짜가 속한 월의 마지막 날을 돌려주는 함수
| SELECT LAST_DAY('1995-02-01') FROM dual; |
LAST_DAY('1995-02-01') |
-----------------------+
1995-02-28 00:00:00.000|
문제
오늘(sysdate)로부터 10개월 뒤 다음오는 금요일의 날짜를 출력하시오.
- DBeaver
| SELECT NEXT_DAY(ADD_MONTHS(sysdate, 10), 6) FROM dual; |
NEXT_DAY(ADD_MONTHS(SYSDATE,10),6)|
----------------------------------+
2023-07-14 17:06:25.000|
- sql*plus, sql Developer
| SELECT NEXT_DAY(ADD_MONTHS(sysdate, 10), 'FRIDAY') FROM dual; |
문제
사원(employees)의 입사일(hire_date)로부터 6개월 뒤 그 다음달의 첫 주 금요일?
| SELECT sysdate, NEXT_DAY(LAST_DAY(ADD_MONTHS(sysdate, 6)), 6) FROM dual; |
SYSDATE |NEXT_DAY(LAST_DAY(ADD_MONTHS(SYSDATE,6)),6)|
-----------------------+-------------------------------------------+
2022-09-07 17:14:36.000| 2023-04-07 17:14:36.000|
ROUND(반올림)|TRUNC(버림)
함수명은 숫자에서 사용된 함수명과 동일하지만 입력되는 데이터 타입과 단위가 날짜인 경우 날짜함수로도 사용된다.
ROUND|TRUNC(날짜, 단위)
단위로는 'YEAR', 'MONTH'를 사용할 수 있다.
MONTH 단위로 반올림한 경우 MONTH보다 작은 단위인 날짜의 일, 시, 분, 초의 값의 절반 여부를 연산하여 올림 또는 버림이 발생한다.
| SELECT ROUND(sysdate, 'MONTH') FROM dual; |
ROUND(SYSDATE,'MONTH') |
-----------------------+
2022-09-01 00:00:00.000|
| SELECT sysdate + 8 + 7/24, ROUND(sysdate + 8 + 7/24, 'MONTH') FROM dual; |
SYSDATE+8+7/24 |ROUND(SYSDATE+8+7/24,'MONTH')|
-----------------------+-----------------------------+
2022-09-16 00:25:19.000| 2022-10-01 00:00:00.000|
YEAR' 단위로 반올림하는 경우 'YEAR'보다 작은 단위인 월, 일, 시, 분, 초의 절반 여부를 연산하여 올림 또는 버림이 연산된다.
| SELECT sysdate, ROUND(sysdate, 'YEAR') FROM dual; |
SYSDATE |ROUND(SYSDATE,'YEAR') |
-----------------------+-----------------------+
2022-09-07 17:29:08.000|2023-01-01 00:00:00.000|
TRUNC 함수는 기준 단위보다 작은 단위의 값들을 무조건 버림 연산을 수행한다.
| SELECT sysdate + 10, TRUNC(sysdate + 10, 'MONTH') FROM dual; |
SYSDATE+10 |TRUNC(SYSDATE+10,'MONTH')|
-----------------------+-------------------------+
2022-09-17 17:32:01.000| 2022-09-01 00:00:00.000|
SELECT sysdate + 10, TRUNC(sysdate + 10, 'YEAR')
FROM dual;
SYSDATE+10 |TRUNC(SYSDATE+10,'YEAR')|
-----------------------+------------------------+
2022-09-17 17:33:22.000| 2022-01-01 00:00:00.000|
SELECT
employee_id,
hire_date,
MONTHS_BETWEEN(SYSDATE, hire_date) tenure,
ADD_MONTHS(hire_date, 6) review,
NEXT_DAY(hire_date, 6), # 6은 sql*plus/sql developer에서는 'FRIDAY'로 바꿔서 실행
LAST_DAY(hire_date)
FROM
employees
WHERE
MONTHS_BETWEEN(SYSDATE, hire_date) < 180;728x90
반응형