How to convert a Unix timestamp to a datetime

Unix epoch timestamps are an integer value which represents the number of seconds since midnight UTC 01-Jan-1970. They are often used in log files. They are compact and it is easy to calculate the time difference between 2 timestamps. But they aren’t very human-friendly and it is often desirable to convert them to a less opaque format, such as a ISO datetime. You can do that easily in Easy Data Transform using the MSecsToDateTime option in the Calculate transform.

First you need to mutliply the Unix timestamp column by 1000, to convert it from seconds to milliseconds using the Calculate transform:

Then you can convert this new column to an ISO datetime, again using Calculate:

Once you have an ISO datetime, you can use the Date Format transform to change it into other date formats, for example a US-style month/day/year date.

Note that case is important in the Format from and Format to fields.

Finally you can use the Rename Cols and Remove Cols transforms to tidy things up by renaming and removing columns.

You can also use the DateTimeToMSecs option in the Calculate transform to perform the reverse conversion from an ISO datetime to milliseconds since 01-Jan-1970. You would then have to use the Calculate transform to divide by a 1000 to convert to a Unix timestamp.

See the video above for more details.

Easy Data Transform can also help with merging, cleaning, filtering, enriching and reshaping your data. All with a few clicks, no coding required.