SQL 배우기

이것만 보면 날짜시간 형변환 완벽이해! hive sql

차근차근 디지털 2023. 2. 14. 13:34

데이터 분석을 하다보면 시계열로 볼 일도 많고 시간차이 계산이나 다양한 형태로 변환할 일도 많습니다!

저는 매번 구글링을 해서 활용하는데, 언젠가부터 이렇게 자주 쓸거면 확실하게 알고 가야겠다는 생각이 들더라구요!

그래서 "날짜시간 함수 완벽 정리" 시간을 가져보겠습니다.

 

단순하게 생각하면, 날짜시간 형태는 3가지 타입인 것 같습니다.

String , UnixTime, 찐 날짜시간타입(date, timestamp 이 있고 year, hour 등의 함수를 사용할 수 있음 )

Input Output Data Type
'20230214130405' '20230214130405' string
unix_timestamp('20230214130405', 'yyyyMMddHHmmss')

 1676379845 bigint
(유닉스타임)
unix_timestamp('2023/02/14 13:04:05', 'yyyy/MM/dd HH:mm:ss')
from_unixtime(unix_timestamp('20230214130405', 'yyyyMMddHHmmss'), 'yyyyMMddHHmmss') '20230214130405' string
from_unixtime(unix_timestamp('20230214130405', 'yyyyMMddHHmmss'), 'yyyy/MM/dd HH:mm:ss') '2023/02/14 13:04:05' string
cast(from_unixtime(unix_timestamp('20230214130405', 'yyyyMMddHHmmss')) as date) 2023/02/14 date
cast(from_unixtime(unix_timestamp('20230214130405', 'yyyyMMddHHmmss')) as timestamp) 2023/02/14 13:04:05.000000 timestamp
date_format(from_unixtime(unix_timestamp('20230214130405', 'yyyyMMddHHmmss'), 'yyyyMMddHHmmss')) '20230214130405' string
date_format(from_unixtime(unix_timestamp('20230214130405', 'yyyyMMddHHmmss'), 'yyyy/MM/dd HH:mm:ss')) '2023/02/14 13:04:05' string

unix_timestamp : 말 그대로 유닉스 타임으로 바꿔준다! 

   → 공식 : unix_timestamp( 문자열, 앞 문자열과 같은 날짜 형태) 

   → unix_timestamp('20230214', 'yyyyMMdd') : 1676332800

   → unix_timestamp('2023-02-14', 'yyyy-MM-dd') : 1676332800

 

from_unixtime : 말 그대로 유닉스타임을 어떤 형태로 보여줄까?라는 함수

   → 공식 : from_unixtime ( 유닉스타임, 보고 싶은 날짜시간 형태)  

   → from_unixtime (1676379845) : 2023-02-14 13:04:05  (기본은 yyyy-MM-dd HH:mm:ss 형태를 보여줌)

   → from_unixtime (1676379845, 'yyyy') : 2023

   → from_unixtime (1676379845, 'yyyy-MM-dd') : 2023-02-14

   → from_unixtime (1676379845, 'yyyy/MM/dd HH:mm:ss') : 2023/02/14 13:04:05

 

cast(    as format) : 찐 타임스탬프로 바꿔준다

   → 공식 : cast ( 문자 as 이걸로 바꿔줘)

   → cast('2023/02/14 13:04:05' as timestamp) 이제야 찐 timestamp 로 바껴서 hour(), minutes() 등등의 함수를 쓸 수 있다!

  → format 자리에 쓸 수 있는 것은 timestamp, date 등등이 있다

 

date_format

  → 공식 : date_format (찐 날짜시간 형태거나 문자형이면 from_unixtime이랑 친구해야함, 보고 싶은 날짜시간 형태)  

  → date_format ('2023/02/14 13:04:05', 'yyyy/MM/dd HH:mm:ss') : 찐 날짜시간 타입이 아닌 단수 문자형이면 나오지 않음 ㅠㅠ (해당 컬럼의 속성값을 잘 확인하기!)

  → date_format(from_unixtime(unix_timestamp('20230214130405', 'yyyyMMddHHmmss'), 'yyyy/MM/dd HH:mm:ss')) : '2023/02/14 13:04:05' string 형태의 원하는 형태가 나옴!  year, month, day, hour, minute, second 등의 함수를 사용할 수 있음!

  → date_format('2023/02/14 13:04:05', '★')  ★에 넣어서 추출할 수 있는 형태는 아주 많음 (아래는 대표적인 예)