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
DB schema

Expected Results

date_part
169200

Your Answer Hint Help Save Run Query


              

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.

You can do this by extracting the epoch from the interval between two timestamps.

Keyboard shortcuts:


Other hints: