TO_TIMESTAMP_TZ 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]

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

---

[ TO_TIMESTAMP_TZ Oracle Function ]

 

---

TO_TIMESTAMP 함수를 사용하여서 문자로 지정한 날짜/시간 데이터를 TIMESTAMP WITH TIME ZONE 데이터형을 따르는 값으로 변환한다. TIMESTAMP WITH TIME ZONE 데이터형은 GMT 기준 시차를 함께 출력한다.

 

• 함수 설명 : TO_TIMESTAMP_TZ 오라클 함수 링크
• NEW_TIME 함수 : NEW_TIME 오라클 함수 비교 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table] 링크

 

---

1. Oracle(오라클)

 

TO_TIMESTAMP_TZ 함수

TO_TIMESTAMP_TZ 함수를 사용하여서 문자로 지정한 날짜/시간 데이터를 TIMESTAMP WITH TIME ZONE 데이터형을 따르는 값으로 변환한다.

 

Oracle Programming

SELECT TO_TIMESTAMP_TZ('1998/09/27 22:05:21.089','YYYY-MM-DD HH24:MI:SS.FF') TO_TIMESTAMP_TZ_FUNC
FROM   DUAL

 

Results

        TO_TIMESTAMP_TZ_FUNC
------------------------------------------
27-SEP-98 10.05.21.089000 PM US/PACIFIC

 

 

---

2. Python Pandas(파이썬)

 

astimezone(pytz.utc)

 

Python Programming

import datetime, pytz
from dateutil.tz.tz import tzoffset

loc = datetime.datetime(2020, 4, 19, 21, 12, tzinfo=tzoffset(None, -7200))

print(loc.astimezone(pytz.utc).strftime('%Y-%m-%d %H:%M:%S.%f') )

 

Results

2020-04-19 23:12:00.000000

 

---

dt.tz_localize()

 

Python Programming

import pandas as pd
base_datetime = pd.Series(['2020-11-10 01:23:45'])
base_datetime = pd.to_datetime(base_datetime)
display(base_datetime)

chagne_datetime = base_datetime.dt.tz_localize("America/Halifax").dt.tz_convert('US/Pacific')
chagne_datetime

 

Results

0   2020-11-10 01:23:45
dtype: datetime64[ns]





0   2020-11-09 21:23:45-08:00
dtype: datetime64[ns, US/Pacific]

 

---

astimezone() 함수

 

Python Programming

from datetime import datetime
import pytz
from tzlocal import get_localzone # $ pip install tzlocal

utc_dt = datetime(2020, 7, 10, 18, 44, 59, 193982, tzinfo=pytz.utc)
print(utc_dt.astimezone(get_localzone())) # print local time

 

Results

2020-07-11 03:44:59.193982+09:00

 

 

 

---

3. R Programming (R Package)

 

as.POSIXct() 함수

 

R Programming

%%R
as.POSIXct("2020-11-10 01:23:45", tz="America/Halifax") 

 

Results

[1] "2020-11-10 01:23:45 AST"

 

---

lubridate::with_tz() 함수

 

R Programming

%%R

base_datetime <- ymd_hms("2020-11-10 01:23:45", tz = "America/Halifax")
lubridate::with_tz(base_datetime, "US/Pacific")

 

Results

[1] "2020-11-09 21:23:45 PST"

 

---

as.POSIXct() 함수

 

R Programming

%%R

as.POSIXct(1499773898,tz="Asia/Kolkata",origin="1970-01-01")

 

Results

[1] "2017-07-11 17:21:38 IST"

 

---

lubridate::ymd_hms() 함수

 

R Programming

%%R

format(lubridate::ymd_hms("2020-08-03 12:01:59.23") , "%Y-%m-%dT%H:%M:%S%z")

 

Results

[1] "2020-08-03T12:01:59+0000"

 

---

as.POSIXct() 함수

 

R Programming

%%R

format(as.POSIXct(1499773898,tz="Asia/Kolkata",origin="1970-01-01") , "%Y-%m-%dT%H:%M:%S%z")

 

Results

[1] "2017-07-11T17:21:38+0530"

 

 

 

---

4. R Dplyr Package

 

---

5. R sqldf Package

 

strftime() 함수

 

R Programming

%%R

sqldf(" select strftime('%Y-%m-%d %H:%M:%f +08:00','2020-11-10 01:23:45 +04:00') time_zone ")

 

Results

                       time_zone
1 2020-11-09 21:23:45.000 +08:00

 

---

TIME() 함수

 

R Programming

%%R

sqldf(" SELECT time(time(), 'localtime') local_time ")

 

Results

  local_time
1   00:59:57

 

 

 

---

6. Python pandasql Package

 

strftime() 함수

 

Python Programming

ps.sqldf(" select strftime('%Y-%m-%d %H:%M:%f +08:00','2020-11-10 01:23:45 +04:00') time_zone ")

 

Results

	time_zone
0	2020-11-09 21:23:45.000 +08:00

 

---

7. R data.table Package

 

---

8. Python Duckdb의 SQL

 

Python Programming

%%sql
  select TIMESTAMPTZ '1998/09/27 22:05:21.089'                       AS TIMESTAMPTZ_1,
         CAST('1998/09/27 22:05:21.089' AS TIMESTAMP WITH TIME ZONE) AS TIMESTAMPTZ_2

 

Python Programming

duckdb.sql(" select TIMESTAMPTZ '1998/09/27 22:05:21.089'                       AS TIMESTAMPTZ_1,        \
                    CAST('1998/09/27 22:05:21.089' AS TIMESTAMP WITH TIME ZONE) AS TIMESTAMPTZ_2 ").df()

 

Results

                     TIMESTAMPTZ_1                    TIMESTAMPTZ_2
0 1998-09-27 22:05:21.089000+00:00 1998-09-27 22:05:21.089000+00:00

 

---

 

Python Programming

%%sql
  select timestamp with time zone  '2020-11-10 01:23:45+04:00' as TIMESTAMPTZ_1,
         strftime(timestamp with time zone  '2020-11-10 01:23:45+04:00' , '%Y-%m-%d %H:%M:%S +08:00' ) as TIMESTAMPTZ_2

 

Python Programming

duckdb.sql(" select timestamp with time zone  '2020-11-10 01:23:45+04:00' as TIMESTAMPTZ_1,                    \
                    strftime(timestamp with time zone  '2020-11-10 01:23:45+04:00' , '%Y-%m-%d %H:%M:%S +08:00' ) as TIMESTAMPTZ_2 ").df()

 

Results

              TIMESTAMPTZ_1               TIMESTAMPTZ_2
0 2020-11-09 21:23:45+00:00  2020-11-09 21:23:45 +08:00

 

---

Sand in an hourglass ( https://unsplash.com/photos/KYxXMTpTzek )

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

 

 

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