Question

Return a list of the start and end time of the last 10 bookings (ordered by the time at which they end, followed by the time at which they start) in the system.
Schema reminder
DB schema

Expected Results

starttime endtime
2013-01-01 15:30:00 2013-01-01 16:00:00
2012-09-30 19:30:00 2012-09-30 20:30:00
2012-09-30 19:00:00 2012-09-30 20:30:00
2012-09-30 19:30:00 2012-09-30 20:00:00
2012-09-30 19:00:00 2012-09-30 20:00:00
2012-09-30 19:00:00 2012-09-30 20:00:00
2012-09-30 18:30:00 2012-09-30 20:00:00
2012-09-30 18:30:00 2012-09-30 20:00:00
2012-09-30 19:00:00 2012-09-30 19:30:00
2012-09-30 18:30:00 2012-09-30 19:30:00

Your Answer Hint Help Save Run Query

 

Answers and Discussion Show

select starttime, starttime + slots*(interval '30 minutes') endtime
	from cd.bookings
	order by endtime desc, starttime desc
	limit 10          

This question simply returns the start time for a booking, and a calculated end time which is equal to start time + (30 minutes * slots). Note that it's perfectly okay to multiply intervals.

The other thing you'll notice is the use of order by and limit to get the last ten bookings. All this does is order the bookings by the (descending) time at which they end, and pick off the top ten.

You can multiply an interval by the number of slots in a booking.

Keyboard shortcuts:


Other hints: