9

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

Bohemian
  • 412,405
  • 93
  • 575
  • 722
user857558
  • 167
  • 1
  • 2
  • 7

2 Answers2

15

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

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • From my understanding in MySQL if we do operations on datetime and find row that satisfies condition it will not use index (given it is set on start_tid) I have millions of rows and want to make my query efficient as possible, I could run query multiple times per each day but is there any other way? – haneulkim Sep 11 '20 at 05:18
  • 1
    @Ambleu there's no getting around having to hit every row with a function unless your db supports functional indexes, which mysql doesn't. – Bohemian Sep 11 '20 at 22:13
4

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')
TJDJD
  • 70
  • 5