From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "'taizi *EXTERN*'" <taizi2006blog(at)163(dot)com>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: How to query by month? |
Date: | 2016-11-29 15:06:04 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B5399E643@ntex2010i.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Keith | 2016-11-29 15:16:23 | Re: How to query by month? |
Previous Message | xu jian | 2016-11-29 15:01:38 | can pg 9.6 vacuum freeze skip page on index? |