PostgreSQL intervals are smarter than just number of seconds between two datetimes. They can represent "1 month" or "1 year" which can't be represented as a number of seconds. Also, intervals can be used between dates or times, not just datetimes.
Exactly right; date arithmetic is horrible if you only have seconds to work with.
Even just saying "24 hours from now" -- as your user would see it -- you'll sometimes get the wrong answer if you just add on 24 hours worth of seconds... suppose they have a DST shift tonight?
What if you need to know how many days remain from today to March 1st of this year? ...well, are we in a leap year?
Postgres datetime doesn't solve all of the problems that crop up, but it's certainly better than using unix time.
24 hours from now is no problem if you're using unix time. Just add 24 hours worth of seconds -- when you convert back to the user's time, your conversion logic will take care of timezones. This is also incredibly important if serving an app over the internet as your user may have just flown from USA to Japan. Storing unix time solves this in the easiest way.
Same for working out how many days remain - you convert March 1st to a unix date, get an interval in seconds, and then convert that into a human readable format on display.
This isn't some revelation - unix time has been used successfully for decades now.
EDIT: Also, Postgresql uses a number very similar to unix time in the actual storage of the date - it just handles the pretty display for you. So you're arguing for using the same thing whether you say use a postgre datetime or a unix date number.
> [Postgres] just handles the pretty display for you
It's rather more than that, though I don't have the time to kill digging into it now.
And personally I mostly seem to end up doing more complicated date processing in code, not queries; but there everything needs to be a date immediately (not unix time or similar) for most purposes -- then I can use complicated libraries written by others to let me do "simple" things with dates, like rolling months.
Ah, also:
> you convert March 1st to a unix date
Then that's where the complicated logic goes. Basically, you need that somewhere, and it's non-trivial (leap year calc is the least of it).
My point isn't that unix dates aren't useful for storage, but that they aren't useful by themselves for calculation.
The only true problem with storing unix time is leap seconds and relativistic effects, which can both be safely ignored for most time keeping usages.