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

Question

I have a transaction table which contains net_amount field and last_updated_time field, where last_updated_time is stored 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 1

You can do something like:

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

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

EDIT: I converted last_update_time to timestamp as pointed out by @a_horse_with_no_name.

Answer 2

If I understand your question correctly, you may try to do something like the following (Java 8)

     long day1MSec, day2MSec ;

     LocalDate localDate1 = LocalDate.of( 2011 , Month.JULY , 3 );    
     LocalDate localDate2 = LocalDate.of( 2011 , Month.JULY , 25 );    

     final long msPerDay = 24 * 60 * 60 * 1000;//milisec per day

     day1MSec = localDate1.toEpochDay() * msPerDay;
     day2MSec = localDate2.toEpochDay() * msPerDay;

     //now your sql would look something like
     String sql = "select sum(amount)from transaction group by last_updated having last_updated between "+day1MSec + " and "+day2MSec;

So all you need to do in your Java code is convert dates to miliseconds. If you want to use months or years , just adjust your date to match that of the beginning of a month or a year.

LocalDate localDate1 = LocalDate.of( 2011 , Month.JANUARY , 1 );   

UPDATE: For java versions lower than 8, you can use

     Date date =     new SimpleDateFormat("yyyy-MM-dd", Locale.ITALY).parse("2015-06-25");
     long mSec = date.getTime(); //this returns miliseconds 

Strangely the two versions differ in the results produced so I wonder whether there's a bug in the first version (because the second version seems to give the correct result)