title: Group by date range in mysql and php
body: <p>I have a regular table for posts, and it has a datetime field. I want to get posts in specific date ranges.</p>
  
  <p>E.g. I want all posts that were created between <code>Nov 10, 2019</code> and <code>Nov 13, 2019</code> as a single group of posts, and another group of posts that were created in other date range, like between <code>Jan 1, 2019</code> and <code>Jan 15,2019</code>.</p>
  
  <p>What I would like is to have something like this:</p>
  
  <pre><code>[
  "Nov 10,2019-Nov 13,2019"  =&gt;  [['id'=&gt;1,'text'=&gt;'something'],['id'=&gt;2,'text'=&gt;'something'],...],
  "Jan 1,2019-Jan 15,2019"  =&gt;  [['id'=&gt;1,'text'=&gt;'something'],['id'=&gt;2,'text'=&gt;'something'],...],
  ...
  ]
  </code></pre>
  
  <p>Is it possible using a single query?</p>
  
  <p>I know I can group them together using something like <code>GROUP BY DATE(dateTimeField)</code>.</p>
  
  <p>But how can I group it by date range?</p>
  
  <p><strong>EDIT</strong>: what I currently have:</p>
  
  <pre><code>SELECT * FROM posts WHERE ( (datetime &gt;= '2019-11-10 00:00:00' AND datetime &lt;= '2019-11-13 23:59:59') OR (datetime &gt;= '2019-01-01 00:00:00' AND datetime &lt;= '2019-01-15 23:59:59')) LIMIT 0,50
  </code></pre>
  
  <p>this is my query and i know i can use <code>BETWEEN</code> for this purpose but I'm considering that's not the question.</p>
  
  <p>the question is how can I group by the range like this:</p>
  
  <pre><code>SELECT * FROM posts WHERE ( (datetime &gt;= '2019-11-10 00:00:00' AND datetime &lt;= '2019-11-13 23:59:59') OR (datetime &gt;= '2019-01-01 00:00:00' AND datetime &lt;= '2019-01-15 23:59:59')) LIMIT 0,50 GROUP BY THE_RANGE_SPECIFIED_IN_WHERE_CLAUSE
  </code></pre>
  
  <p>i DON'T want this:</p>
  
  <pre><code>SELECT * FROM posts WHERE ( (datetime &gt;= '2019-11-10 00:00:00' AND datetime &lt;= '2019-11-13 23:59:59') OR (datetime &gt;= '2019-01-01 00:00:00' AND datetime &lt;= '2019-01-15 23:59:59')) LIMIT 0,50 date(datetime)
  </code></pre>
  
  <p>i dont want to separate posts based on each individual days but based on the same range I have specfied.</p>