Unix Epoch Times

A particular period of history, especially one considered remarkable or noteworthy…

The beginning of a new and important period in the history of anything… A milestone.

These are a few of the definitions for the fancy word ‘epoch’. For astronomers, it is the time at which observations are made, as of the positions of planets or stars. In computer applications, epochs are used to maintain a time reference as a single number for ease of computation.

Unix Epoch Time

The unix time stamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970 at UTC, and at this very moment, the Unix epoch time is 1645179588.

Since computer applications make extensive use of this feature, it is quite normal to get these kind of time stamps. It is usually the case that we want to convert these timestamps to a human readable format, so let us examine a few examples to convert Unix time in Tableau.

As mentioned, a 10-digit number represent the Unix epoch time in seconds. To convert this to UTC time in Tableau, we can use the following calculation:

DATEADD('second', INT([Unix time field]), #1970-01-01#)

Note that the calculation assumes that the Unix time is in seconds. It may well be that we get a 13-digit Unix time, and in this case we will divide it by 1000 first as in:

DATEADD('second', INT([Unix time field]/1000), #1970-01-01#)

Also note that the DATEADD() function requires the second argument to be a number and since in most times we get it in a string format we will have to convert the [Unix time field] to an integer.

Different Epochs

Sometimes though, we get very weird Unix timestamps that doesn’t make sense. In a recent project I was exploring LDAP/Windows Active Directory time stamps and stumbled upon an 18-digit timestamp such as this: 132802481539115672.

What??!!

Well, it turns out that there are quite a few timestamps to choose from. To name a few, apple macOS considers its Epoch Time as starting from January 1, 1904. Microsoft Windows considers its Epoch Time as starting from January 1, 1601, while Unix and Linux Systems consider their Epoch Time as starting from January 1, 1970.

If you find yourself intrigued by the subject, you can check out more Notable epoch dates in computing.

Back to our ugly looking timestamp…

The 18-digit Active Directory timestamps, are also named as ‘Windows NT time format’, ‘Win32 FILETIME’ or ‘Win32 SYSTEMTIME’ or ‘NTFS file time’. Since they are widely used we can find them in various Active Directory attributes such as ‘LastLogonTimestamp’, ‘LastPwdSet’ etc.

The timestamp is the count of 100-nanosecond intervals since Jan 1, 1601 UTC. There are several ways to go around this in order to get a human readable timestamp in Tableau.

1. A bit of Unix Arithmetics: We will revert to seconds and discard the last 7 digits of the LDAP timestamp, thus dividing our Windows timestamp by 10000000. We can then convert it to Unix epoch time by subtracting 11644473600 (number of seconds between January 1, 1601 and January 1, 1970).
   
   • Conversion to Unix Epoch Time:
     INT([Windows time field]/10000000) - 11644473600
     and then
   • DATEADD('second', [Unix Epoch Time], #1970-01-01#).
     
2. Conversion to seconds and then smoothly get the desired timestamp:
   DATEADD('second', INT([Windows time field]/10000000), #1601-01-01#).

Unix Epoch Time Fun Fact

If you recall, the year 2000 signified the famous Y2K Bug. The bug was expected to bring down world wide computer systems and created quite some panic at the time. The bug itself was the inability of computer systems to distinguish dates correctly. Instead of allowing four digits for the year, many computer programs only allowed two digits (e.g., 90 instead of 1990).

Well… eventually nothing much happened, A storm in a cup of tea…

As we now know, Unix time represents the seconds passed since January 1, 1970. Historically, Unix time has been encoded as a signed 32-bit integer, with values up until 2³¹ (<2,147,483,648). Once these amount of seconds will pass (apparently on 03:14:07, Tuesday, 19 January 2038), Values will change sign and indicate a negative number, leading to a false date. Any system using data structures with 32-bit time representations will be at risk to fail.

Summary

In this blog we covered Unix Epoch Time and discussed the way we can manipulate it to show a human readable form. I hope you have a clearer view of Unix Time and the different Epochs it represents.