title: Improve sql query (JPA?)
body: <p>I don't know enough about sql to improve a database query.</p>
  
  <p>I have the following native sql query:</p>
  
  <pre><code>SELECT 
     m.id, 
     m.merchantname, 
     date_trunc('month' ,to_timestamp(t.timestamp / 1000)) as month_name,
     COUNT(*) as num_transactions, 
     cast(SUM(case when t.type IN('VEND_READER','VEND_READER_COUPON_TRANSFER') THEN t.amount ELSE 0 END) as numeric) AS sum_vend, 
     cast(SUM(case when t.type IN('REVALUE_READER','REVALUE_STARTBALANCE','REVALUE_PAYPAL','REVALUE_PROMOTION','REVALUE_CREDITCARD') THEN t.amount ELSE 0 END) AS numeric) as sum_revalue 
  
  FROM 
     transaction t JOIN merchant m ON m.id = t.merchant_id 
  
  WHERE 
     t.transactionstate = 'SUCCESS' 
  GROUP BY 
     m.id, month_name  
  ORDER BY
     m.id
  </code></pre>
  
  <p><strong>All I want are entries including sum of vends/revaluations and count of transactions for every merchant and month.</strong> </p>
  
  <p>It doesn't matter how the <em>month</em> field is formatted (It could be as sql timestamp or string or ...)</p>
  
  <pre><code>[
   {merchantID:1, merchantName:"myMerchant", vend:2030, revalue:1980, transactions:115, month:"July - 2016"},
   {merchantID:1, merchantName:"myMerchant", vend:1895, revalue:2700, transactions:100, month:"June - 2016"},
   {merchantID:1, merchantName:"myMerchant", vend:2453, revalue:1892, transactions:120, month:"May - 2016"},
   {merchantID:2, merchantName:"myMerchant2", vend:345, revalue:360, transactions:123, month:"July - 2016"},
   ...
  ]
  </code></pre>
  
  <p>If such a query is possible with JPA it would be great. But I couldn't find a way to convert an epoch time in miliseconds to an month. All our timestamps(miliseconds since 1970) are saved as BIGINT.</p>
  
  <p>Merchant.java</p>
  
  <pre><code>@Entity
  public class Merchant
  {
      @Id
      @GeneratedValue
      private Long id;
  
      private Integer merchantGlobalID;
  
      private String merchantName;
  }
  </code></pre>
  
  <p>Transaction.java</p>
  
  <pre><code>@Entity
  public class Transaction
  {
      @Id
      @GeneratedValue
      private Long id;
  
      @ManyToOne(fetch = FetchType.LAZY)
      private Merchant merchant;
  
      private Integer amount;
  
      private Long timestamp;
  
      @Enumerated(EnumType.STRING)
      private TransactionType type;
  
      @Enumerated(EnumType.STRING)
      private TransactionState transactionState;
  }
  </code></pre>
  
  <p>My query works, but it has a really bad performance. We use a postgres database and the current query use some specific commands. If there is a way to get the results in JPA it would be nice.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>On advice of <a href="https://stackoverflow.com/users/557470/robe-elckers">Robe Elckers</a> I call an <code>EXPLAIN ANALYZE</code> query. Here are the results. I don't know what I should do now.</p>
  
  <pre><code>GroupAggregate  (cost=797050.04..849223.46 rows=1070224 width=559) (actual time=6913.003..7368.277 rows=16 loops=1)
    Group Key: m.id, (date_trunc('month'::text, to_timestamp(((t."timestamp" / 1000))::double precision)))
    -&gt;  Sort  (cost=797050.04..799725.60 rows=1070224 width=559) (actual time=6792.567..7049.720 rows=1070341 loops=1)
          Sort Key: m.id, (date_trunc('month'::text, to_timestamp(((t."timestamp" / 1000))::double precision)))
          Sort Method: external merge  Disk: 57384kB
          -&gt;  Hash Join  (cost=4.61..155797.88 rows=1070224 width=559) (actual time=0.104..6005.352 rows=1070341 loops=1)
                Hash Cond: (t.merchant_id = m.id)
                -&gt;  Seq Scan on transaction t  (cost=0.00..130375.45 rows=1070224 width=32) (actual time=0.006..3502.850 rows=1070341 loops=1)
                      Filter: ((transactionstate)::text = 'SUCCESS'::text)
                      Rows Removed by Filter: 1598
                -&gt;  Hash  (cost=4.27..4.27 rows=27 width=535) (actual time=0.023..0.023 rows=27 loops=1)
                      Buckets: 1024  Batches: 1  Memory Usage: 2kB
                      -&gt;  Seq Scan on merchant m  (cost=0.00..4.27 rows=27 width=535) (actual time=0.004..0.015 rows=27 loops=1)
  Planning time: 0.535 ms
  Execution time: 7373.833 ms
  </code></pre>