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

Posted by taeho Tae-Ho
2015.12.03 15:47 운영체제

서버보안 일을 하다 보면 가끔 Unixtime을 년월일시분초로 변환할 일이 생긴다. 서버보안 도구의 매니저에서 로그를 조회하면 유닉스타임을 자동으로 날짜로 변환해 보여주지만 원격에서 접속이 안될 땐 로그파일을 받아보곤 하는데 그 로그파일엔 날짜가 유닉스 타임으로 표시되어 있다.


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



적색 박스에 표시된 항목이 유닉스타임으로 기록된 날짜 및 시간이다. RedCastle  뿐만 아니라 외산 및 국산의 여러 소프트웨어들은 타임스탬프를 유닉스 타임으로 기록하는 경우가 종종 있다. 하지만 이렇게 기록되어 있으면 이 시간만 보고는 도대체 언제 발생된 로그인지 이해할 수 없다.


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



위의 이미지의 상단 적색 박스에 있는 수식이 바로 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시간을 더해줘야 대한민국의 시간이 나온다.


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


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



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

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



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


끝...!!


신고
이 댓글을 비밀 댓글로