Обсуждение: current_date / datetime stuff

Hello,

I was hoping someone here may be able to help me out with this one:

Is there anything similiar to:     SELECT current_date;
that will return the date of the first Monday of the month?

Please let me know.

Thanks,
Joshua

On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote:
> that will return the date of the first Monday of the month?

I guess you need to write a function to do this.  I suppose you could
do it by finding out what day of the week it is and what the date is,
then counting backwards to the earliest possible Monday.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?    --attr. John Maynard Keynes

On Jun 4, 2007, at 10:27 , Andrew Sullivan wrote:

> On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote:
>> that will return the date of the first Monday of the month?
>
> I guess you need to write a function to do this.  I suppose you could
> do it by finding out what day of the week it is and what the date is,
> then counting backwards to the earliest possible Monday.

As Andrew said, there's no built-in function to do this, but it's  
easy enough to write one. Here's a rough example (very lightly tested  
and probably overly complicated)

CREATE OR REPLACE FUNCTION first_dow(DATE, INTEGER)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT v_first_day_of_month +       CASE WHEN v_day_of_week <= $2 THEN $2 - v_day_of_week            ELSE 8 -
v_day_of_week      END AS first_day_of_month
 
FROM (     SELECT v_first_day_of_month            , extract('dow' from v_first_day_of_month)::integer
        AS v_day_of_week     FROM (SELECT date_trunc('month', $1)::date)          AS mon(v_first_day_of_month)) as
calc;
$_$;

CREATE OR REPLACE FUNCTION first_monday(DATE)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT first_dow($1, 1);
$_$;

select first_monday(current_date);
first_monday
--------------
2007-06-04
(1 row)

select first_monday('2007-04-01');
first_monday
--------------
2007-04-02
(1 row)

Michael Glaesemann
grzm seespotcode net

On 6/4/07, Joshua <joshua@joshuaneil.com> wrote:
> Hello,
>
> I was hoping someone here may be able to help me out with this one:
>
> Is there anything similiar to:     SELECT current_date;
> that will return the date of the first Monday of the month?
>
> Please let me know.
>
> Thanks,
> Joshua

select ( select case i <= dow   when true  then d + (i - dow + 7)   when false then d + (i - dow) end from (   select d
 , extract(dow from d)::int as dow   , 1 as i -- monday   from (     select date_trunc('month',current_date)::date - 1
asd   ) q ) q2
 
) as first_monday_of_the_month

On Jun 4, 2007, at 10:59 , Michael Glaesemann wrote:

>
> On Jun 4, 2007, at 10:27 , Andrew Sullivan wrote:
>
>> On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote:
>>> that will return the date of the first Monday of the month?
>>
>> I guess you need to write a function to do this.  I suppose you could
>> do it by finding out what day of the week it is and what the date is,
>> then counting backwards to the earliest possible Monday.
>
> As Andrew said, there's no built-in function to do this, but it's  
> easy enough to write one. Here's a rough example (very lightly  
> tested and probably overly complicated)

And a little simpler:

CREATE OR REPLACE FUNCTION first_dow_of_month(DATE, INTEGER)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT v_first_day_of_month + ( 7 + $2 - v_day_of_week) % 7       AS first_dow_of_month
FROM (     SELECT v_first_day_of_month            , extract('dow' from v_first_day_of_month)::integer
        AS v_day_of_week     FROM (SELECT date_trunc('month', $1)::date)          AS mon(v_first_day_of_month)) as
calc;
$_$;

CREATE OR REPLACE FUNCTION first_monday(DATE)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT first_dow_of_month($1, 1);
$_$;

Michael Glaesemann
grzm seespotcode net

oneliner:

select date_trunc('month',now()) + ((8 - extract('dow' from date_trunc 
('month',now()))||'days')::text)::interval;

Kristo
On 04.06.2007, at 19:39, Michael Glaesemann wrote:

>
> On Jun 4, 2007, at 10:59 , Michael Glaesemann wrote:
>
>>
>> On Jun 4, 2007, at 10:27 , Andrew Sullivan wrote:
>>
>>> On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote:
>>>> that will return the date of the first Monday of the month?
>>>
>>> I guess you need to write a function to do this.  I suppose you  
>>> could
>>> do it by finding out what day of the week it is and what the date  
>>> is,
>>> then counting backwards to the earliest possible Monday.
>>
>> As Andrew said, there's no built-in function to do this, but it's  
>> easy enough to write one. Here's a rough example (very lightly  
>> tested and probably overly complicated)
>
> And a little simpler:
>
> CREATE OR REPLACE FUNCTION first_dow_of_month(DATE, INTEGER)
> RETURNS DATE
> IMMUTABLE
> LANGUAGE SQL AS $_$
> SELECT v_first_day_of_month + ( 7 + $2 - v_day_of_week) % 7
>        AS first_dow_of_month
> FROM (
>      SELECT v_first_day_of_month
>             , extract('dow' from v_first_day_of_month)::integer
>                             AS v_day_of_week
>      FROM (SELECT date_trunc('month', $1)::date)
>           AS mon(v_first_day_of_month)) as calc;
> $_$;
>
> CREATE OR REPLACE FUNCTION first_monday(DATE)
> RETURNS DATE
> IMMUTABLE
> LANGUAGE SQL AS $_$
> SELECT first_dow_of_month($1, 1);
> $_$;
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq

We should have a onliner contest. I love oneliners!!!

> oneliner:
>
> select date_trunc('month',now()) + ((8 - extract('dow' from date_trunc 
> ('month',now()))||'days')::text)::interval;
>
> Kristo
> On 04.06.2007, at 19:39, Michael Glaesemann wrote:
>

On 6/5/07, Gerardo Herzig <gherzig@fmed.uba.ar> wrote:
> We should have a onliner contest. I love oneliners!!!

+1 on that

Kristo Kaiv escreveu:
> oneliner:
> 
> select date_trunc('month',now()) + ((8 - extract('dow' from 
> date_trunc('month',now()))||'days')::text)::interval;
> 



There is a problem when first monday is 1st or 2nd day of month.

bdteste=# SELECT date_trunc('month',meses) + ((8 - extract('dow' from 
date_trunc ('month',meses))||'days')::text)::interval FROM (SELECT 
('2007-' || s.a || '-15')::timestamp as meses FROM generate_series(1,12) 
as s(a)) AS foo;      ?column?
--------------------- 2007-01-08 00:00:00 2007-02-05 00:00:00 2007-03-05 00:00:00 2007-04-09 00:00:00 2007-05-07
00:00:002007-06-04 00:00:00 2007-07-09 00:00:00 2007-08-06 00:00:00 2007-09-03 00:00:00 2007-10-08 00:00:00 2007-11-05
00:00:002007-12-03 00:00:00
 
(12 registros)

Testing this condition we have the correct answer:
bdteste=# SELECT date_trunc('month',foo) + ((CASE WHEN extract('dow' 
from date_trunc('month',foo)) > 1 THEN 8 ELSE 1 END) - extract('dow' 
from date_trunc ('month',foo)))*'1 day'::interval FROM (SELECT ('2007-' 
|| s.a || '-15')::timestamp as foo FROM generate_series(1,12) as s(a)) 
AS bar;      ?column?
--------------------- 2007-01-01 00:00:00 2007-02-05 00:00:00 2007-03-05 00:00:00 2007-04-02 00:00:00 2007-05-07
00:00:002007-06-04 00:00:00 2007-07-02 00:00:00 2007-08-06 00:00:00 2007-09-03 00:00:00 2007-10-01 00:00:00 2007-11-05
00:00:002007-12-03 00:00:00
 
(12 registros)

[]s
Osvaldo