ROUND(date) 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]

* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크

---

[ ROUND(date) Oracle Function ]

 

---

ROUND함수는 날짜(Date)를 지정한 format(fmt)에 지정한 단위로 반올림한 날짜를 반환한다. 반환된 값은 비록 date와 다른 datetime형을 지정했어도, 항상 DATE 데이터형을 반환한다. 만약 fmt를 생략하면, date는 day 근처로 반올림된다. date 표현은 DATE값으로 변화될 필요가 있다.

 

• 함수 설명 : ROUND(date) 오라클 함수 링크

 

---

1. Oracle(오라클)

 

Oracle Programming

SELECT ROUND(TO_DATE('2023/01/22','YYYY/MM/DD'), 'YEAR') AS TO_DATE
FROM   DUAL

 

Results

       TO_DATE
------------------------
01-JAN-2023 00:00:00

 

---

2. Python Pandas(파이썬)

 

Python Programming

display( pd.to_datetime(["2020-06-20 00:00:00"])+ pd.offsets.MonthBegin(-1)  )   # 해당월의 시작일
display( pd.to_datetime(["2020-06-20 00:00:00"])+ pd.offsets.YearBegin(-1)   )   # 년도의 시작일
display( pd.to_datetime(["2020-06-20 00:00:00"])+ pd.offsets.YearEnd(0)      )   # 년도의 마지막 일자

 

Results

DatetimeIndex(['2020-06-01'], dtype='datetime64[ns]', freq=None)



DatetimeIndex(['2020-01-01'], dtype='datetime64[ns]', freq=None)



DatetimeIndex(['2020-12-31'], dtype='datetime64[ns]', freq=None)

 

---

truncate('year')

날짜에서 연도를 기준으로 연도의 초일자로 날짜를 절단한다(자른다).

 

Python Programming

from delorean import Delorean
from datetime import datetime, timedelta

Delorean(datetime=datetime(2020, 6, 20, 15, 49, 36, 630521), timezone='UTC').truncate('year')

 

Results

Delorean(datetime=datetime.datetime(2020, 1, 1, 0, 0), timezone='UTC')

 

---

truncate('month')

날짜에서 월을 기준으로 월의 초일자로 날짜를 절단한다(자른다).

 

Python Programming

from delorean import Delorean
from datetime import datetime, timedelta

Delorean(datetime=datetime(2020, 6, 20, 15, 49, 36, 630521), timezone='UTC').truncate('month')

 

Results

Delorean(datetime=datetime.datetime(2020, 6, 1, 0, 0), timezone='UTC')

 

---

ceil_dt()

가장 가까운 30분 단위로 반올림한다.

 

Python Programming

from datetime import datetime, timedelta

def ceil_dt(dt, delta):
    return dt + (datetime.min - dt) % delta

now = datetime.now()    # datetime(2020, 6, 20, 15, 49, 36, 630521)
print(now)    
print(ceil_dt(now, timedelta(minutes=30)))

 

Results

2021-02-10 15:49:41.579221
2021-02-10 16:00:00

 

---

Pandas Timestamp.round

• DateOffset objects : https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html

입사일자를 2일 단위로 반올림한다. ( datetime 단위로 처리되어서 DateOffset 중에서 시간 단위 인수만 지정 가능한 듯. 월/분기 등의 인수 지정 시 에러 )

 

Python Programming

withmooc = copy.copy(emp)

# pd.to_datetime('1981-4-2 00:00:00').round('2d')
# pd.to_datetime('1981-4-2 01:00:00').round('2d')

withmooc['new_hire'] = pd.to_datetime(withmooc['hiredate']).dt.round(freq='2D')

withmooc.head()

 

Results

	empno	ename	job		mgr	hiredate	sal	comm	deptno	new_hire
0	7369	SMITH	CLERK		7902.0	1980/12/17	800	NaN	20	1980-12-18
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	1981-02-20
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	1981-02-22
3	7566	JONES	MANAGER		7839.0	1981/04/02	2975	NaN	20	1981-04-01
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	1981-09-28

 

---

[참고] relativedelta() 함수

기준일자를 기준으로 지정한 기간이 경과된 날짜를 반환한다.

 

Python Programming

from datetime import *

from dateutil.relativedelta import *

print( datetime.strptime('2020-12-03', '%Y-%m-%d') + relativedelta(years=-1) )  # 기준일자(2020-12-03)로 부터 1년 전 날짜를 반환한다.

datetime(year=2015, month=3, day=31) + relativedelta(years=+1, months=+1)       # 기준일자(2015-03-31)로 부터 1sus 1개월 이후 날짜를 반환한다.

 

Results

2019-12-03 00:00:00





datetime.datetime(2016, 4, 30, 0, 0)

 

 

---

3. R Programming (R Package)

 

lubridate::round_date()

지정한 일자에 대하여 연도를 기준으로 반올림한다.

 

R Programming

%%R

lubridate::round_date(lubridate::ymd_hms("2009-08-03 12:01:59.23"), "year")

 

Results

[1] "2010-01-01 UTC"

 

---

lubridate::floor_date()

지정한 일자에 대하여 연도를 기준으로 내림한다.

 

R Programming

%%R

lubridate::floor_date(lubridate::ymd_hms("2009-08-03 12:01:59.23"), "year")

 

Results

[1] "2009-01-01 UTC"

 

---

lubridate::ceiling_date()

지정한 일자에 대하여 연도를 기준으로 올림 한다.

 

R Programming

%%R

lubridate::ceiling_date(lubridate::ymd_hms("2009-08-03 12:01:59.23"), "year")

 

Results

[1] "2010-01-01 UTC"

 

 

---

4. R Dplyr Package

 

lubridate::round_date(), lubridate::floor_date(), lubridate::ceiling_date()

 

%%R

emp %>%
  dplyr::mutate( hire_round = lubridate::round_date(as.Date(hiredate), "year"),         ## 년도를 기준으로 반올림
                 hire_floor = lubridate::floor_date(as.Date(hiredate), "year"),         ## 년도를 기준으로 내림
                 hire_ceil  = lubridate::ceiling_date(as.Date(hiredate), "year")) %>%   ## 년도를 기준으로 올림
  dplyr::select(-c("mgr","job")) %>%
  head()

 

Results

# A tibble: 6 x 9
  empno ename  hiredate     sal  comm deptno hire_round hire_floor hire_ceil 
  <dbl> <chr>  <date>     <dbl> <dbl>  <dbl> <date>     <date>     <date>    
1  7369 SMITH  1980-12-17   800    NA     20 1981-01-01 1980-01-01 1981-01-01
2  7499 ALLEN  1981-02-20  1600   300     30 1981-01-01 1981-01-01 1982-01-01
3  7521 WARD   1981-02-22  1250   500     30 1981-01-01 1981-01-01 1982-01-01
4  7566 JONES  1981-04-02  2975    NA     20 1981-01-01 1981-01-01 1982-01-01
5  7654 MARTIN 1981-09-28  1250  1400     30 1982-01-01 1981-01-01 1982-01-01
6  7698 BLAKE  1981-03-01  2850    NA     30 1981-01-01 1981-01-01 1982-01-01

 

 

---

5. R sqldf Package

 

datetime()

 

%%R

sqldf(" select datetime(date('1981-01-09'),'start of year') year_start ")

 

Results

           year_start
1 1981-01-01 00:00:00

 

 

---

6. Python pandasql Package

 

datetime()

 

Python Programming

ps.sqldf(" select datetime(date('1981-01-09'),'start of year') year_start ")

 

Results

	year_start
0	1981-01-01 00:00:00

 

 

---

7. R data.table Package

 

lubridate::round_date(), lubridate::floor_date(), lubridate::ceiling_date()

 

R Programming

%%R

DT          <- data.table(emp)
dept_DT     <- data.table(dept)

DT[, `:=`( hire_round = lubridate::round_date(as.Date(hiredate), "year"),         ## 년도를 기준으로 반올림
           hire_floor = lubridate::floor_date(as.Date(hiredate), "year"),         ## 년도를 기준으로 내림
           hire_ceil  = lubridate::ceiling_date(as.Date(hiredate), "year") ) ][, c("job","ename") := NULL][1:10]  ## 년도를 기준으로 올림

 

Results

    empno  mgr   hiredate  sal comm deptno hire_round hire_floor  hire_ceil
 1:  7369 7902 1980-12-17  800   NA     20 1981-01-01 1980-01-01 1981-01-01
 2:  7499 7698 1981-02-20 1600  300     30 1981-01-01 1981-01-01 1982-01-01
 3:  7521 7698 1981-02-22 1250  500     30 1981-01-01 1981-01-01 1982-01-01
 4:  7566 7839 1981-04-02 2975   NA     20 1981-01-01 1981-01-01 1982-01-01
 5:  7654 7698 1981-09-28 1250 1400     30 1982-01-01 1981-01-01 1982-01-01
 6:  7698 7839 1981-03-01 2850   NA     30 1981-01-01 1981-01-01 1982-01-01
 7:  7782 7839 1981-01-09 2450   NA     10 1981-01-01 1981-01-01 1982-01-01
 8:  7788 7566 1982-12-09 3000   NA     20 1983-01-01 1982-01-01 1983-01-01
 9:  7839   NA 1981-11-17 5000   NA     10 1982-01-01 1981-01-01 1982-01-01
10:  7844 7698 1981-09-08 1500    0     30 1982-01-01 1981-01-01 1982-01-01

 

---

8. Python Duckdb의 SQL

 

date_trunc() 함수

 

Python Programming

%%sql
  select date_trunc('month', date '1981-01-09') as date_round

 

Python Programming

duckdb.sql(" select date_trunc('month', date '1981-01-09') as date_round ").df()

 

Results

  date_round
0 1981-01-01

 

---

Alarm clock friends situation with hand (https://unsplash.com/photos/UAvYasdkzq8)

  --------------------------------------------  

 

 

[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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크