Unix Timestamp 처리하는법

timestamp (datetime) 데이터를 다루다보면 여러 포맷의 timestamp가 등장한다.

그 중 Unix timestamp(epoch timestamp) 가 가끔 등장하게 되는데 10자리의 digit (세컨드 기준)으로 구성되어 있다

Unix time은 1970년 1월 1일 0시 이후부터 몇초가 흘렀는지 10자리로 알려주게되는데, (밀리세컨드까지 표기하면 13자리)

보통 timestamp가 Unix time으로 저장되어있는 DB에서 데이터를 읽으면 long 형식으로 읽게된다.

 

이러한 10자리 Unix Time을 <python, sql, pyspark> 내에서 어떻게 "yyyy-MM-dd HH:mm:ss" timestamp 데이터타입으로 변환할 수 있는지 알아보자.

 

파이썬 (Python pandas)

아래와 같은 레코드가 pandas df 에 저장되어 있다고 가정하자

1. ts는 현재 13 밀리세컨드 Unix timestamp (long 타입)인데 먼저 변환을 할 때 ms(밀리세컨드)로 저장할지 s(세컨드)로 저장할지 정해야 함. 좀 더 세분화하고 싶다면 ms로 저장하지만 크게 문제가 되지는 않음. to_datetime() 과 astype() 사용가능

df['ts_s'] = pd.to_datetime((df['ts']/1000).astype('int'), unit='s')
df['ts_s2'] = (df['ts']/1000).astype('int').astype('datetime64[s]')

df['ts_ms'] = pd.to_datetime(df['ts'], unit='ms')
df['ts_ms2'] = df['ts'].astype('datetime64[ms]')

2. .dt 함수를 이용하여, datetime64 데이터 타입에서 hour, day, week, month, year, weekday_name 추출 가능

df['hour'] = df['dts'].dt.hour
df['day'] = df['dts'].dt.day
df['week'] = df['dts'].dt.week
df['month'] = df['dts'].dt.month
df['year'] = df['dts'].dt.year
df['weekday_name'] = df['dts'].dt.weekday_name

postgreSQL

time 테이블의 schema가 아래와 같다고 가정하고

staging_event 테이블의 ts column 이 13자리 Unix timestamp 형식의 BIGINT 라고 가정했을때

staging_event 의 ts column을 이용하여 time 테이블의 각 column (start_time, hour, day, week, ...) 에 INSERT 하는 방법

(
start_time TIMESTAMP NOT NULL PRIMARY KEY sortkey,
hour       INTEGER,
day        INTEGER,
week       INTEGER,
month      INTEGER,
year       INTEGER,
weekday    INTEGER
);

INSERT INTO time
SELECT DISTINCT
    timestamp 'epoch' + ts/1000 * interval '1 second' AS start_time,
    EXTRACT(hour from start_time),
    EXTRACT(day from start_time),
    EXTRACT(week from start_time),
    EXTRACT(month from start_time),
    EXTRACT(year from start_time),
    EXTRACT(weekday from start_time)
FROM staging_event

• 핵심은  << timestamp 'epoch' + ts/1000 * interval '1 second' >>
  • ts를 1000으로 나눠서 세컨드(s) 로 바꾸고
  • interval '1 second' 를 곱해서 세컨드(s) 데이터 타입으로 바꾸고
  • timestamp 'epoch' 을 추가해주는 느낌...?
• 나머지 column 들은 timestamp 형식인 start_time 에서 EXTRACT 하면됨

파이스파크 (pySpark)

# 10자리 Unix timestamp 자료형은 to_timestamp() 함수를 통해 "yyyy-MM-dd HH:mm:ss" 형태로 가공가능
# 13자리 Unix timestamp 라면 "/1000"을 추가해서 가공가능
df = df.withColumn('ts_to_str', to_timestamp(df.ts / 1000, "yyyy-MM-dd HH:mm:ss"))

# 다양한 형태의 timestamp형 "yyyy-MM-ddTHH:mm:ss+0000" 를
# "yyyy-MM-dd HH:mm:ss" 형태로 가공 가능
df = df.withColumn("str_to_timestamp", to_timestamp(df.ts_to_str, "yyyy-MM-dd HH:mm:ss"))

# 결과적으로,
# input으로 주어진 date가 10자리 unix_timestamp로 주어지면 to_timestamp(ts) 쓰고
df = df.withColumn('Unix_to_timestamp', df.ts / 1000)

# 이상한 형태의 timestamp로 주어지면 to_timstamp(ts, "yyyy-MM-dd HH:mm:ss") 로 쓰임
df = df.withColumn("Timstamp_to_timestamp", to_timestamp(df.ts, "yyyy-MM-dd HH:mm:ss"))

# 추가적으로 시간끼리의 연산을 위해서는 Unix timestamp 형식을 이용하면 편하다
# Default timestamp + second 하기 위해서는
df = df.withColumn("added timestamp", to_timestamp(unix_timestamp(df.ts) + 1232))

• 판다스(Pandas) 와 조금 비슷하면서도 다르다
• to_timestamp()을 통해 10자리 Unix Timestamp와 다양한 형태의 Default Timestamp 형식을 "yyyy-MM-dd HH:mm:ss" 형태로 변환 가능
• 시간끼리의 연산이 필요할때는 Unix Timestamp로 변환하고 연산을 한 다음에 다시 Default Timestamp로 변환 하는 것이 편함