Mysql need to select range of rows based on two columns values

Question

I have the following table as follows

date          hour     click
2015-02-27    00        7
2015-02-27    01        7
2016-02-27    02        7
2016-02-27    02        8
2016-02-27    03        7
2016-02-27    04        9
.             .         .
.             .         .
2016-02-27    23        9
.             .         .
.             .         .
.             .         .
.             .         .
2016-03-02    00        9
2016-02-02    01        5
2016-02-02    02        18

We store the date in one column in hour in another column.

I need to select the range of rows where i pass the from date and hour to date and hour. I had get it for two different dates as follows.

select b.date, b.hour, sum(b.click)
from clickinfo b
where (b.date ='2016-01-27' and b.hour>='02')
   or (b.date >'2016-01-27' and b.date <'2016-02-02')
   or (b.date='2016-02-02' and b.hour<=05)
group by b.`date`, b.hour;

but i am not able to select the range for same day date and different hours.

That is from date - '2016-01-27' and hour -'05' to date - '2016-01-27'and hour = 23

Seriously consider storing date and time as a single entity. — Strawberry
– Strawberry, Commented Mar 4, 2016 at 9:26
that is part of other project design. we only get the data what we need. — pavan
– pavan, Commented Mar 4, 2016 at 9:28

Slava N. · Accepted Answer · 2016-03-04 11:02:40Z

1

create temporary table clickinfo (date1 varchar(50), hour1 varchar(50), click int);

insert into clickinfo (date1, hour1, click) values
('2015-02-27'    ,'00',        7)
,('2015-02-27'    ,'01',        7)
,('2015-02-27'    ,'05',        7)
,('2016-02-27'    ,'02',        7)
,('2016-02-27'    ,'02',        8)
,('2016-02-27'    ,'03',        7)
,('2016-02-27'    ,'04',        9)
,('2016-02-27'    ,'05',        9);


select
  date_add(date1 ,  interval hour1 hour), sum(click) from clickinfo
where
  date_add(date1 ,  interval hour1 hour)
      between str_to_date('2015-02-27 5:0', '%Y-%m-%d %H:%i')
              and str_to_date('2016-02-28 23:0', '%Y-%m-%d %H:%i')
group by
  date_add(date1 ,  interval hour1 hour);

edited Mar 4, 2016 at 11:02

answered Mar 4, 2016 at 9:01

Slava N.

5964 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pavan Over a year ago

hey can u please check your query . i am still not able to get the rows always showing empty rows.

alexander.polomodov · Accepted Answer · 2016-03-04 08:51:08Z

1

You can just add another or condition on case equal date of start and end:

select b.date, b.hour, sum(b.click)
from clickinfo b
where (b.date ='2016-01-27' and b.hour>='02')
   or (b.date >'2016-01-27' and b.date <'2016-02-02')
   or (b.date='2016-02-02' and b.hour<=05)
   or ('2016-01-27' == '2016-02-02' and b.hour>='02' and b.hour<=05)
group by b.`date`, b.hour;

answered Mar 4, 2016 at 8:51

alexander.polomodov

5,54414 gold badges44 silver badges48 bronze badges

2 Comments

pavan Over a year ago

What about for same date with different hours.

alexander.polomodov Over a year ago

last or in the where section do this for you ('start_date' == 'finish_date' and b.hour>='start_hour' and b.hour<='finish_hour')

Collectives™ on Stack Overflow

Mysql need to select range of rows based on two columns values

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related