pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to query by month?
4+ messages / 4 participants
[nested] [flat]
* How to query by month?
@ 2016-11-29 14:08 taizi <taizi2006blog@163.com>
0 siblings, 3 replies; 4+ messages in thread
From: taizi @ 2016-11-29 14:08 UTC (permalink / raw)
To: pgsql-admin
Hi all,
im a beginner for SQL, and trying to use it with my daily work.
Now i catched this question(my title of mail).
Suppose there is a table about orders, i want to sum the total amount of each month.
There was a statement of mine to november.
select sum(order_amount) FROM table_name WHERE order_date between '2016-11-01' AND '2016-11-30';
I know it comes a result what i want, but i hope someone can tell me another way to use.
After searched, i found there is a function named date_part, but it doesn't work.
thanks for any help!
Leopold
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to query by month?
@ 2016-11-29 15:06 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: taizi <taizi2006blog@163.com>
2 siblings, 0 replies; 4+ messages in thread
From: Albe Laurenz @ 2016-11-29 15:06 UTC (permalink / raw)
To: 'taizi *EXTERN*' <taizi2006blog@163.com>; pgsql-admin
taizi wrote:
> im a beginner for SQL, and trying to use it with my daily work.
> Now i catched this question(my title of mail).
> Suppose there is a table about orders, i want to sum the total amount of each month.
> There was a statement of mine to november.
> select sum(order_amount) FROM table_name WHERE order_date between '2016-11-01' AND '2016-11-30';
> I know it comes a result what i want, but i hope someone can tell me another way to use.
> After searched, i found there is a function named date_part, but it doesn't work.
You can use
WHERE date_trunc('month', order_date) = '2016-11-01 00:00:00'
The downside is that this clause cannot use a simple index on
"order_date", you'd have to create an index on date_trunc('month', order_date).
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to query by month?
@ 2016-11-29 15:16 Keith <keith@keithf4.com>
parent: taizi <taizi2006blog@163.com>
2 siblings, 0 replies; 4+ messages in thread
From: Keith @ 2016-11-29 15:16 UTC (permalink / raw)
To: taizi <taizi2006blog@163.com>; +Cc: pgsql-admin
On Tue, Nov 29, 2016 at 9:08 AM, taizi <taizi2006blog@163.com> wrote:
> Hi all,
> im a beginner for SQL, and trying to use it with my daily work.
> Now i catched this question(my title of mail).
> Suppose there is a table about orders, i want to sum the total amount of
> each month.
> There was a statement of mine to november.
> select sum(order_amount) FROM table_name WHERE order_date between
> '2016-11-01' AND '2016-11-30';
> I know it comes a result what i want, but i hope someone can tell me
> another way to use.
> After searched, i found there is a function named date_part, but it
> doesn't work.
> thanks for any help!
> Leopold
>
>
>
>
>
I think you want date_trunc(), not date_part. If you want the sum per month
of the whole table do
keith@keith=# create table orders (order_amount numeric, order_date
timestamptz);
CREATE TABLE
Time: 69.647 ms
keith@keith=# insert into orders values (1, generate_series('2016-01-01
00:00:00', '2016-12-31 23:59:59', '1 day'::interval));
INSERT 0 366
Time: 15.211 ms
keith@keith=# SELECT date_trunc('month', order_date), sum(order_amount)
FROM orders GROUP BY date_trunc('month', order_date) ORDER BY 1;
date_trunc | sum
------------------------+-----
2016-01-01 00:00:00-05 | 31
2016-02-01 00:00:00-05 | 29
2016-03-01 00:00:00-05 | 31
2016-04-01 00:00:00-04 | 30
2016-05-01 00:00:00-04 | 31
2016-06-01 00:00:00-04 | 30
2016-07-01 00:00:00-04 | 31
2016-08-01 00:00:00-04 | 31
2016-09-01 00:00:00-04 | 30
2016-10-01 00:00:00-04 | 31
2016-11-01 00:00:00-04 | 30
2016-12-01 00:00:00-05 | 31
(12 rows)
Time: 0.656 ms
If you only need it for part of the table, add a WHERE condition in as
needed.
Keith
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to query by month?
@ 2016-11-29 17:00 Joshua D. Drake <jd@commandprompt.com>
parent: taizi <taizi2006blog@163.com>
2 siblings, 0 replies; 4+ messages in thread
From: Joshua D. Drake @ 2016-11-29 17:00 UTC (permalink / raw)
To: taizi <taizi2006blog@163.com>; pgsql-admin
On 11/29/2016 06:08 AM, taizi wrote:
> Hi all,
> im a beginner for SQL, and trying to use it with my daily work.
> Now i catched this question(my title of mail).
> Suppose there is a table about orders, i want to sum the total amount of
> each month.
> There was a statement of mine to november.
> select sum(order_amount) FROM table_name WHERE order_date between
> '2016-11-01' AND '2016-11-30';
> I know it comes a result what i want, but i hope someone can tell me
> another way to use.
> After searched, i found there is a function named date_part, but it
> doesn't work.
select sum(order_amount) from table_name where date_part('month',
order_date) = '$month';
Note that the $month will be the month number, not name. So for
November, it will = 11.
JD
> thanks for any help!
> Leopold
>
>
>
>
>
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-11-29 17:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-11-29 14:08 How to query by month? taizi <taizi2006blog@163.com>
2016-11-29 15:06 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2016-11-29 15:16 ` Keith <keith@keithf4.com>
2016-11-29 17:00 ` Joshua D. Drake <jd@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox