pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIs there a better way than this to get the start and end of a month?
3+ messages / 2 participants
[nested] [flat]
* Is there a better way than this to get the start and end of a month?
@ 2002-09-22 05:32 David Stanaway <david@stanaway.net>
2002-09-25 14:28 ` Re: Is there a better way than this to get the start and end of a month? Dmitry Tkach <dmitry@openratings.com>
2002-09-25 14:39 ` Re: Is there a better way than this to get the start and end of a month? Dmitry Tkach <dmitry@openratings.com>
0 siblings, 2 replies; 3+ messages in thread
From: David Stanaway @ 2002-09-22 05:32 UTC (permalink / raw)
To: pgsql-sql
Here are the 2 functions I have at the moment. I was wondering if
someone had a better way?
CREATE OR REPLACE FUNCTION month_start (date)
RETURNS date
AS '
DECLARE
day ALIAS FOR $1;
BEGIN
RETURN day - (extract(''day'' FROM day)||'' days'')::interval +
''1 day''::interval;
END;
'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION month_end (date)
RETURNS date
AS '
DECLARE
day ALIAS FOR $1;
month int;
year int;
BEGIN
month := extract(''month'' FROM day);
year := extract(''year'' FROM day);
IF month = 12 THEN
month := 1;
year := year +1;
ELSE
month := month +1;
END IF;
RETURN (''01-''||month||''-''||year)::date -
''1 day''::interval;
END;
'
LANGUAGE 'plpgsql';
--
David Stanaway
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Is there a better way than this to get the start and end of a month?
2002-09-22 05:32 Is there a better way than this to get the start and end of a month? David Stanaway <david@stanaway.net>
@ 2002-09-25 14:28 ` Dmitry Tkach <dmitry@openratings.com>
1 sibling, 0 replies; 3+ messages in thread
From: Dmitry Tkach @ 2002-09-25 14:28 UTC (permalink / raw)
To: David Stanaway <david@stanaway.net>
Well, month_end could be more straightforward as something like
select month_start ($1) + interval '1 day' - interval '1 month';
Dima
David Stanaway wrote:
> Here are the 2 functions I have at the moment. I was wondering if
> someone had a better way?
>
> CREATE OR REPLACE FUNCTION month_start (date)
> RETURNS date
> AS '
> DECLARE
> day ALIAS FOR $1;
> BEGIN
> RETURN day - (extract(''day'' FROM day)||'' days'')::interval +
> ''1 day''::interval;
> END;
> '
> LANGUAGE 'plpgsql';
>
> CREATE OR REPLACE FUNCTION month_end (date)
> RETURNS date
> AS '
> DECLARE
> day ALIAS FOR $1;
> month int;
> year int;
> BEGIN
> month := extract(''month'' FROM day);
> year := extract(''year'' FROM day);
> IF month = 12 THEN
> month := 1;
> year := year +1;
> ELSE
> month := month +1;
> END IF;
> RETURN (''01-''||month||''-''||year)::date -
> ''1 day''::interval;
> END;
> '
> LANGUAGE 'plpgsql';
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Is there a better way than this to get the start and end of a month?
2002-09-22 05:32 Is there a better way than this to get the start and end of a month? David Stanaway <david@stanaway.net>
@ 2002-09-25 14:39 ` Dmitry Tkach <dmitry@openratings.com>
1 sibling, 0 replies; 3+ messages in thread
From: Dmitry Tkach @ 2002-09-25 14:39 UTC (permalink / raw)
To: David Stanaway <david@stanaway.net>
Sorry, the previous message was wrong...
This is better:
create function month_start (date) as
'select date_trunc ('month', $1)::date;'
language 'sql';
create function month_end (date) as
'select month_start ($1) - 1 + interval '1 month';
language 'sql';
I hope, it helps...
Dima
David Stanaway wrote:
> Here are the 2 functions I have at the moment. I was wondering if
> someone had a better way?
>
> CREATE OR REPLACE FUNCTION month_start (date)
> RETURNS date
> AS '
> DECLARE
> day ALIAS FOR $1;
> BEGIN
> RETURN day - (extract(''day'' FROM day)||'' days'')::interval +
> ''1 day''::interval;
> END;
> '
> LANGUAGE 'plpgsql';
>
> CREATE OR REPLACE FUNCTION month_end (date)
> RETURNS date
> AS '
> DECLARE
> day ALIAS FOR $1;
> month int;
> year int;
> BEGIN
> month := extract(''month'' FROM day);
> year := extract(''year'' FROM day);
> IF month = 12 THEN
> month := 1;
> year := year +1;
> ELSE
> month := month +1;
> END IF;
> RETURN (''01-''||month||''-''||year)::date -
> ''1 day''::interval;
> END;
> '
> LANGUAGE 'plpgsql';
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-09-25 14:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-22 05:32 Is there a better way than this to get the start and end of a month? David Stanaway <david@stanaway.net>
2002-09-25 14:28 ` Dmitry Tkach <dmitry@openratings.com>
2002-09-25 14:39 ` Dmitry Tkach <dmitry@openratings.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