포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ TRUNC(date) Oracle Function ]
TRUNC(date) 함수는 인수로 지정한 날짜(date)를 사용자가 지정한 포맷 형식(fmt)을 기준으로 절삭한 날짜(date)를 반환한다. 반환된 값은 비록 date에 다른 datetime 데이터 형을 지정했어도, DATE 데이터 형으로 반환된다. 만약 fmt를 생략했다면, date는 가장 가까운 날로 절삭한다.
- 함수 설명 : TRUNC(date) 오라클 함수 링크
- LAST_DAY 함수 / ROUND(date) 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table]
1. Oracle(오라클)
TO_DATE() 함수
기준 날짜를 연도를 기준으로 절삭 작업을 수행하여서 “2020-01-01”을 반환한다.
| Oracle Programming |
SELECT TRUNC(TO_DATE('2020/06/22', 'YYYY/MM/DD'), 'YEAR')
FROM DUAL;
| Results |
TRUNC_FUNC
-------------------------
01-JAN-2020 00:00:00
2. Python Pandas(파이썬)
strptime() 함수와 replace() 함수
기준 날짜의 월/일/시간/분/초/마이크로 초를 직접 치환하여서 “2020-01-01”을 반환한다.
| Python Programming |
from dateutil import relativedelta
import datetime
datetime.datetime.strptime('2020-12-03', '%Y-%m-%d').replace(month=1, day=1, hour=0, minute=0, second=0, microsecond=0)
| Results |
datetime.datetime(2020, 1, 1, 0, 0)
datetime.datetime 함수
- Dateoffset : 기준일자를 사용자가 지정한 인수에 따라 특정 일자로 변경한다.
- SemiMonthEnd : 기준일자를 해당 월의 중순(15일)으로 변경한다.
- MonthEnd : 기준일자를 해당 월의 마지막 날짜로 변경한다.
- YearEnd : 기준일자를 해당면의 마지막 날짜로 변경한다.
| Python Programming |
from pandas.tseries.offsets import SemiMonthEnd, MonthEnd,YearEnd
display( datetime.datetime.strptime('2020-11-03', '%Y-%m-%d') + SemiMonthEnd(1) )
display( datetime.datetime.strptime('2020-11-03', '%Y-%m-%d') + MonthEnd(1) )
display( datetime.datetime.strptime('2020-11-03', '%Y-%m-%d') + YearEnd(1) )
datetime.datetime 함수
인수 -1은 지정한 인수에 해당하는 직전 날짜를 반환한다.
| Python Programming |
from pandas.tseries.offsets import SemiMonthBegin,MonthBegin,YearBegin
display( datetime.datetime.strptime('2020-12-03', '%Y-%m-%d') + SemiMonthBegin(-1) )
display( datetime.datetime.strptime('2020-12-03', '%Y-%m-%d') + MonthBegin(-1) )
display( datetime.datetime.strptime('2020-12-03', '%Y-%m-%d') + YearBegin(-1) )
| Results |
Timestamp('2020-12-01 00:00:00')
Timestamp('2021-01-01 00:00:00')
Timestamp('2020-01-01 00:00:00')
pd.to_datetime() 함수
| Python Programming |
from pandas.tseries.offsets import MonthEnd
from dateutil.relativedelta import relativedelta
import copy
withmooc =copy.copy(emp)
withmooc['add_Month'] = pd.to_datetime(withmooc['hiredate']) + MonthEnd(1) # + pd.DateOffset(months=5)
withmooc.head(7)
| Results |
empno ename job mgr hiredate sal comm deptno add_Month
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 1980-12-31
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 1981-02-28
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 1981-02-28
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 1981-04-30
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 1981-09-30
5 7698 BLAKE MANAGER 7839.0 1981/03/01 2850 NaN 30 1981-03-31
6 7782 CLARK MANAGER 7839.0 1981/01/09 2450 NaN 10 1981-01-31
3. R Programming (R Package)
as.Date() 함수
기준일자를 지정한 날짜 format으로 변경하여 반환한다.
| R Programming |
%%R
base_date = lubridate::ymd_hms("2020-05-21 12:07:15",tz=Sys.timezone())
print( as.Date(format(base_date, "%Y-%m-01") ) )
print( as.Date(format(base_date, "%Y-01-01") ) )
| Results |
[1] "2020-05-01"
[1] "2020-01-01"
lubridate::ymd_hms() 함수
floor_date 함수와 ceiling_date 함수를 사용하여서 기준일자를 특정 날짜로 내림 또는 절삭 작업을 수행하여 변경된 날짜를 반환한다.
| R Programming |
%%R
library(lubridate)
base_date = lubridate::ymd_hms("2020-05-21 12:07:15",tz=Sys.timezone())
print(lubridate::floor_date(base_date - months(1), "month"))
print(lubridate::floor_date(base_date - months(1), "month") - 1 )
print(lubridate::ceiling_date(base_date - months(1), "month"))
print(lubridate::ceiling_date(base_date - months(1), "month") - 1)
[1] "2020-04-01 KST"
[1] "2020-03-31 23:59:59 KST"
[1] "2020-05-01 KST"
[1] "2020-04-30 23:59:59 KST"
lubridate::ymd_hms() 함수
lubridate::rollback 함수를 사용하여서 기준일자를 이전 월의 마지막 날짜로 변경하여 반환한다.
roll_to_first 인수를 지정하여서 기준일자를 이전 월의 초일자 날짜로 변경하여 반환한다.
| R Programming |
%%R
library(lubridate)
base_date = lubridate::ymd_hms("2020-05-21 12:07:15",tz=Sys.timezone())
print(end_last_month <- lubridate::rollback(base_date) )
print(init_last_month <- lubridate::rollback(end_last_month, roll_to_first = TRUE) )
| Results |
[1] "2020-04-30 12:07:15 KST"
[1] "2020-04-01 12:07:15 KST"
lubridate::ceiling_date() 함수
lubridate::ceiling_date 함수를 사용하여서 기준일자에 대하여 일자(Day)를 기준으로 올림 하여서 다음 달 초일자(1일)로 변경한다.
| R Programming |
%%R
# 현재일자
print( Sys.Date() )
library(lubridate)
# 현재일자를 기준으로 다음달 초일자를 계산 후 1일을 빼서 당월 마지막 날짜를 추출한다.
print( lubridate::ceiling_date(Sys.Date(),"month") - days(1) )
# 현재일자와 당월 말일자 사이의 Gap
print( (ceiling_date(Sys.Date(),"month") - 1) - Sys.Date() )
difftime((ceiling_date(Sys.Date(),"month") - 1) , Sys.Date() )
| Results |
[1] "2021-02-15"
[1] "2021-02-28"
Time difference of 13 days
Time difference of 13 days
as.Date() 함수
개별 사원의 입사 월 마지막 날짜를 기준으로 5개월이 경과된 날짜를 반환한다. 현재 월의 마지막 날짜를 계산하기 위하여 다음 달 초일자(1일)를 계산 후 1일을 빼는 방식을 계산한다.
| R Programming |
%%R
withmooc <- emp
withmooc['add_date'] <- (lubridate::ceiling_date(as.Date(withmooc$hiredate),"month") - 1) # %m+% months(5)
withmooc[1:10, ]
| Results |
# A tibble: 10 x 9
empno ename job mgr hiredate sal comm deptno add_date
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <date>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1980-12-31
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-02-28
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-02-28
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-04-30
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981-09-30
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981-03-31
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1981-01-31
8 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 1982-12-31
9 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 1981-11-30
10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1981-09-30
4. R Dplyr Package
as.Date() 함수
개별 사원의 입사월 마지막 날짜를 기준으로 5개월이 경과된 날짜를 반환한다. 현재 월의 마지막 날짜를 계산하기 위하여 다음 달 초일자(1일)를 계산 후 1일을 빼는 방식을 계산한다.
| R Programming |
%%R
library(lubridate)
library(mondate) # 월단위 날짜 처리
emp %>%
dplyr::mutate(empno,ename,hiredate,
month_end = (lubridate::ceiling_date(as.Date(hiredate),"month") - days(1)) ) %>%
head(7)
| Results |
# A tibble: 7 x 9
empno ename job mgr hiredate sal comm deptno month_end
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <date>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1980-12-31
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-02-28
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-02-28
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-04-30
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981-09-30
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981-03-31
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1981-01-31
5. R sqldf Package
- [SQLite 참고] : SQLite date Function [링크]
- datetime 함수의 인수를 사용하여 특정 일자로 변경한다
- start of day : 기준일자의 시작 시간( 자정 - 00:00:00)을 반환한다.
- start of month : 기준일자가 속한 해당 월의 초일자를 반환한다.
- start of year : 기준일자가 속한 해(Year)의 시작일자(1월 1일)를 반환한다.
- last_day : 기준일자가 속한 현재 월의 마지막 날짜를 계산하기 위하여 먼저 해당 월의 초일자를 계산하고, 당월 초일자에서 1개월 경과한 다음 달 초일자를 계산 후 1일을 빼는 방식으로 계산한다.
| R Programming |
%%R
sqldf(" select datetime('2020-08-30 12:54:12', 'start of day') day_start,
datetime('2020-08-30 12:54:12', 'start of month') mon_start,
datetime('2020-08-30 12:54:12', 'start of year') year_start,
date('2020-08-10 12:54:12','start of month','+1 month','-1 day') last_day ")
| Results |
day_start mon_start year_start last_day
1 2020-08-30 00:00:00 2020-08-01 00:00:00 2020-01-01 00:00:00 2020-08-31
DATE() 함수
- last_day : 개별 사원의 입사 월 마지막 날짜를 계산한다.
- Mon5_last_day : 개별 사원의 입사월 마지막 날짜에서 5개월이 경과된 날짜를 반환한다.
| R Programming |
%%R
sqldf(" SELECT ename,ename, hiredate,
DATE(strftime('%Y-%m-%d', hiredate * 3600 * 24, 'unixepoch'), 'start of month', '+1 months', '-1 day') last_day,
DATE(strftime('%Y-%m-%d', hiredate * 3600 * 24, 'unixepoch'), 'start of month', '+1 months', '-1 day' , '+5 month') Mon5_last_day
FROM emp
ORDER BY empno, hiredate ")[1:10, ]
| Results |
ename ename hiredate last_day Mon5_last_day
1 SMITH SMITH 1980-12-17 1980-12-31 1981-05-31
2 ALLEN ALLEN 1981-02-20 1981-02-28 1981-07-28
3 WARD WARD 1981-02-22 1981-02-28 1981-07-28
4 JONES JONES 1981-04-02 1981-04-30 1981-09-30
5 MARTIN MARTIN 1981-09-28 1981-09-30 1982-03-02
6 BLAKE BLAKE 1981-03-01 1981-03-31 1981-08-31
7 CLARK CLARK 1981-01-09 1981-01-31 1981-07-01
8 SCOTT SCOTT 1982-12-09 1982-12-31 1983-05-31
9 KING KING 1981-11-17 1981-11-30 1982-04-30
10 TURNER TURNER 1981-09-08 1981-09-30 1982-03-02
6. Python pandasql Package
datetime() 함수
| Python Programming |
ps.sqldf("select datetime('2020-08-30 12:54:12', 'start of day') day_start, \
datetime('2020-08-30 12:54:12', 'start of month') mon_start, \
datetime('2020-08-30 12:54:12', 'start of year') year_start ")
| Results |
day_start mon_start year_start
0 2020-08-30 00:00:00 2020-08-01 00:00:00 2020-01-01 00:00:00
DATE() 함수
| Python Programming |
ps.sqldf(" SELECT ename, hiredate, \
DATE(date(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2)), 'start of month', '+1 months', '-1 day') last_day, \
DATE(date(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2)), 'start of month', '+1 months', '-1 day' , '+5 month') last_day \
FROM emp \
ORDER BY empno, hiredate ").head(7)
| Results |
ename hiredate last_day last_day
0 SMITH 1980/12/17 1980-12-31 1981-05-31
1 ALLEN 1981/02/20 1981-02-28 1981-07-28
2 WARD 1981/02/22 1981-02-28 1981-07-28
3 JONES 1981/04/02 1981-04-30 1981-09-30
4 MARTIN 1981/09/28 1981-09-30 1982-03-02
5 BLAKE 1981/03/01 1981-03-31 1981-08-31
6 CLARK 1981/01/09 1981-01-31 1981-07-01
7. R data.table Package
as.Date() 함수
add_date : 기준일자가 속한 현재 월의 마지막 날짜를 계산하기 위하여 먼저 해당 월의 초일자를 계산하고, 당월 초일자에서 1개월 경과한 다음 달 초일자를 계산 후 1일을 빼는 방식으로 계산한다.
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
# DT[, .add_date := as.numeric(format( (lubridate::ceiling_date(as.Date(hiredate),"month") - 1) %m+% months(5) , "%Y%m%d")) ]
DT[, .add_date := (lubridate::ceiling_date(as.Date(hiredate),"month") - 1) ][1:10, ]
| Results |
empno ename job mgr hiredate sal comm deptno .add_date
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1980-12-31
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-02-28
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-02-28
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-04-30
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981-09-30
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981-03-31
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1981-01-31
8: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 1982-12-31
9: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 1981-11-30
10: 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1981-09-30
8. Python Duckdb의 SQL
Date_trunc() 함수
| Python Programming |
%%sql
select date_trunc('day', TIMESTAMP '2020-08-30 12:54:12') DAY_START,
strftime( date_trunc('day', TIMESTAMP '2020-08-30 12:54:12'), '%Y-%m-%d %H:%M:%S') AS DAY_START_char,
date_trunc('MONTH', TIMESTAMP '2020-08-30 12:54:12') AS DAY_START,
date_trunc('YEAR', TIMESTAMP '2020-08-30 12:54:12') AS YEAR_START
| Python Programming |
select date_trunc('day', TIMESTAMP '2020-08-30 12:54:12') DAY_START, \
strftime( date_trunc('day', TIMESTAMP '2020-08-30 12:54:12'), '%Y-%m-%d %H:%M:%S') AS DAY_START_char, \
date_trunc('MONTH', TIMESTAMP '2020-08-30 12:54:12') AS DAY_START, \
date_trunc('YEAR', TIMESTAMP '2020-08-30 12:54:12') AS YEAR_START ").df()
| Results |
DAY_START DAY_START_char DAY_START_2 YEAR_START
0 2020-08-30 2020-08-30 00:00:00 2020-08-01 2020-01-01
| Python Programming |
%%sql
select ename, hiredate,
date_trunc('month', cast(hiredate as date)) + INTERVAL 1 month - interval 1 day as last_day,
date_trunc('month', cast(hiredate as date)) + INTERVAL 1 month - interval 1 day as last_day_1,
date_trunc('month', cast(hiredate as date)) + INTERVAL 1 month - interval 1 day + INTERVAL 5 month as last_day
from emp
ORDER BY empno, hiredate
LIMIT 6
| Python Programming |
duckdb.sql(" select ename, hiredate, \
date_trunc('month', cast(hiredate as date)) + INTERVAL 1 month - interval 1 day as last_day, \
date_trunc('month', cast(hiredate as date)) + INTERVAL 1 month - interval 1 day as last_day_1, \
date_trunc('month', cast(hiredate as date)) + INTERVAL 1 month - interval 1 day + INTERVAL 5 month as last_day \
from emp \
ORDER BY empno, hiredate \
LIMIT 6 ").df()
| Results |
ename hiredate last_day last_day_1 last_day_2
0 SMITH 1980/12/17 1980-12-31 1980-12-31 1981-05-31
1 ALLEN 1981/02/20 1981-02-28 1981-02-28 1981-07-28
2 WARD 1981/02/22 1981-02-28 1981-02-28 1981-07-28
3 JONES 1981/04/02 1981-04-30 1981-04-30 1981-09-30
4 MARTIN 1981/09/28 1981-09-30 1981-09-30 1982-02-28
5 BLAKE 1981/03/01 1981-03-31 1981-03-31 1981-08-31
--------------------------------------------
| [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table] 오라클 함수와 R & Python 비교 사전 목록 링크 |
| 오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크 |
| [SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글