查找最近2个日期的数据的间隔

Question

I'm developing a web-based tool that can help analyze number intervals that occurs in a 6-digit lottery.

Let us focus on a certain number first. Say 7

The sql query I've done so far:

SELECT * FROM `l642` WHERE `1d`=7 OR `2d`=7 OR `3d`=7 OR `4d`=7 OR `5d`=7 
OR `6d`=7 ORDER BY `draw_date` DESC LIMIT 2

This will pull the last two latest dates where number 7 is present

I'm thinking of using DATEDIFF but I'm confused on how to get the previous value to subtract it on the latest draw_date

My goal is to list the intervals of numbers 1-42 and I'll plan to accomplish it using PHP.

Looking forward to your help

Answer 1

A few ideas spring to mind.

(1) First, since you perfectly have your result set ordered, use PHP loop on the two rows getting $date1 =$row['draw_date']. Then fetch next/last row and set $date2 =$row['draw_date']. With these two you have

$diff=date_diff($date1,$date2);

as the difference in days.

(2) A second way is to have mysql return datediff by including a rownumber in the resultset and doing a self-join with aliases say alias a for row1 and alias b for row2.

datediff(a.draw_date,b.drawdate).

How one goes about getting rownumber could be either:

(2a) rownumber found here: With MySQL, how can I generate a column containing the record index in a table?

(2b) worktable with id int auto_increment primary key column with select into from your shown LIMIT 2 query (and a truncate table worktable between iterations 1 to 42) to reset auto_increment to 0.

The entire thing could be wrapped with an outer table 1 to 42 where 42 rows are brought back with 2 columns (num, number_of_days), but that wasn't your question.

So considering how infrequent you are probably doing this, I would probably recommend not over-engineering it and would shoot for #1