pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Is 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>
  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-25 14:28  Dmitry Tkach <dmitry@openratings.com>
  parent: David Stanaway <david@stanaway.net>
  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-25 14:39  Dmitry Tkach <dmitry@openratings.com>
  parent: David Stanaway <david@stanaway.net>
  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