1

This is my Schema down below enter image description here

And i have to solve this question "How can you produce a list of the start times for bookings for tennis courts, for the date '2012-09-21'? Return a list of start time and facility name pairings, ordered by the time."

The query down below works fine

select bks.starttime as start, facs.name as name
from 
    cd.facilities facs
    inner join cd.bookings bks
        on facs.facid = bks.facid
where 
    facs.name in ('Tennis Court 2','Tennis Court 1') and
    bks.starttime >= '2012-09-21' and
    bks.starttime < '2012-09-22'
order by bks.starttime

But this query down below with to_char() doesn't work. What is the issue here?

 select starttime,name 
 from cd.bookings, cd.facilities 
     where cd.bookings.facid = cd.facilities.facid 
     and cd.facilities.name like 'Tennis Court%' 
     and to_char(cd.bookings.starttime,'YYYY-MM-DD') = '2012-09-21'
 order by cd.bookings.starttime

I am practicing this exercise on this url: https://pgexercises.com/questions/joins/simplejoin2.html

sakib 1212
  • 60
  • 6
  • Please define "doesn't work". What exactly happens when you use the second query. But the first query is the best way to do this. Alternatively maybe `starttime::date = date '2012-09-21'` (but that won't use an index on `starttime`) –  Jul 09 '21 at 06:14
  • One obvious difference is that the second query will return **all** "tennis courts", while the first one will only return courts 1 and 2 –  Jul 09 '21 at 06:16
  • @a_horse_with_no_name Thank you. It is working now after reloading the browser tab. The query was correct. But i want to know why is the first query best way to do this instead of the second query?. Can you please give me a hint or explain this. – sakib 1212 Jul 09 '21 at 06:22
  • 1
    This is a bit subjective, but: for one explicit `JOIN` should be preferred over the outdated implicit join conditions in the WHERE clause. The first query is also better because it can make use of an index on `starttime` to speed up data retrieval, while the second one (or using `starttime::date = ...`) will require an additional special index that is probably only useful for this query and nothing else –  Jul 09 '21 at 06:37

0 Answers0