Thread: 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

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

In the following examples, replace <month> and <year> with the values
that you want.

Try this:

SELECT ( 1 -
    extract(dow from ('1/<month>/<year>')::date)::integer
     + 8
    ) % 7

This will give you a number from 1 to 7 indicating the day of the month
which is the first Monday from the month of the specified date. The '1'
represents Monday. Replace accordingly if you want a different day.

If you want the full date, you will need to concatenate the day and
month:

SELECT ( 1 -
    extract(dow from ('1/<month>/<year>')::date)::integer
     + 8
    ) % 7
    || '/' || '<month>' || '/' || '<year>';

And finally, if you want an answer for the 'current' month, then try
this monster:

SELECT ( 1 - firstday::integer + 8 ) % 7  || '/' || month || '/' || year
FROM (
    SELECT extract(dow from ('1/'||month||'/'||year)::date) AS firstday,
        month,year
    FROM (
        SELECT extract(month from current_date) AS month,
        extract(year from current_date) AS year
    ) AS date_parts
) AS date_parts;


NOTE: Americans may wish to swap the order of day/month. Or not :)

Netzach

Joshua 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?

You might try the following query, or a variation thereof (replacing the
"now()"s in the subquery with an arbitrary date if you need the first
monday of months other than the current one):

select cast(case
     when d.dow > 1 then d.m + (8 - d.dow) * interval '1 day'
     when d.dow < 1 then d.m + 1 * interval '1 day'
     else d.m end as date) as first_monday
from (select extract(dow from date_trunc('month', now())) as dow,
     date_trunc('month', now()) as m) d;

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/

On Jun 4, 2007, at 11:23 , Netzach wrote:

> SELECT ( 1 -
>     extract(dow from ('1/<month>/<year>')::date)::integer
>      + 8
>     ) % 7

> SELECT ( 1 -
>     extract(dow from ('1/<month>/<year>')::date)::integer
>      + 8
>     ) % 7
>     || '/' || '<month>' || '/' || '<year>';

> SELECT ( 1 - firstday::integer + 8 ) % 7  || '/' || month || '/' ||
> year
> FROM (
>     SELECT extract(dow from ('1/'||month||'/'||year)::date) AS firstday,
>         month,year
>     FROM (
>         SELECT extract(month from current_date) AS month,
>         extract(year from current_date) AS year
>     ) AS date_parts
> ) AS date_parts;

> NOTE: Americans may wish to swap the order of day/month. Or not :)
>

PostgreSQL has a large number of useful date manipulation functions
so you can avoid doing this kind of error-prone string work.

http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html

Michael Glaesemann
grzm seespotcode net