Script below creates a simple database and table. In windows environment, you can use it with wamp server’s ( https://sourceforge.net/projects/wampserver/ ) phpmyadmin sql console:
delimiter $$
create database testtimes $$
use testtimes $$
create table mytime (
hour bigint not null,
min varchar(3) not null,
sec bigint not null,
unique key mytime_lst (hour,min,sec)
) $$
insert into mytime (hour,min,sec) values (0,"3",10) $$
insert into mytime (hour,min,sec) values (0,"3",11) $$
insert into mytime (hour,min,sec) values (0,"4",8) $$
insert into mytime (hour,min,sec) values (0,"5",3) $$
insert into mytime (hour,min,sec) values (1,"0",7) $$
insert into mytime (hour,min,sec) values (1,"3",10) $$
delimiter ;
In the simplified example, there are 3 columns and some rows. Columns are tied together as one logical unit, I created index on them. I want to pinpoint 1 logical unit in the table, and use the index to give me 2 result next to that point. I am not sure, do I have that support or not. If the problem is related to specific SQL server, please, give me hint about.
(To prevent unrelated questions: the column “min” is converted to varchar on purpose. The example above is a simplified one. In the real environment, I do not have the luxury to rely on bigints only.)
An example query:
select *
from mytime
where (hour >= 0 and min >= "4" and sec >= 7)
order by hour, min, sec asc
limit 2
When I run the query above (phpmyadmin helps), it gives back only the row inserted by:
insert into mytime (hour,min,sec) values (0,"4",8) $$
I would like to get back this row too:
insert into mytime (hour,min,sec) values (0,"5",3) $$
Is it possible to fix that example query to do the job?
2
Answers
You should convert your fields hour, min and sec to date format like this :
You could even use > ‘0:4:7’ if you prefer
OR
If you don’t want to convert to date using str_to_date and concat, you could use multiple where clause with OR like this :
You change get result with changing your min condition