Re: Inconsistent results postgresql - Mailing list pgsql-general

Hello all,

I've got two queries which should produce the same results but they don't for some reason, please consider these :

SELECT date_trunc('day', payments.created_at) "day", SUM("payments"."amount") AS sum_id 
FROM "payments"  INNER JOIN "users" ON "users"."id" = "payments"."user_id" 
WHERE "payments"."currency" = 'gbp' AND (payments.refunded_date is null)  AND 
(users.deleted_at is null)  AND 
(users.subscribed_at between '2014-07-07 00:00:00.000000' and '2014-07-07 23:59:59.999999')  AND 
("payments"."created_at" BETWEEN '2014-07-07 00:00:00.000000' AND '2014-07-07 23:59:59.999999')
GROUP BY 1
ORDER by 1 asc

It produces this :

day                   | sum_id
------------------------------
"2014-07-07 00:00:00" | 1863.85

But when I try to group by day at looking at 30 days period with this query :

SELECT date_trunc('day', payments.created_at) "day", SUM("payments"."amount") AS sum_id 
FROM "payments"  INNER JOIN "users" ON "users"."id" = "payments"."user_id" 
WHERE "payments"."currency" = 'gbp' AND (payments.refunded_date is null)  AND 
(users.deleted_at is null)  AND 
(users.subscribed_at between '2014-06-30 00:00:00.000000' and '2014-07-30 23:59:59.999999')  AND 
("payments"."created_at" BETWEEN '2014-06-30 00:00:00.000000' AND '2014-07-30 23:59:59.999999')
GROUP BY 1
ORDER by 1 asc

It produces this (truncated to include the important data only) :

day                   | sum_id
------------------------------
"2014-07-07 00:00:00" | 1898.84

So looking for same date using different date range I get different results, how is this even possible? Can I look at something else? I'm really stuck here

Thanks