Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>In MySQL, you can assign variables in a SELECT statement while retrieving data. This functionality helps in solving many problems where one would "normally" use windowing functions (which MySQL doesn't have). It can also help in yours. Here's a solution I ended up with:</p> <pre><code>SET @startdate = CAST(NULL AS datetime); SET @granularity = 60; /* minutes */ SET @minduration = 180; /* minutes */ SET @minvalue = 10; SELECT t.Date, t.Value FROM ( SELECT StartDate, MAX(Date) AS EndDate FROM ( SELECT Date, Value, CASE WHEN Value &gt; @minvalue OR @startdate IS NOT NULL THEN IFNULL(@startdate, Date) END AS StartDate, @startdate := CASE WHEN Value &gt; @minvalue THEN IFNULL(@startdate, Date) END AS s FROM ( SELECT Date, Value FROM YourTable UNION ALL SELECT MAX(Date) + INTERVAL @granularity MINUTE, @minvalue FROM YourTable ) s ORDER BY Date ) s WHERE StartDate IS NOT NULL GROUP BY StartDate ) s INNER JOIN YourTable t ON t.Date &gt;= s.StartDate AND t.Date &lt; s.EndDate WHERE s.EndDate &gt;= s.StartDate + INTERVAL @minduration MINUTE ; </code></pre> <p>Three of the four variables used here are merely script arguments, and only one, <code>@startdate</code>, actually gets both assigned and checked in the query.</p> <p>Basically, the query iterates over the rows, marking those where the value is greater than a specific minimum (<code>@minvalue</code>), eventually producing a list of time ranges during which values matched the condition. Actually, in order to calculate the ending bounds correctly, non-matching rows that immediately follow groups of the matching ones are also included in the respective groups. Because of that, an extra row is being added to the original dataset, where <code>Date</code> is calculated off the latest <code>Date</code> plus the specified <code>@granularity</code> of timestamps in your table and <code>Value</code> is just <code>@minvalue</code>.</p> <p>Once obtained, the list of ranges is joined back to the original table to retrieve the detail rows that fall in between the ranges' bounds, the ranges that are not long enough (as specified by <code>@minduration</code>) being filtered out along the way.</p> <p>If you run this solution <a href="http://sqlfiddle.com/#!2/250e6/5" rel="nofollow">on SQL Fiddle</a>, you will see the following output:</p> <pre><code>DATE VALUE ------------------------------ ----- January, 01 2000 03:00:00-0800 11 January, 01 2000 04:00:00-0800 11 January, 01 2000 05:00:00-0800 11 </code></pre> <p>which, I understand, is what you would expect.</p>
    singulars
    1. This table or related slice is empty.
    1. POMysql query with criteria value > 10 for consecutive date periode of 3 hours
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USAndriy M
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMysql query with criteria value > 10 for consecutive date periode of 3 hours
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COexcellent solution! Thanks for the explanation! Much appreciated
      singulars
      1. PO
      1. USPaulten
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload