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
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.
select * from some_table where created_at >= timestamp '2019-10-02 14:37:12';
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 = -47606400;
Would you know that the above is the release date for Pink Floyd’s “A Saucerful of secrets”: 1967-06-29?
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.
date values also have the advantage of allowing “date arithmetics” using “context aware” intervals.