서버의 unixtime을 엑셀 날짜 및 시간으로 변환하기

정보보안 관련 업무를 하다 보면 가끔 unixtime을 년월일시분초로 변환할 일이 생긴다. 이는 대부분 보안 관련 로그에 로그가 발생할 당시의 시간을 unixtime, 즉 초단위로 기록되는 시간으로 기록하기 때문이다.

보안 제품의 관리자 프로그램 또는 웹사이트에서는 대부분 로그를 조회하면 유닉스타임을 자동으로 날짜 및 시분초로 변환해 보여주지만 원격에서 접속이 안될 땐 로그파일을 받아보곤 하는데 그 로그파일엔 날짜가 유닉스 타임으로 기록되어 있는 경우가 많다.

일단 로그를 받아 엑셀에서 열면 다음처럼 보인다.

적색 박스에 표시된 항목이 유닉스타임으로 기록된 날짜 및 시분초이다. 내가 오랫동안 주요 솔루션으로 기술지원을 담당했던 RedCastle SecureOS  뿐만 아니라 외산 및 국산의 여러 소프트웨어들은 타임스탬프를 유닉스 타임으로 기록하는 경우가 종종 있다. 하지만 이렇게 기록되어 있으면 로그파일을 직접 vi나 메모장 등으로 열었을 때 이 시간만 보고는 도대체 언제 발생된 로그인지 이해할 수 없다.

엑셀에서 이 유닉스타임 스탬프를 “년-월-일 시:분:초” 형태로 변환하기 위해선 다음과 같이 수식을 사용해야 한다.

위의 이미지의 상단 적색 박스에 있는 수식이 바로 unixtime의 타임스탬프를 Excel의 타임스탬프로 변환하는 식이다.

위의 식에서 A1은 D1 으로 바뀌어야 함(수식 붙여넣기하여 A1으로 표기되었음. 오류)

이 수식이 무슨 뜻인고 하니…

unixtime은 1970년 1월1일 0시를 기준으로 로그를 기록할 때까지의 흘러간 초다.

그리고 Excel의 timestamp는 1900년 1월 1일 0시를 기준으로 흘러간 날짜의 수다.

때문에 초가 기준인 unixtime을 86400초(24시간)로 나누면 일로 변환되고 엑셀 timestamp의 기준인 1900년부터 70년의 일을 더해주는데 그 70년동안 윤년이 17번 있었기 때문에 17을 더 더해준다.

마지막의 9/24를 더해주는 이유는 대한민국의 시간과 맞추기 위해 9시간을 더해주는 것이다. unixtime은 GMT 시간이다. 즉 영국 그리니치 천문대 기준의 시간이다. 때문에 9시간을 더해줘야 대한민국의 시간이 나온다.

앞 화면에서 두개의 적색 상자 중 아래가 엑셀 타임스탬프로 변환된 날짜다.

이제 이 날짜를 아래 화면처럼 셀 속성에 들어가 사용자 포맷을 선택하여 변경한다.

포맷은 무척 단순하다. 따로 설명이 없어도 이해할 듯…

위의 셀 서식을 적용하면 아래 이미지처럼 엑셀 타임스탬프가 날짜-시간으로 변환되어 보인다.

셀의 모서리를 클릭하고 아래로 드래그하면 아래의 셀들도 모두 변환된다.

끝…!!