From: | Emir Ibrahimbegovic <emir(dot)ibrahimbegovic(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Inconsistent results postgresql |
Date: | 2014-07-31 00:41:30 |
Message-ID: | CABuViOwjD2wbkjA08Cfc0CgVb29zccNDaC+=JsOck9C11fzYXA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 1ORDER 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 1ORDER 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
From | Date | Subject | |
---|---|---|---|
Next Message | David G Johnston | 2014-07-31 01:21:20 | Re: Inconsistent results postgresql |
Previous Message | Scott Marlowe | 2014-07-30 19:21:28 | Re: free RAM not being used for page cache |