Shibina EC Shibina EC - 2 months ago 6
SQL Question

How to Group By using Month from date stored as millisecond Postgres

I have a transaction table contain Amount field and last_updated_time field,where last_updated_time stores as milliseconds, I need to get total amount group by using month, year or date. How can I do this in PostgreSQL.

My table looks like as below.

+------------+-------------------+
| net_amount | last_updated_time |
+------------+-------------------+
| 100 | 1470286872831 |
+------------+-------------------+
| 200 | 1471594713801 |
+------------+-------------------+
| 300 | 1471594651335 |
+------------+-------------------+


and expecting result as,

+----------+---------------+
| month | sum_of_amount |
+----------+---------------+
| january | 1000 |
+----------+---------------+
| february | 2000 |
+----------+---------------+
| --- | ---- |
+----------+---------------+

Answer

You can do something like:

SELECT sum(amount), date_trunc('month', last_updated_time)
FROM transaction
GROUP BY date_trunc('month', last_updated_time);

I just checked it on my side project database and it works for me.