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 :)

Answer 2

If you have stored the time in a column of type "Timestamp" or "Datetime", you can select a range of records between hours like this:

select * from testTable where hour(`timeStampCol`) >= 12 and hour(`timeStampCol`) <= 18

I tested this with this setp up:

CREATE TABLE `cm`.`testTable` (
  `timeStampCol` TIMESTAMP  NOT NULL,
  `dateTimeCol` DATETIME  NOT NULL
)
ENGINE = MyISAM
COMMENT = 'Delete this table';

insert into testTable values ('2010-01-01 14:52:00', '2010-01-01 14:52:00')
insert into testTable values ('2010-01-01 19:48:00', '2010-01-01 19:48:00')