Work out the end time of bookings
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.
Expected Results
| starttime | endtime |
|---|---|
| Tue Jan 01 2013 15:30:00 GMT+0000 (Coordinated Universal Time) | Tue Jan 01 2013 16:00:00 GMT+0000 (Coordinated Universal Time) |
| Sun Sep 30 2012 19:30:00 GMT+0000 (Coordinated Universal Time) | Sun Sep 30 2012 20:30:00 GMT+0000 (Coordinated Universal Time) |
| Sun Sep 30 2012 19:00:00 GMT+0000 (Coordinated Universal Time) | Sun Sep 30 2012 20:30:00 GMT+0000 (Coordinated Universal Time) |
| Sun Sep 30 2012 19:30:00 GMT+0000 (Coordinated Universal Time) | Sun Sep 30 2012 20:00:00 GMT+0000 (Coordinated Universal Time) |
| Sun Sep 30 2012 19:00:00 GMT+0000 (Coordinated Universal Time) | Sun Sep 30 2012 20:00:00 GMT+0000 (Coordinated Universal Time) |
| Sun Sep 30 2012 19:00:00 GMT+0000 (Coordinated Universal Time) | Sun Sep 30 2012 20:00:00 GMT+0000 (Coordinated Universal Time) |
| Sun Sep 30 2012 18:30:00 GMT+0000 (Coordinated Universal Time) | Sun Sep 30 2012 20:00:00 GMT+0000 (Coordinated Universal Time) |
| Sun Sep 30 2012 18:30:00 GMT+0000 (Coordinated Universal Time) | Sun Sep 30 2012 20:00:00 GMT+0000 (Coordinated Universal Time) |
| Sun Sep 30 2012 19:00:00 GMT+0000 (Coordinated Universal Time) | Sun Sep 30 2012 19:30:00 GMT+0000 (Coordinated Universal Time) |
| Sun Sep 30 2012 18:30:00 GMT+0000 (Coordinated Universal Time) | Sun Sep 30 2012 19:30:00 GMT+0000 (Coordinated Universal Time) |
Your Answer
Your Results
Loading database...
Answers and Discussion
select starttime, starttime + slots*(interval '30 minutes') endtime
from cd.bookings
order by endtime desc, starttime desc
limit 10This 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.