The title doesn't actually fully describes the problem, that is: I have a table with dates -
1. 2011-07-01 13:01:48
2. 2011-07-01 13:09:36
3. 2011-07-01 13:21:24
4. 2011-07-01 13:35:12
5. 2011-07-01 13:49:23
6. 2011-07-01 13:57:47
7. 2011-07-01 14:05:12
8. 2011-07-01 14:12:45
9. 2011-07-01 14:31:48
10. 2011-07-01 14:47:31
and so on. What I need is to get three random dates of each hour, for example:
1. 2011-07-01 13:01:48
2. 2011-07-01 13:21:24
3. 2011-07-01 13:49:23
4. 2011-07-01 14:05:12
5. 2011-07-01 14:12:45
6. 2011-07-01 14:47:31
How can I do it in mysql?
Tips: If you want to generate random workdays excluding the weekends, please apply the below formula: =WORKDAY(RANDBETWEEN(DATE(2019, 1, 1),DATE(2019, 10, 1))-1,1)
To generate random dates between two dates, you can use the RANDBETWEEN function, together with the DATE function. This formula is then copied down from B5 to B11. The result is random dates between Jan 1, 2016 and Dec 31, 2016 (random dates in the year 2016).
Select the cell that contains the first date. Drag the fill handle across the adjacent cells that you want to fill with sequential dates. at the lower-right corner of the cell, hold down, and drag to fill the rest of the series. Fill handles can be dragged up, down, or across a spreadsheet.
create table dates (`date` datetime);
insert into dates (`date`) values
('2011-07-11 06:05:02'),
('2011-07-11 06:15:02'),
('2011-07-11 06:45:02'),
('2011-07-11 06:55:02'),
('2011-07-11 06:56:02'),
('2011-07-11 08:05:02'),
('2011-07-11 08:07:02'),
('2011-07-11 08:09:02'),
('2011-07-11 08:11:02'),
('2011-07-11 08:40:02'),
('2011-07-11 09:05:02'),
('2011-07-11 11:10:02'),
('2011-07-11 11:11:02'),
('2011-07-11 11:55:02')
;
set @i := 0;
set @d := '';
select `date`
from (
select
case
when @d != date_format(`date`, '%Y-%m-%d %H')
then @i := 0
else @i := @i + 1
end as i,
case
when @d != date_format(`date`, '%Y-%m-%d %H')
then @d := date_format(`date`, '%Y-%m-%d %H')
end as d,
case when @i < 3 then `date` else null end as `date`
from (
select `date`
from dates
order by date_format(`date`, '%Y-%m-%d %H'), rand()
) ss
) sw
where `date` is not null
order by `date`
;
This should work quite well if you have statistically enough rows per hour:
select *
from (
select *
from yourtable
order by rand()
)
group by date(yourdate), hour(yourdate), floor(rand()*3)
if data set is not large -- if your data set is large then you should not use some random algorithm on the data set anyway, then you can use the following simple query:
select * from t order by rand() limit 6;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With