pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to specify the beginning of the month in Postgres SQL syntax?
5+ messages / 5 participants
[nested] [flat]
* How to specify the beginning of the month in Postgres SQL syntax?
@ 2003-12-07 16:16 jeff@dnuk.com
2003-12-07 19:38 ` Re: How to specify the beginning of the month in Postgres SQL syntax? CoL <col@mportal.hu>
2003-12-10 00:52 ` Re: How to specify the beginning of the month in Postgres SQL syntax? Paul Thomas <paul@tmsl.demon.co.uk>
2003-12-10 05:31 ` Re: How to specify the beginning of the month in Postgres SQL syntax? sqllist <sqllist@coralindia.com>
0 siblings, 3 replies; 5+ messages in thread
From: jeff@dnuk.com @ 2003-12-07 16:16 UTC (permalink / raw)
To: pgsql-sql
Hello,
I need to create a view in Postgres that has a where clause of the
date < beginning of month.
i.e.:
SELECT supplier_number, Sum(amount) AS due
FROM purchase_orders
WHERE date < '2003-12-1' AND paid = 0
GROUP BY supplier_number
ORDER BY supplier_number ASC
As you can see, I've specified the 1st of December this year as the
where clause. What I want is an SQL statement that automatically
generates the first of the month. How do I do this?
Thanks.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to specify the beginning of the month in Postgres SQL syntax?
2003-12-07 16:16 How to specify the beginning of the month in Postgres SQL syntax? jeff@dnuk.com
@ 2003-12-07 19:38 ` CoL <col@mportal.hu>
2 siblings, 0 replies; 5+ messages in thread
From: CoL @ 2003-12-07 19:38 UTC (permalink / raw)
To: pgsql-sql
hi
jeff@dnuk.com wrote, On 12/7/2003 5:16 PM:
> Hello,
>
> I need to create a view in Postgres that has a where clause of the
> date < beginning of month.
>
> i.e.:
> SELECT supplier_number, Sum(amount) AS due
> FROM purchase_orders
> WHERE date < '2003-12-1' AND paid = 0
> GROUP BY supplier_number
> ORDER BY supplier_number ASC
>
>
> As you can see, I've specified the 1st of December this year as the
> where clause. What I want is an SQL statement that automatically
> generates the first of the month. How do I do this?
>
a simple way:
create function month_begin() returns date as '
SELECT to_date(current_date,'YYYY-MM');
' language sql STABLE;
... where date < month_begin() ....
C.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to specify the beginning of the month in Postgres SQL syntax?
2003-12-07 16:16 How to specify the beginning of the month in Postgres SQL syntax? jeff@dnuk.com
@ 2003-12-10 00:52 ` Paul Thomas <paul@tmsl.demon.co.uk>
2 siblings, 0 replies; 5+ messages in thread
From: Paul Thomas @ 2003-12-10 00:52 UTC (permalink / raw)
To: jeff@dnuk.com; +Cc: pgsql-sql
On 07/12/2003 16:16 jeff@dnuk.com wrote:
> Hello,
>
> I need to create a view in Postgres that has a where clause of the
> date < beginning of month.
>
> i.e.:
> SELECT supplier_number, Sum(amount) AS due
> FROM purchase_orders
> WHERE date < '2003-12-1' AND paid = 0
> GROUP BY supplier_number
> ORDER BY supplier_number ASC
>
>
> As you can see, I've specified the 1st of December this year as the
> where clause. What I want is an SQL statement that automatically
> generates the first of the month. How do I do this?
I do shed-loads of these date-related queries and although it's feasible
to write some SQL/UDF function to do what you're asking, in my experience
it is better to process the date in your app and pass it across as a
parameter. That way you could use the same piece of SQL to get, for
example, data which is > month owing just by passing 2003-11-01 as the
date. Probably what you need is to write a function which takes an
arbitary date and returns the first date in that month/year. You _could_
write this as PostgreSQL User Defined Function but writing it as part of
your app will give you a) greater flexibility as the function will be
easily available to other parts of your application b) if your app
language/dev environment has a source-level debugger, you will be able to
benefit from it when debugging your function and c) someone trying to
maintain your app in 4 years time will only need to know your application
language, SQL and a possible (very!) few PostgreSQL-specific variations
from the SQL language definition.
HTH
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to specify the beginning of the month in Postgres SQL syntax?
2003-12-07 16:16 How to specify the beginning of the month in Postgres SQL syntax? jeff@dnuk.com
@ 2003-12-10 05:31 ` sqllist <sqllist@coralindia.com>
2003-12-10 15:51 ` Re: How to specify the beginning of the month in Postgres SQL syntax? Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 5+ messages in thread
From: sqllist @ 2003-12-10 05:31 UTC (permalink / raw)
To: pgsql-sql; +Cc: jeff@dnuk.com
Hi Jeff,
You can use
WHERE date < to_date( to_char(current_date,'yyyy-MM') || '-01','yyyy-mm-dd')
Thanx
Denis
----- Original Message -----
From: <jeff@dnuk.com>
To: <pgsql-sql@postgresql.org>
Sent: Sunday, December 07, 2003 9:46 PM
Subject: [SQL] How to specify the beginning of the month in Postgres SQL
syntax?
> Hello,
>
> I need to create a view in Postgres that has a where clause of the
> date < beginning of month.
>
> i.e.:
> SELECT supplier_number, Sum(amount) AS due
> FROM purchase_orders
> WHERE date < '2003-12-1' AND paid = 0
> GROUP BY supplier_number
> ORDER BY supplier_number ASC
>
>
> As you can see, I've specified the 1st of December this year as the
> where clause. What I want is an SQL statement that automatically
> generates the first of the month. How do I do this?
>
> Thanks.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to specify the beginning of the month in Postgres SQL syntax?
2003-12-07 16:16 How to specify the beginning of the month in Postgres SQL syntax? jeff@dnuk.com
2003-12-10 05:31 ` Re: How to specify the beginning of the month in Postgres SQL syntax? sqllist <sqllist@coralindia.com>
@ 2003-12-10 15:51 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2003-12-10 15:51 UTC (permalink / raw)
To: sqllist <sqllist@coralindia.com>; +Cc: pgsql-sql; jeff@dnuk.com
"sqllist" <sqllist@coralindia.com> writes:
> WHERE date < to_date( to_char(current_date,'yyyy-MM') || '-01','yyyy-mm-dd')
That seems like the hard way. Try this:
regression=# select date_trunc('month', now());
date_trunc
------------------------
2003-12-01 00:00:00-05
(1 row)
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-12-10 15:51 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-07 16:16 How to specify the beginning of the month in Postgres SQL syntax? jeff@dnuk.com
2003-12-07 19:38 ` CoL <col@mportal.hu>
2003-12-10 00:52 ` Paul Thomas <paul@tmsl.demon.co.uk>
2003-12-10 05:31 ` sqllist <sqllist@coralindia.com>
2003-12-10 15:51 ` Tom Lane <tgl@sss.pgh.pa.us>
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