Select records by time in interval between 12:00:00 and 18:00:00 on every day

Question

I've tried to select all records in a table with the timestamp in the dateformat 2011-08-01-

12:00:00

Using the following code:

SELECT f.`fly_reg`, RIGHT(f.`start_tid`,8) AS st, f.`start_hight`
FROM vbsk_dk_02.fab_master_flyvedata f 
Where st between 12:00:00 AND 18:00:00

But can't get it to work

Answer 1

You've got two issues here:

1. You can't refer to column aliases in the where clause. Instead, you have to repeat your calculation in the where clause
2. Use the TIME() function to extract the time part of the datatime

With those two issues addressed, you get:

select
    f.fly_reg,
    TIME(f.start_tid) AS st,
    f.start_hight 
    FROM vbsk_dk_02.fab_master_flyvedata f 
where TIME(f.start_tid) between '12:00:00' AND '18:00:00'

As an option, if you don't actually need the time value in the select, you can remove it and just have it in the where clause. Also, you can use the HOUR() function if that suits better. With those two changes in, your query would simplify to:

select *
FROM vbsk_dk_02.fab_master_flyvedata
where HOUR(f.start_tid) between 12 and 18

which is a lot neater :)