포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ NUMTODSINTERVAL Oracle Function ]
NUMTODSINTERVAL함수는 입력된 인수 n을 INTERVAL DAY TO SECOND 문자(날짜-시간 구간 형식)로 변경한다. 인수 n은 Number 값 또는 암묵적으로 NUMBER 값으로 변환 가능한 식을 사용할 수 있다. 인수 interval_unit는 CHAR, VARCHAR2, NCHAR, NVARCHAR2 데이터형이다. Interval_unit에 값은 n의 단위를 지정하고, 다음 문자 값을 지정해야 한다.
- 함수 설명 : NUMTODSINTERVAL 오라클 함수 링크
- LAST_DAY / TO_DSINTERVAL 함수 참조
1. Oracle(오라클)
| Oracle Programming |
SELECT TO_CHAR(SYSDATE+ NUMTODSINTERVAL(-30,'MINUTE') ,'yyyymmddhh24miss') NUMTODSINTERVAL_VAR
FROM DUAL
| Results |
NUMTODSINTERVAL_VAR
-----------------------
20240106075125
| Python Programming |
SELECT CONCAT( TO_CHAR( SYSDATE - 1/24/6*3, 'YYYYMMDDHH24MI' ), '00' ) DATE_VAR
FROM DUAL
| Results |
DATE_VAR
----------------
20240106075200
2. Python Pandas(파이썬)
1970년 1월 1일을 기준이 특정 기간(30년 5개월 30일 10시간 30초)이 경과된 날짜와 시간을 반환한다.
| Python Programming |
pd.to_datetime('1970-01-01 01:00:00.0') + pd.DateOffset(years=30)+ pd.DateOffset(months=5)+ pd.DateOffset(days=30) + \
pd.DateOffset(hours=10)+ pd.DateOffset(second=30)
| Results |
Timestamp('2000-07-01 11:00:30')
개별 사원의 입사일 이후 5개월이 경과된 날짜를 반환한다.
| Python Programming |
( pd.to_datetime(emp['hiredate']) + pd.DateOffset(months=5) ).head(10)
| Results |
0 1981-05-17
1 1981-07-20
2 1981-07-22
3 1981-09-02
4 1982-02-28
5 1981-08-01
6 1981-06-09
7 1983-05-09
8 1982-04-17
9 1982-02-08
Name: hiredate, dtype: datetime64[ns]
개별 사원을 기준으로 입사 월의 마지막 일자를 계산 후 5개월이 경과된 날짜를 반환한다.
| 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 1981-05-31
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 1981-07-28
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 1981-07-28
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 1981-09-30
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 1982-02-28
5 7698 BLAKE MANAGER 7839.0 1981/03/01 2850 NaN 30 1981-08-31
6 7782 CLARK MANAGER 7839.0 1981/01/09 2450 NaN 10 1981-06-30
개별 사원의 입사월 마지막 일자를 계산 후 5개월이 경과된 날짜를 반환한다.
| Python Programming |
###### from dateutil.relativedelta import relativedelta, FR
from pandas.tseries.offsets import MonthEnd
from dateutil.relativedelta import relativedelta
import copy
withmooc =copy.copy(emp)
withmooc['add_Month'] = withmooc.apply(lambda row: pd.to_datetime(row['hiredate'], format='%Y %m %d')+ MonthEnd(1) + relativedelta(months=5),
axis=1 )
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 1981-05-31
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 1981-07-28
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 1981-07-28
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 1981-09-30
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 1982-02-28
5 7698 BLAKE MANAGER 7839.0 1981/03/01 2850 NaN 30 1981-08-31
6 7782 CLARK MANAGER 7839.0 1981/01/09 2450 NaN 10 1981-06-30
| Python Programming |
from dateutil.relativedelta import relativedelta, FR
import copy
withmooc =copy.copy(emp)
withmooc['add_Month'] = [pd.to_datetime(x, format='%Y %m %d') + MonthEnd(1) + relativedelta(months=5)
for x in withmooc['hiredate']]
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 1981-05-31
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 1981-07-28
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 1981-07-28
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 1981-09-30
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 1982-02-28
5 7698 BLAKE MANAGER 7839.0 1981/03/01 2850 NaN 30 1981-08-31
6 7782 CLARK MANAGER 7839.0 1981/01/09 2450 NaN 10 1981-06-30
3. R Programming (R Package)
1970년 1월 1일을 기준이 특정 기간(30년/5개월/30일/10시간/30초)이 경과된 날짜와 시간을 반환한다.
| R Programming |
%%R
ymd_hms("1970-01-01 01:00:00.0") %m+% years(30) %m+% months(5) %m+% days(30) %m+% hours(10) %m+% seconds(30)
| Results |
[1] "2000-07-01 11:00:30 UTC"
개별 사원의 입사일 이후 5개월이 경과된 날짜를 반환한다.
| R Programming |
%%R
as.Date(emp$hiredate) %m+% months(5)
| R Programming |
%%R
# 현재일자
print( Sys.Date() )
library(lubridate)
# 현재월의 마지막 날짜를 반환한다. 이를 위하 다음 달 1일을 계산 후 1일을 빼는 방식을 계산한다.
print( 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-07"
[1] "2021-02-28"
Time difference of 21 days
Time difference of 21 days
개별 사원의 입사월 마지막 일자를 계산 후 5개월이 경과된 날짜를 반환한다.
| 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 1981-05-31
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-07-28
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-07-28
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-09-30
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1982-02-28
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981-08-31
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1981-06-30
8 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 1983-05-31
9 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 1982-04-30
10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1982-02-28
4. R Dplyr Package
개별 사원의 입사일 이후 5개월이 경과된 날짜를 반환한다.
| R Programming |
%%R
emp %>%
dplyr::mutate(add_date = as.Date(hiredate) %m+% months(5)) %>%
head(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 1981-05-17
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-07-20
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-07-22
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-09-02
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1982-02-28
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981-08-01
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1981-06-09
8 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 1983-05-09
9 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 1982-04-17
10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1982-02-08
| R Programming |
%%R
library(lubridate)
library(mondate) # 월단위 날짜 처리
emp %>%
dplyr::mutate(empno,ename,hiredate,
month_end = (lubridate::ceiling_date(as.Date(hiredate),"month") - days(1)), # 입사월 마지막 일자
add_date = as.Date(mondate::mondate(month_end) + 5), # 입사월 이후 5개월이 경과된 월의 마지막 날짜
add_date_1 = month_end %m+% months(5), # 개별 사원의 입사월 마지막 일자를 계산 후 5개월이 경과된 날짜
add_date_2 = lubridate::add_with_rollback(month_end, months(5)) # 개별 사원의 입사월 마지막 일자를 계산 후 5개월이 경과된 날짜
) %>%
dplyr::select(empno,ename,hiredate,month_end,add_date,add_date_1,add_date_2) %>%
head()
| Results |
# A tibble: 6 x 7
empno ename hiredate month_end add_date add_date_1 add_date_2
<dbl> <chr> <date> <date> <date> <date> <date>
1 7369 SMITH 1980-12-17 1980-12-31 1981-05-31 1981-05-31 1981-05-31
2 7499 ALLEN 1981-02-20 1981-02-28 1981-07-31 1981-07-28 1981-07-28
3 7521 WARD 1981-02-22 1981-02-28 1981-07-31 1981-07-28 1981-07-28
4 7566 JONES 1981-04-02 1981-04-30 1981-09-30 1981-09-30 1981-09-30
5 7654 MARTIN 1981-09-28 1981-09-30 1982-02-28 1982-02-28 1982-02-28
6 7698 BLAKE 1981-03-01 1981-03-31 1981-08-31 1981-08-31 1981-08-31
5. R sqldf Package
기준 일자를 기준으로 특정 기간이 경과된 날짜-시간을 반환한다.
| R Programming |
%%R
sqldf(" select datetime('1970-01-01 01:00:00.0', '+' || 2 || ' year') year_add,
datetime('1970-01-01 01:00:00.0', '+' || 2 || ' month') mon_add,
datetime('1970-01-01 01:00:00.0', '+' || 2 || ' day') day_add,
datetime('1970-01-01 01:00:00.0', '+' || 2 || ' hour') hour_add,
datetime('1970-01-01 01:00:00.0', '+' || 2 || ' minute') mim_add,
datetime('1970-01-01 01:00:00.0', '+' || 2 || ' second') sec_add ")
| Results |
year_add mon_add day_add
1 1972-01-01 01:00:00 1970-03-01 01:00:00 1970-01-03 01:00:00
hour_add mim_add sec_add
1 1970-01-01 03:00:00 1970-01-01 01:02:00 1970-01-01 01:00:02
1970년 1월 1일을 기준이 특정 기간(30년/5개월/30일/10시간/30초)이 경과된 날짜와 시간을 반환한다.
| R Programming |
%%R
sqldf(" select strftime('%Y-%m-%d %H:%M:%f',datetime('1970-01-01 01:00:00.0','+30 years','+5 months','+30 days','+10 hours','+30 second')) add_time")
| Results |
add_time
1 2000-07-01 11:00:30.000
| R Programming |
%%R
sqldf(" SELECT date('now') now_day_1,
strftime('%d-%m-%Y', 'now') now_day_2,
date('now','start of month','+1 month','-1 day') last_day,
strftime('%d',datetime('now','start of month','+1 month','-1 second')) last_day_1,
strftime('%d',datetime('now','start of month','+1 month','-1 second')) - strftime('%d-%m-%Y', 'now') Days_left")
| Results |
now_day_1 now_day_2 last_day last_day_1 Days_left
1 2021-02-06 06-02-2021 2021-02-28 28 22
| 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') last_day
FROM emp
ORDER BY empno, hiredate ")[1:10, ]
| Results |
ename ename hiredate last_day 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
| Python Programming |
ps.sqldf(" select datetime('1970-01-01 01:00:00.0', '+' || 2 || ' year') year_add, \
datetime('1970-01-01 01:00:00.0', '+' || 2 || ' month') mon_add, \
datetime('1970-01-01 01:00:00.0', '+' || 2 || ' day') day_add, \
datetime('1970-01-01 01:00:00.0', '+' || 2 || ' hour') hour_add, \
datetime('1970-01-01 01:00:00.0', '+' || 2 || ' minute') min_add, \
datetime('1970-01-01 01:00:00.0', '+' || 2 || ' second') sec_add ")
| Results |
year_add mon_add day_add hour_add min_add sec_add
0 1972-01-01 01:00:00 1970-03-01 01:00:00 1970-01-03 01:00:00 1970-01-01 03:00:00 1970-01-01 01:02:00 1970-01-01 01:00:021970년 1월 1일을 기준이 특정 기간(30년/5개월/30일/10시간/30초)이 경과된 날짜와 시간을 반환한다.
| Python Programming |
ps.sqldf(" select strftime('%Y-%m-%d %H:%M:%f',datetime('1970-01-01 01:00:00.0','+30 years','+5 months','+30 days','+10 hours','+30 second')) add_time ")
| Results |
add_time
0 2000-07-01 11:00:30.000
| Python Programming |
ps.sqldf(" SELECT date('now') now_day_1, \
strftime('%d-%m-%Y', 'now') now_day_2, \
date('now','start of month','+1 month','-1 day') last_day, \
strftime('%d',datetime('now','start of month','+1 month','-1 second')) last_day_1, \
strftime('%d',datetime('now','start of month','+1 month','-1 second')) - strftime('%d-%m-%Y', 'now') Days_left ")
| Results |
now_day_1 now_day_2 last_day last_day_1 Days_left
0 2021-02-06 06-02-2021 2021-02-28 28 22
| 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()
| 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
7. R data.table Package
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
# DT[, .add_date := as.numeric(format( as.Date(hiredate) %m+% months(5) , "%Y%m%d")) ]
DT[, add_date := as.Date(hiredate) %m+% months(5) ][1:10, ]
| Results |
empno ename job mgr hiredate sal comm deptno add_date
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1981-05-17
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-07-20
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-07-22
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-09-02
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1982-02-28
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981-08-01
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1981-06-09
8: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 1983-05-09
9: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 1982-04-17
10: 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1982-02-08
| 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) %m+% months(5) ][1:10, ]
| Results |
empno ename job mgr hiredate sal comm deptno add_date
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1981-05-31
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-07-28
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-07-28
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-09-30
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1982-02-28
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981-08-31
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1981-06-30
8: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 1983-05-31
9: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 1982-04-30
10: 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 1982-02-28
8. Python Duckdb의 SQL
| Python Programming |
%%sql
select current_date,
current_time,
now() current_datetime,
strftime(now() - INTERVAL 30 MINUTE, '%Y%m%d%H%M%S') AS NUMTODSINTERVAL_1,
strftime(now() - INTERVAL 30 MINUTE, '%Y%m%d%H%M')||'00' AS NUMTODSINTERVAL_2
| Python Programming |
duckdb.sql(" select current_date, \
current_time, \
now() current_datetime, \
strftime(now() - INTERVAL 30 MINUTE, '%Y%m%d%H%M%S') AS NUMTODSINTERVAL_1, \
strftime(now() - INTERVAL 30 MINUTE, '%Y%m%d%H%M')||'00' AS NUMTODSINTERVAL_2 ").df()
| Results |
current_date current_time current_datetime \
0 2023-08-31 23:50:23.989 2023-08-31 23:50:23.989000+00:00
NUMTODSINTERVAL_1 NUMTODSINTERVAL_2
0 20230831232023 20230831232000
| Python Programming |
%%sql
select TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 2 year year_add,
TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 2 month mon_add,
TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 2 day day_add,
TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 2 hour hour_add,
TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 2 minute min_add,
TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 2 second sec_add
| Python Programming |
duckdb.sql(" select TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 2 year year_add, \
TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 2 month mon_add, \
TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 2 day day_add, \
TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 2 hour hour_add, \
TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 2 minute min_add, \
TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 2 second sec_add ").df()
| Results |
year_add mon_add day_add \
0 1972-01-01 01:00:00 1970-03-01 01:00:00 1970-01-03 01:00:00
hour_add min_add sec_add
0 1970-01-01 03:00:00 1970-01-01 01:02:00 1970-01-01 01:00:02
| Python Programming |
%%sql
select strftime('%Y-%m-%d %H:%M:%S.%g', TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 30 year + INTERVAL 5 months + INTERVAL 30 days + INTERVAL 10 hours + INTERVAL 30 second) as add_time
| Python Programming |
duckdb.sql(" select strftime('%Y-%m-%d %H:%M:%S.%g', TIMESTAMP '1970-01-01 01:00:00.0' + INTERVAL 30 year + INTERVAL 5 months + INTERVAL 30 days + INTERVAL 10 hours + INTERVAL 30 second) as add_time ").df()
| Results |
add_time
0 2000-07-01 11:00:30.000
| Python Programming |
%%sql
SELECT CURRENT_DATE now_day_1,
strftime('%d-%m-%Y', CURRENT_DATE) now_day_2,
date_trunc('month', CURRENT_DATE) + INTERVAL 1 month - interval 1 day as last_day,
day(date_trunc('month', current_timestamp) + INTERVAL 1 month - interval 1 second) as last_day_1,
day(date_trunc('month', current_timestamp) + INTERVAL 1 month - interval 1 second) - day(CURRENT_DATE) as Days_left
| Python Programming |
print( duckdb.sql(" SELECT CURRENT_DATE now_day_1, \
strftime('%d-%m-%Y', CURRENT_DATE) now_day_2, \
date_trunc('month', CURRENT_DATE) + INTERVAL 1 month - interval 1 day as last_day, \
day(date_trunc('month', current_timestamp) + INTERVAL 1 month - interval 1 second) as last_day_1, \
day(date_trunc('month', current_timestamp) + INTERVAL 1 month - interval 1 second) - day(CURRENT_DATE) as Days_left ").df() )
| Results |
now_day_1 now_day_2 last_day last_day_1 Days_left
0 2023-09-01 01-09-2023 2023-09-30 30 29
| Python Programming |
%%sql
SELECT ename,
hiredate,
date_trunc ('month', cast(hiredate as date) ) + INTERVAL 1 month - interval 1 day as last_day_0,
date_trunc ('month', cast(hiredate as date) ) + INTERVAL 1 month - interval 1 day + INTERVAL 5 month as last_day_1
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_0, \
date_trunc ('month', cast(hiredate as date) ) + INTERVAL 1 month - interval 1 day + INTERVAL 5 month as last_day_1 \
FROM emp \
ORDER BY empno, hiredate \
LIMIT 6 ").df()
| Results |
ename hiredate last_day_0 last_day_1
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-02-28
5 BLAKE 1981/03/01 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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글