Work out the number of seconds between timestamps
Question
Work out the number of seconds between the timestamps '2012-08-31 01:00:00' and '2012-09-02 00:00:00'Schema reminder ▼
Your Answer
Answers and Discussion Show
select extract(epoch from (timestamp '2012-09-02 00:00:00' - '2012-08-31 01:00:00'));
The above answer is a Postgres-specific trick. Extracting the epoch converts an interval or timestamp into a number of seconds, or the number of seconds since epoch (January 1st, 1970) respectively. If you want the number of minutes, hours, etc you can just divide the number of seconds appropriately.
If you want to write more portable code, you will unfortunately find that you cannot use extract epoch. Instead you will need to use something like:
select extract(day from ts.int)*60*60*24 + extract(hour from ts.int)*60*60 + extract(minute from ts.int)*60 + extract(second from ts.int) from (select timestamp '2012-09-02 00:00:00' - '2012-08-31 01:00:00' as int) ts
This is, as you can observe, rather awful. If you're planning to write cross platform SQL, I would consider having a library of common user defined functions for each DBMS, allowing you to normalise any common requirements like this. This keeps your main codebase a lot cleaner.