Epoch time is both a blessing and a curse. It is super-convenient for counting seconds (and doing calculations based on them) but can also be a pain to try to get into something readable as, or comparable to, a recognizable date. So today we’ll get into and out of epoch to show its flexibility without our brains having to be contortionists too.
Epoch time is a construct from UNIX that uses a baseline of midnight on January 1, 1970 UTC for “time zero.” The number associated with epoch is the number of seconds since then. As an example, the current epoch timestamp as I am writing this is 1438356946, corresponding to 1,438,356,946 seconds since 1970-01-01 00:00:00 – which translates to 2015-07-31 15:35:46 UTC. This means that epoch time can actually be negative if you’re referring to a date prior to 1970.
Converting to and from Epoch Time
Converting from epoch time to a timestamp isn’t too terrible. Basically, you just add the number of seconds to the starting date.
select '1970-01-01'::date + 1438356946 * interval '1 second';
Converting to epoch time is really super-simple. There’s a function that conveniently does this for you.
select extract(epoch from age(now(),'1970-01-01 00:00:00'));
- The conversion process uses intervals heavily. This is crucial because trying to add integers will not work in the same manner. Converting to an interval to express a difference in timestamps is easy using the AGE function. If you run into trouble, ensure that you are working with intervals.
- Some epoch time stamps may be in milliseconds. It’s important to understand whether you are working with seconds or milliseconds because anything you do with epoch is likely to be a calculation and you don’t want to be off by a factor of 1000!
- Due to the limitations of BIGINT (as a 32-bit signed integer), epoch cannot appropriately capture dates beyond January 19, 2038. It also cannot express dates prior to December 13, 1901. If you need to deal with dates outside of this range, use date or timestamp data types.