Re: [SQL] current_date / datetime stuff
true, didn't test it that thoroughly: mod 7 should be bit more beautiful
select date_trunc('month',now()) + ( ((8 - extract('dow' from
date_trunc('month',now()))%7) ||'days')::text)::interval;
On 06.06.2007, at 18:54, Osvaldo Rosario Kussama wrote:
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:00
2007-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:00
2007-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:00
2007-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:00
2007-12-03 00:00:00
(12 registros)
[]s
Osvaldo
Re: [SQL] current_date / datetime stuff
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:00
2007-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:00
2007-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:00
2007-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:00
2007-12-03 00:00:00
(12 registros)
[]s
Osvaldo
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] current_date / datetime stuff
On 6/5/07, Gerardo Herzig <[EMAIL PROTECTED]> wrote: We should have a onliner contest. I love oneliners!!! +1 on that ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] current_date / datetime stuff
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:
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] current_date / datetime stuff
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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] current_date / datetime stuff
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
Re: [SQL] current_date / datetime stuff
On 6/4/07, Joshua <[EMAIL PROTECTED]> 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 as d
) q
) q2
) as first_monday_of_the_month
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] current_date / datetime stuff
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
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] current_date / datetime stuff
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 | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
