Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting three random dates from each hour

Tags:

random

mysql

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?

like image 978
Vadim Samokhin Avatar asked Jul 11 '11 13:07

Vadim Samokhin


People also ask

How do you use Randbetween for dates?

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)

How do you generate random dates?

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

How do I create a date generator in Excel?

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.


3 Answers

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`
;
like image 106
Clodoaldo Neto Avatar answered Oct 13 '22 20:10

Clodoaldo Neto


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)
like image 31
Karolis Avatar answered Oct 13 '22 21:10

Karolis


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;
like image 34
James.Xu Avatar answered Oct 13 '22 21:10

James.Xu