You can get what you are looking for without CASE at all.
One thing to realize is when working with dates/timestamps converting to text is virtually always unnecessary and incorrect (as is the case here) basically just do not do it. Use the Postgres date functions.
The following gives what you are wanting. It begins creating a CTE to define time periods and a descriptive name for each period. It then JOINS that CTE with the flight table. (See demo here)
with departures( stime, etime, description) as
(values ('00:00:00'::time, '02:00:00'::time, 'Night flight')
, ('02:00:00'::time, '06:00:00'::time, 'Early morning flight')
, ('06:00:00'::time, '11:00:00'::time, 'Morning flight')
, ('11:00:00'::time, '16:00:00'::time, 'Noon flight')
, ('16:00:00'::time, '19:00:00'::time, 'Evening flight')
, ('19:00:00'::time, '24:00:00'::time, 'Night flight')
)
select f.flight_id "Flight Id"
, (f.departure at time zone 'Asia/Kolkata')::time(0) "Schedule Departure Time"
, d.description "Description"
from departures d
join flights f
on ( (f.departure at time zone 'Asia/Kolkata')::time(0) >= d.stime
and (f.departure at time zone 'Asia/Kolkata')::time(0) < d.etime
)
order by "Schedule Departure Time";
Notes:
- Timestamps always include a date and fractional seconds. Since you
want just time comparisons both need to be discarded. This is
accomplished with
::time(0). It does, however, round to the nearest
second.
- I changed your definition for 'Night Flight'. In a timestamp, after
discarding the date the resulting time,
23:00:00 will always be
greater than 02:00:00. The change corrects for this.
- I used at time zone 'Asia/Kolkata' to get your +3.5 hour
offset from UTC (am at UTC-5). Depending on your server time zone
setting you may not require this.
I am a newbie so bear with me if the format is not structured.
so I got the expected output after writing this code...
'''
with flights ( stime, etime, description) as
(values ('00:00:00'::time, '02:00:00'::time, 'Night flight')
, ('02:00:00'::time, '06:00:00'::time, 'Early morning flight')
, ('06:00:00'::time, '11:00:00'::time, 'Morning flight')
, ('11:00:00'::time, '16:00:00'::time, 'Noon flight')
, ('16:00:00'::time, '19:00:00'::time, 'Evening flight')
, ('19:00:00'::time, '24:00:00'::time, 'Night flight')
)
select flight_id "Filght ID"
, flight_no "Flight No"
, scheduled_departure "scheduled_departure"
, scheduled_arrival "scheduled_arrival"
, flights "Timings"
from flights
join bookings.flights f
on ( (scheduled_departure at time zone 'Asia/Kolkata')::time(0) >= stime
and (scheduled_arrival at time zone 'Asia/Kolkata')::time(0) < time
)
'''
but in the modified column I just want the Text such as 'Night Flight' instead of the entire time which is '00:00:00'::time, '02:00:00'::time, 'Night flight'
how can I get just the text output?
2nd I tried to filter the output using having'or where statement
using this code
with flights ( stime, etime, description) as
(values ('00:00:00'::time, '02:00:00'::time, 'Night flight')
, ('02:00:00'::time, '06:00:00'::time, 'Early morning flight')
, ('06:00:00'::time, '11:00:00'::time, 'Morning flight')
, ('11:00:00'::time, '16:00:00'::time, 'Noon flight')
, ('16:00:00'::time, '19:00:00'::time, 'Evening flight')
, ('19:00:00'::time, '24:00:00'::time, 'Night flight')
)
select flight_id "Filght ID"
, flight_no "Flight No"
, scheduled_departure "scheduled_departure"
, scheduled_arrival "scheduled_arrival"
, flights "Timings"
from flights
join bookings.flights f
on ( (scheduled_departure at time zone 'Asia/Kolkata')::time(0) >= stime
and (scheduled_arrival at time zone 'Asia/Kolkata')::time(0) < etime
)
group by
flight_id,
flights
having flights = 'Morning flight'
it is giving me an error saying this
ERROR: input of anonymous composite types is not implemented
LINE 22: having flights = 'Morning flight'
(Pasting text because image is allowed in my account)
@belayer
Response:
- First Issue, unwanted columns: You only want want the Text such as 'Night Flight' instead of the entire time. Well each column is
individually SELECTed in the query. If there are some you do not want then just remove them from the select list.
- Second issue:
flights = 'Morning flight, flights refers to the
entire set on rows within the CTE. The usage would be the same as if there were a table flights, you would not say table_name = string. In this case you are looking for flights.description = 'Morning flight. You will get a similar error on the line aliased as "Timings". You will need to refer to the individual columns.
- Third Issue: Filtering. The target column of HAVING is typically the result of an aggregate column. However, your query has no
aggregate function, so WHERE is appropriate. (Note the structure change. You can google SQL Order of Operations. So:
with flights ( stime, etime, description) as
(values ('00:00:00'::time, '02:00:00'::time, 'Night flight')
, ('02:00:00'::time, '06:00:00'::time, 'Early morning flight')
, ('06:00:00'::time, '11:00:00'::time, 'Morning flight')
, ('11:00:00'::time, '16:00:00'::time, 'Noon flight')
, ('16:00:00'::time, '19:00:00'::time, 'Evening flight')
, ('19:00:00'::time, '24:00:00'::time, 'Night flight')
)
select flight_id "Filght ID"
, flight_no "Flight No"
, scheduled_departure "scheduled_departure"
, scheduled_arrival "scheduled_arrival"
, flights.description "Timings" -- ?? Guessing
from flights
join bookings.flights f
on ( (scheduled_departure at time zone 'Asia/Kolkata')::time(0) >= stime
and (scheduled_arrival at time zone 'Asia/Kolkata')::time(0) < etime
)
Where flights.description = 'Morning flight';
