title: Calculate average exchange rate for time period
body: <p>In Django I have model similar to this example:</p>
  
  <pre><code>class Currency(models.Model):
      name = models.CharField(max_length=3, unique=True)
      full_name = models.CharField(max_length=20)
  
  
  class ExchangeRate(models.Model):
      currency = models.ForeignKey('Currency')
      start_date = models.DateFiled()
      end_date = models.DateField()
      exchange_rate = models.DecimalField(max_digits=12, decimal_places=4)
  </code></pre>
  
  <p>Let's simplify this and assume that we have only one currency and <code>ExchangeRate</code> table looks like:</p>
  
  <pre><code>+---------------------+-------------------+------------+------------+---------------+
  | currency_from__name | currency_to__name | start_date |  end_date  | exchange_rate |
  +---------------------+-------------------+------------+------------+---------------+
  |        PLN          |        USD        | 2014-03-01 | 2014-08-01 |    3.00000    |
  |        PLN          |        USD        | 2014-08-01 | 2014-12-01 |    6.00000    |
  +---------------------+-------------------+------------+------------+---------------+
  </code></pre>
  
  <p><strong><em>Note that</strong> this is example to simplify math operations!</em></p>
  
  <p>In this table data density is once per month and valid record for one month is for example when <code>start_date = 2014.03.01</code> and <code>end_date = 2014.04.01</code>, so <code>start_date</code> is inclusive and <code>end_date</code> is exclusive.</p>
  
  <p>I want to calculate average exchange rate for time period: </p>
  
  <p><a href="https://i.stack.imgur.com/U330R.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/U330R.gif" alt="[2014.06.01 ; 2012.09.01)"></a></p>
  
  <p>Which means: <a href="https://i.stack.imgur.com/bysFA.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bysFA.gif" alt="&gt;= 2014.06.01"></a> and <a href="https://i.stack.imgur.com/ouTUR.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ouTUR.gif" alt="&lt; 2014.09.01"></a></p>
  
  <p>When in Django I write:</p>
  
  <pre><code>start_date = date(2014, 6, 1)
  end_date = date(2014, 9, 1)
  
  ExchangeRate.objects.all().filter(
          (
              Q(start_date__lt=start_date) &amp; 
              Q(end_date__gt=start_date)
          ) | (
              Q(start_date__gte=start_date) &amp; 
              Q(start_date__lt=end_date) &amp; 
              Q(end_date__gt=start_date) 
          )
  ).annotate(
      currency_from_name = 'currency_from__name', 
      currency_to_name = 'currency_to__name'
  ).values(  # GROUP BY
      'currency_from_name',
      'currency_to_name'
  ).aggregate(
      F('currency_from_name'), 
      F('currency_to_name'), 
      Avg('exchange_rate')
  )
  </code></pre>
  
  <p>After this query I'm receiving value <code>4.5000</code> which from mathematical reason is correct but wrong when You need to take care of time range.<br>
  Correct answer is <code>4.000</code>.</p>
  
  <p>I only came up with this solution to annotate extra column with this formula and then calculate average value from this column:</p>
  
  <p><a href="https://i.stack.imgur.com/J7Abo.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/J7Abo.gif" alt="https://www.codecogs.com/eqnedit.php?latex=\inline&space;Abs&space;\left&space;(&space;\frac{months&space;\left&space;(&space;greater(ER_{start_date}\&space;,\&space;start_date),&space;smaller(ER_{start_date}\&space;,\&space;end_date)&space;\right&space;)&space;}{months(start_date\&space;,\&space;end_date)}&space;\right&space;)&space;*&space;ER_{exchange_rate}"></a></p>
  
  <p>Where:</p>
  
  <ul>
  <li><code>Abs</code> is function for absolute value <a href="http://www.postgresql.org/docs/7.4/static/functions-math.html#FUNCTIONS-MATH-FUNC-TABLE" rel="nofollow noreferrer"><code>abs()</code></a></li>
  <li><code>months</code> is function to calculate months between two dates <a href="https://stackoverflow.com/a/14258327/320104"><code>months_between()</code></a></li>
  <li><code>greater</code>, <code>smaller</code> are functions to choose accordingly greater and smaller value from arguments - <a href="http://www.postgresql.org/docs/9.3/static/functions-conditional.html#FUNCTIONS-GREATEST-LEAST" rel="nofollow noreferrer"><code>greatest()</code>, <code>least()</code></a></li>
  <li><code>ER</code> means column from <code>ExchangeRate</code> - e.g. <a href="https://docs.djangoproject.com/en/1.7/ref/models/queries/#f-expressions" rel="nofollow noreferrer"><code>F('exchange_rate')</code></a></li>
  </ul>
  
  <p>I'm using <strong>9.3 PostgreSQL DB</strong> and <strong>Django 1.8.4</strong>.</p>
  
  <p>Maybe there is a simple function for that?<br>
  Maybe I'm overcomplicating this?</p>