Question

Find the result of subtracting the timestamp '2012-07-30 01:00:00' from the timestamp '2012-08-31 01:00:00'
Schema reminder
DB schema

Expected Results

interval
32 days

Your Answer Hint Help Save Run Query


              

Answers and Discussion Show

select timestamp '2012-08-31 01:00:00' - timestamp '2012-07-30 01:00:00' as interval;          

Subtracting timestamps produces an INTERVAL data type. INTERVALs are a special data type for representing the difference between two TIMESTAMP types. When subtracting timestamps, Postgres will typically give an interval in terms of days, hours, minutes, seconds, without venturing into months. This generally makes life easier, since months are of variable lengths.

One of the useful things about intervals, though, is the fact that they can encode months. Let's imagine that I want to schedule something to occur in exactly one month's time, regardless of the length of my month. To do this, I could use [timestamp] + interval '1 month'.

Intervals stand in contrast to SQL's treatment of DATE types. Dates don't use intervals - instead, subtracting two dates will return an integer representing the number of days between the two dates. You can also add integer values to dates. This is sometimes more convenient, depending on how much intelligence you require in the handling of your dates!

You can use the '-' symbol on timestamps

Keyboard shortcuts:


Other hints: