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          |
+----------+---------------+
| ---      | ----          |
+----------+---------------+

I'd make a View where the millisecond yielding column is converted to a datetime datatype. Then Grouping on that should be possible through SQL. — Fildor, Sep 16 '16 at 10:40
Is it possible to solve this problem using TO_TIMESTAMP(),date_part() functions in PostgreSQL. — Shibina EC, Sep 17 '16 at 05:34

Ondrej Burkert · Accepted Answer · 2016-09-19T10:58:46.637

5

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.

edited Sep 19 '16 at 10:58

answered Sep 16 '16 at 14:15

Ondrej Burkert

6,617
2
32
27

This would require last_updated_time to be a timestamp, which apparently isn't the case. And even if worked, this would be very inefficient for large data sets. – dsp_user Sep 16 '16 at 17:49
2

@dsp_user: aggregating _all_ rows of a table will never be "very efficient". This query is probably the most efficient way to do it. You are right about the timestamp though, but this can easily be changed: `date_trunc('month', to_timestamp(last_updated_time/1000))` – Sep 17 '16 at 07:27
This query may very well be the most efficient way to do this in SQL. However, it makes more sense to me to (pre)calculate the conditions in Java so as to avoid calling date_trunc() and to_timestamp() for every single row. The OP can , of course, use this approach if suits him/her better (for whatever reason). – dsp_user Sep 17 '16 at 09:35
upvote for providing a completely sql solution (note the correction given by a_horse_with_no_name though). – dsp_user Sep 17 '16 at 09:44
This is what I exactly looking for,Thank you all for giving support,especially for Ondrej Burkert and a_horse_with_no_name. – Shibina EC Sep 17 '16 at 10:46

dsp_user · Answer 2 · 2016-09-17T09:39:22.790

1

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)

edited Sep 17 '16 at 09:39

answered Sep 16 '16 at 11:16

dsp_user

2,061
2
16
23

`HAVING` is just a `WHERE` after `GROUP BY`. It doesn't _group by_ the _having_ clause. – mroman Sep 16 '16 at 13:02
Yeah, but he wants to group by month, not by last_updated. He needs something like `GROUP BY MONTH(last_updated_time)`. – mroman Sep 16 '16 at 14:26
If he/she comes up with the right logic in Java , his / her sql doesn't have to involve MONTH or any other PostgreSql function for that matter. Grouping by MONTH could simply involve having a start date and end date (01 - 31 or 01-30 ) – dsp_user Sep 16 '16 at 17:32
Still, what you were proposing is something like http://sqlfiddle.com/#!9/775a34/1/0 which is just plain wrong. If you want to do more logic in Java then you have to do `SELECT SUM(...) FROM ... WHERE last_updated BETWEEN foo AND bar`. Grouping by `last_updated` is horribly wrong. – mroman Sep 17 '16 at 12:34
The focus of my answer was purely on Java, not on sql (because the original (unedited) question included Java tags, not sql tags). The group by clause in the Java version would indeed be unnecessary (redundant) or had to be changed (similar to what you proposed). When I wrote my answer I though the OP was interested in a Java solution, not an sql one. – dsp_user Sep 17 '16 at 13:41

score 0 · Answer 3 · edited May 23 '17 at 12:34

You could write a query that gives you the aggregation all ready:

create table Transaction 
(
    amount DECIMAL(9,2),
    last_updated_time BIGINT
);

insert into Transaction values (10, 1472680800000); -- 2016-09-01
insert into Transaction values (20, 1473458400000); -- 2016-09-10
insert into Transaction values (30, 1474408800000); -- 2016-09-21
insert into Transaction values (5,  1475272800000); -- 2016-10-01
insert into Transaction values (2,  1475272800000); -- 2016-10-01
insert into Transaction values (7,  1475272800000); -- 2016-10-02
insert into Transaction values (15, 1478818800000); -- 2016-11-11

Use EXTRACT in compbination with to_timestamp to extract what ever you want (MONTH, YEAR) and then group by it.

select
    sum(amount),
    EXTRACT(MONTH FROM to_timestamp(last_updated_time/1000)) as month 
from Transaction
group by month order by month asc;

  sum  | month
-------+-------
 60.00 |     9
 14.00 |    10
 15.00 |    11


select
    sum(amount),
    EXTRACT(YEAR FROM to_timestamp(last_updated_time/1000)) as year
from Transaction
group by year order by year asc;

  sum  | year
-------+--------
 89.00 |   2016

And finally to address my comment, you could EXTRACT both the year and the month in your select and then group by them both (see here).

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

3 Answers3