[데이터 전처리4] 날짜 다루기1 : MySQL, Postgre

[데이터 전처리4] 날짜 다루기

---

 

• 오늘날짜 추출하기
• 지정 날짜/시간 추출하기
• 날짜/ 시각에서 연, 월, 일 추출하기
• 문자형'으로 되어 있는 날짜/ 시각에서 연, 월, 일 추출하기

---

MySQL

· 오늘날짜 추출하기

- Postgre와 동일

 

---

 

· 지정 날짜/시간 추출하기

(방법1)

SQL표준

 CAST('value' AS 'type')

SELECT CAST("2022-07-31" AS DATE) AS dt
	  ,CAST("14:22:00" AS TIME) AS time;

 

※ MySQL에서 CAST('value' AS TYPE) 에 가능한 타입은 아래와 같다.

BINARY[(N)] CHAR[(N)] [charset_info] DATE DATETIME DECIMAL[(M[,D])] JSON NCHAR[(N)] SIGNED [INTEGER] TIME UNSIGNED [INTEGER]

 

 

(방법2) 

타입 선언해주기

type 'value'

SELECT
date '2022-07-31' AS dt,
timestamp '2022-07-31 15:22:00' AS stamp
;

---

 

· 날짜/ 시각에서 연, 월, 일 추출하기

EXTRACT( TYPE  FROM  stamp )

* stamp(추출해올 기본 날짜/ 시각)

SELECT
stamp,
EXTRACT(YEAR FROM stamp) AS year,
EXTRACT(MONTH FROM stamp) AS month,
EXTRACT(DAY FROM stamp) AS day,
EXTRACT(HOUR FROM stamp) AS hour
FROM
(SELECT timestamp '2022-07-31 15:22:00' AS stamp) AS t
;

* 주의할 점 : MySQL 인라인 뷰에서 CAST('value' AS timestamp) 함수가 되지 않음. timestamp 'value' 사용

 

---

 

· '문자형'으로 되어 있는 날짜/ 시각에서 연, 월, 일 추출하기

SUBSTRING(stamp, 시작위치, 끝위치)

SELECT
stamp,
substring(stamp, 1, 4) AS year,
substring(stamp, 6, 2) AS month,
substring(stamp, 9, 2) AS day,
substring(stamp, 12, 2) AS hour,
substring(stamp, 1, 7) AS 'year_month'
FROM
(SELECT '2022-07-31 15:22:00' AS stamp) AS t
;

 

SUBSTR(stamp, 시작위치, 끝위치)

SELECT
stamp,
substr(stamp, 1, 4) AS year,
substr(stamp, 6, 2) AS month,
substr(stamp, 9, 2) AS day,
substr(stamp, 12, 2) AS hour,
substr(stamp, 1, 7) AS 'year_month'
FROM
(SELECT '2022-07-31 15:22:00' AS stamp) AS t
;

 

---

 

Postgre

· 오늘날짜 추출하기

CURRENT_DATE

CURRENT_TIMESTAMP

SELECT CURRENT_DATE AS dt,
       CURRENT_TIMESTAMP AS stamp;

 

 

· 지정 날짜/시간 추출하기

 

(방법1)

SQL표준

 CAST('value' AS 'type')

SELECT
CAST('2022-07-31' AS date) AS dt,
CAST('2022-07-31 15:22:00' AS timestamp) AS stamp
;

 

 

(방법2) 

타입 선언해주기

type 'value'

SELECT
date '2022-07-31' AS dt,
timestamp '2022-07-31 15:22:00' AS stamp
;

 

 

(방법3)

PostgreSQL에서 지원하는 ::

'value' ::type

SELECT
'2022-07-31'::date AS dt,
'2022-07-31 15:22:'::timestamp AS stamp
;

 

모든 방법에 대한 결과는 동일하다

---

 

· 날짜/ 시각에서 연, 월, 일 추출하기

EXTRACT( TYPE  FROM  stamp )

* stamp(추출해올 기본 날짜/ 시각)

SELECT
stamp,
EXTRACT(YEAR FROM stamp) AS year,
EXTRACT(MONTH FROM stamp) AS month,
EXTRACT(DAY FROM stamp) AS day,
EXTRACT(HOUR FROM stamp) AS hour
FROM
(SELECT CAST('2022-07-31 15:22:00' AS timestamp) AS stamp) AS t
;

---

 

· '문자형'으로 되어 있는 날짜/ 시각에서 연, 월, 일 추출하기

SUBSTRING(stamp, 시작위치, 끝위치)

SELECT
stamp,
substring(stamp, 1, 4) AS year,
substring(stamp, 6, 2) AS month,
substring(stamp, 9, 2) AS day,
substring(stamp, 12, 2) AS hour,
substring(stamp, 1, 7) AS year_month
FROM
(SELECT CAST('2022-07-31 15:22:00' AS text) AS stamp) AS t
;

 

SUBSTR(stamp, 시작위치, 끝위치)

SELECT
stamp,
substr(stamp, 1, 4) AS year,
substr(stamp, 6, 2) AS month,
substr(stamp, 9, 2) AS day,
substr(stamp, 12, 2) AS hour,
substr(stamp, 1, 7) AS year_month
FROM
(SELECT CAST('2022-07-31 15:22:00' AS text) AS stamp) AS t
;