Re: Inconsistent results postgresql - Mailing list pgsql-general
From | David G Johnston |
---|---|
Subject | Re: Inconsistent results postgresql |
Date | |
Msg-id | 1406769680294-5813377.post@n5.nabble.com Whole thread Raw |
In response to | Inconsistent results postgresql (Emir Ibrahimbegovic <emir.ibrahimbegovic@gmail.com>) |
List | pgsql-general |
Emir Ibrahimbegovic wrote > 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 There may be a timezone issue here - hard to tell without seeing the data types for subscribed_at and created_at. The first thing I would do is move the WHERE clause portion of the query into a CTE: WITH detail AS ( SELECT ) Then I would run the GROUP BY portion over the CTE SELECT * FROM ( SELECT date_trunc(...), sum(...) FROM detail WHERE date_trunc(...) GROUP BY 1 ) src WHERE date_trunc = '2014-07-07'::date Assuming you are still getting different sums you can now readily list the corresponding detail (i.e., remove the group by) and see which records show in the one set but not the other. You can add additional CTE queries to facilitate this if desired - i.e. put each where clause into its own detail query and, if you have some kind of ID to compare against, perform a FULL OUTER JOIN between the two CTE tables and exclude any items that appear in both. Ignoring indexes for the moment it would be much safer and clearer to write the WHERE clause using "BETWEEN date AND date" instead of using timestamps with subsecond precision. If you do insist on using timestamps I would then suggest at least explicitly casting them. '2014-07-07 00:00:00.000000'::timestamp David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Inconsistent-results-postgresql-tp5813375p5813377.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
pgsql-general by date: