CODEWITHSUNDEEP
postgresqldategroup-bytimestamptimezone
Mzia
Mzia

Reputation: 456

Group sum by day + pair_id if data in db has timezone offset

I have a server that gives me some information grouped by day ( I use date_trunc() for it). Because of the timezone I know that 2020-06-06 21:00:00 in my DB is actually 2020-06-07 00:00:00. I need to count sum of column meta, grouped by (tm + pair_id), but because of offset data from db that has 2020-06-06 21:00 tm doesn't sum with other 2020-06-07 data. As a result instead of total amount per day+pair_id I have few rows with the same tm and pair_id (because 2020-06-06 21:00 became 2020-06-07 00:00 in the result)

Can I fix grouping here without creating subrequests?

I have table: 

   CREATE TABLE tests.tbl (
    id int4 NULL,
    tm timestamp NULL,
    meta float4 NULL,
    pair_id int4 NULL
);

With this data in it:

enter image description here

My query is:

SELECT 
    pair_id,
    date_trunc('day', min(tm) at time zone '3' at time zone 'UTC') as tm,
    sum(meta)
FROM tests.tbl
WHERE (tm BETWEEN '2020-06-06 21:00:00+00:00:00' AND '2020-06-08 20:59:00+00:00:00')
group by pair_id, tm
order by tm, pair_id;

My result is:

enter image description here

Expected result is:

enter image description here

Upvotes: 0

Views: 107

Answers (1)

GMB
GMB

Reputation: 222632

I think the logic you want is:

SELECT 
    pair_id,
    date_trunc('day', tm at time zone '3' at time zone 'UTC') as tm,
    sum(meta)
FROM tests.tbl
WHERE tm BETWEEN '2020-06-06 21:00:00+00:00:00' AND '2020-06-08 21:00:00+00:00:00'
GROUP BY 1, 2
ORDER BY 2, 1;

That is: the select and group by clause should be consistent (in particular, the timestamp column should be offset in both clauses).

It might be simpler to understand if you move the offset to a subquery:

SELECT pair_id, date_trunc('day', tm) as tm, sum(meta)
FROM (
    SELECT 
        pair_id,
        tm at time zone '3' at time zone 'UTC' as tm,
        meta
    FROM tests.tbl
) t
WHERE tm >= '2020-06-07'::date AND tm < '2020-06-09'::date
GROUP BY 1, 2
ORDER BY 2, 1;

Upvotes: 1

Related Questions