포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ TO_CHAR(datetime) Oracle Function ]
TO_CHAR(datetime) 함수는 날짜형 데이터 값을 사용자가 지정한 날짜시간(date) 포맷 형식(fmt)을 따르는 VARCHAR2 데이터 타입 문자열로 변환하여 반환한다. 만약 fmt를 생략한다면, date는 다음과 같이 VARCHAR2 값으로 변환한다.
- 함수설명 : TO_CHAR(datetime) 오라클 함수 링크
1. Oracle(오라클)
TO_CHAR(sysdate)
현재 날짜를 문자형으로 변경하여 반환한다.
| Oracle Programming |
SELECT TO_CHAR(SYSDATE) DATE_VAR
FROM DUAL
| Results |
DATE_VAR
------------------------
06-JAN-2024 08:32:13
2. Python Pandas(파이썬)
datetime.now().strftime('%Y-%m-%d')
| Python Programming |
from datetime import datetime
print(datetime.strptime('2020-12-16','%Y-%m-%d')) # 날짜형
datetime.now().strftime('%Y-%m-%d') # 문자형
| Results |
2020-12-16 00:00:00
'2021-02-14'
datetime.now().strftime('%Y-%m-%d')
| Python Programming |
withmooc = copy.copy(emp)
withmooc['CHAR_DATE'] = datetime.now().strftime('%Y-%m-%d')
withmooc.head()
| Results |
empno ename job mgr hiredate sal comm deptno CHAR_DATE
0 7369 SMITH CLERK 7902.0 1980/12/17 800 NaN 20 2021-02-14
1 7499 ALLEN SALESMAN 7698.0 1981/02/20 1600 300.0 30 2021-02-14
2 7521 WARD SALESMAN 7698.0 1981/02/22 1250 500.0 30 2021-02-14
3 7566 JONES MANAGER 7839.0 1981/04/02 2975 NaN 20 2021-02-14
4 7654 MARTIN SALESMAN 7698.0 1981/09/28 1250 1400.0 30 2021-02-14
3. R Programming (R Package)
as.character()
| R Programming |
%%R
print( as.Date('2020-12-16','%Y-%m-%d') ) # 날짜형으로 반환
as.character(Sys.Date(),'%Y-%m-%d') # 문자형으로 반환
| Results |
[1] "2020-12-16"
[1] "2020-12-16"
as.character()
현재 날짜를 문자형으로 변경하여 반환한다.
| R Programming |
%%R
withmooc <- emp
withmooc['CHAR_DATE'] = as.character(Sys.Date(),'%Y-%m-%d')
withmooc[1:10, ]
| Results |
# A tibble: 10 x 9
empno ename job mgr hiredate sal comm deptno CHAR_DATE
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <chr>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 2021-02-14
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 2021-02-14
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 2021-02-14
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 2021-02-14
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 2021-02-14
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 2021-02-14
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 2021-02-14
8 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 2021-02-14
9 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 2021-02-14
10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 2021-02-14
4. R Dplyr Package
as.character() 함수
| R Programming |
%%R
emp %>%
dplyr::mutate(CHAR_DATE = as.character(Sys.Date(),'%Y-%m-%d')) %>%
head(10)
| Results |
# A tibble: 10 x 9
empno ename job mgr hiredate sal comm deptno CHAR_DATE
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <chr>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 2021-02-14
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 2021-02-14
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 2021-02-14
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 2021-02-14
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 2021-02-14
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 2021-02-14
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 2021-02-14
8 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 2021-02-14
9 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 2021-02-14
10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 2021-02-14
5. R sqldf Package
strftime()
| R Programming |
%%R
sqldf(" SELECT STRFTIME('%Y%m%d', DATETIME('NOW')) CHAR_DATE ")
| Results |
CHAR_DATE
1 20201215
6. Python pandasql Package
strftime()
| Python Programming |
ps.sqldf(" SELECT STRFTIME('%Y%m%d', DATETIME('NOW')) CHAR_DATE ")
| Results |
CHAR_DATE
0 20201215
7. R data.table Package
as.character()
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[,CHAR_DATE := as.character(Sys.Date(),'%Y-%m-%d')][1:10, ]
| Results |
empno ename job mgr hiredate sal comm deptno CHAR_DATE
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 2021-02-14
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 2021-02-14
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 2021-02-14
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 2021-02-14
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 2021-02-14
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 2021-02-14
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 2021-02-14
8: 7788 SCOTT ANALYST 7566 1982-12-09 3000 NA 20 2021-02-14
9: 7839 KING PRESIDENT NA 1981-11-17 5000 NA 10 2021-02-14
10: 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 2021-02-14
8. Python Duckdb의 SQL
| Python Programming |
%%sql
select current_date ,
typeof(current_date) as Date_type_1,
STRFTIME(current_date, '%Y%m%d') as CHAR_DATE,
typeof(STRFTIME(current_date, '%Y%m%d')) as Date_type_2
| Python Programming |
duckdb.sql(" select current_date , \
typeof(current_date) as Date_type_1, \
STRFTIME(current_date, '%Y%m%d') as CHAR_DATE, \
typeof(STRFTIME(current_date, '%Y%m%d')) as Date_type_2 ").df()
| Results |
current_date Date_type_1 CHAR_DATE Date_type_2
0 2023-09-05 DATE 20230905 VARCHAR
--------------------------------------------
| [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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글