Epoch Mania

Recently I see an increase in questions in various forums (including Stackoverflow) where people are using (big) integer values instead of proper timestamp values to represent timestamps values (less so for DATE values though).

All modern databases systems provide highly efficient data types to store real timestamp values, but I often get questions asking what the actual downside of using a “UNIX epoch” instead of a proper timestamp is.

Pitfalls

What people easily overlook is that dealing with timestamps seems easy at the beginning but quickly shows that it’s much more complicated. Things like different length of months, leap years, leap seconds, daylight savings time and time zones can not be properly handled with a “epoch” value.

Resolution and accuracy

The “UNIX epoch” is a point in time stored as seconds. So it’s impossible to store and deal with fractional seconds (milliseconds, microseconds) and so on. Most database products offer a precision of up to microseconds with their timestamp types.

Maintainability and Flexibility

The SQL standard defines some very useful (albeit sometimes complicated looking) ways to deal with intervals together with (real) timestamp values.

current_timestamp + interval '1-10' year to month

Coming up with the correct number of seconds for “one year and ten months” is nearly impossible because one needs to take different month lengths, leap years and DST into account to calculate this correctly.

The above expression is standard ANSI SQL (and will work like that with e.g. PostgreSQL and Oracle) but all DBMS products provide ways to express this correctly. In PostgreSQL this can be written in a much more understandable way:

current_timestamp + interval '1 year 10 month'

And obviously, queries are much harder to read when using illegible numbers instead of proper timestamp literals.

Compare this:

select *
from some_table
where created_at >= timestamp '2019-10-02 14:37:12';

with

select *
from some_table
where created_at >= 1570027032;

It gets even worse if you have to deal with timestamps before 1970-01-01

select *
from albums
where release_date = -76118400;

Would you know that the above is the release date for Pink Floyd’s “A Saucerful of secrets”: 1976-08-04?

Conclusion

Use the right data type for the job. Use numeric types for numbers, use DATE for date values and please use TIMESTAMP (or the equivalent in the concrete DBMS product) for timestamp values.