Re: [GENERAL] Date for a week day of a month
generate_series that's a good one!
Thank you!
On 7/3/07, Emi Lu <[EMAIL PROTECTED]> wrote:
Can I know how to get the date of each month's last Thursday please?
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
you can easily do it without functions.
for example, this select:
SELECT
cast(d.date + i * '1 day'::interval as date)
FROM
(select '2007-04-01'::date as date) d,
generate_series(0, 30) i
WHERE
to_char(d.date, 'MM') = to_char( cast(d.date + i * '1 day'::interval as
date), 'MM')
AND to_char(cast(d.date + i * '1 day'::interval as date), 'D') = '5'
ORDER BY 1 DESC
LIMIT 1
;
does what you need.
to get last-thursday for another month, just change: (select
'2007-04-01'::date as date) d, to be 1st of any other month.
depesz
---(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
Re: [GENERAL] Date for a week day of a month
On 7/3/07, Emi Lu <[EMAIL PROTECTED]> wrote:
Can I know how to get the date of each month's last Thursday please?
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
you can easily do it without functions.
for example, this select:
SELECT
cast(d.date + i * '1 day'::interval as date)
FROM
(select '2007-04-01'::date as date) d,
generate_series(0, 30) i
WHERE
to_char(d.date, 'MM') = to_char( cast(d.date + i * '1 day'::interval as
date), 'MM')
AND to_char(cast(d.date + i * '1 day'::interval as date), 'D') = '5'
ORDER BY 1 DESC
LIMIT 1
;
does what you need.
to get last-thursday for another month, just change: (select
'2007-04-01'::date as date) d, to be 1st of any other month.
depesz
Re: [GENERAL] Date for a week day of a month
On Jul 3, 2007, at 14:54 , Emi Lu wrote:
result := (
(date_part('year', $1) || '-' || date_part('month',
$1) || '-01')::date
+ '1 month'::interval - '1 day'::interval
)::date;
I recommend not using string manipulation to handle data that is not
textual. There are a lot of date and time functions available. The
above can be rewritten in a couple of different ways:
result := (date_trunc('month', $1) + interval '1 month' - interval '1
day')::date;
result := (date_trunc('month', $1 + interval '1 month'))::date - 1;
For example:
SELECT current_date
, (date_trunc('month', current_date) + interval '1 month' -
interval '1 day')::date as all_intervals
, (date_trunc('month', current_date + interval '1 month'))::date
- 1 as date_arithmetic;
date| all_intervals | date_arithmetic
+---+-
2007-07-03 | 2007-07-31| 2007-07-31
(1 row)
Hope this helps.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Date for a week day of a month
Emi Lu wrote:
Hello,
Can I know how to get the date of each month's last Thursday please?
For example, something like
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
Thank you!
It turns out my original solution was slightly (badly) wrong and was
returning seemingly random numbers ;-) Here is a modified version that
seems to do the trick.
CREATE OR REPLACE FUNCTION lastday (date,int) RETURNS DATE AS '
DECLARE
last_day date;
last_dow int;
BEGIN
last_day := date_trunc(''month'', $1) + ''1 month''::interval - ''1
day''::interval;
last_dow := $2 - EXTRACT(dow FROM last_day)::int;
if (last_dow > 0) then
last_dow := last_dow - 7;
end if;
RETURN last_day + (''1 day''::interval * last_dow);
END;
' LANGUAGE plpgsql;
Use the same as the previous version, the second parameter is 0-6, where
0 is sunday. The first input is the date, and this time it doesnt have
to be the first day of the month.
=# select '2007-04-01', lastday('2007-04-01', 4);
?column? | lastday
+
2007-04-01 | 2007-04-26
jnb198_chuckie=# select '2007-04-10', lastday('2007-04-10', 4);
?column? | lastday
+
2007-04-10 | 2007-04-26
Nick
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Date for a week day of a month
Thank you all for your inputs!
Based on your inputs, made it a bit change to my application:
==
DROP FUNCTION test_db.lastWeekdayDate (date, varchar) ;
CREATE OR REPLACE FUNCTION test_db.lastWeekdayDate (date, varchar)
RETURNS DATE AS $$
DECLARE
result date;
BEGIN
result := (
(date_part('year', $1) || '-' || date_part('month', $1)
|| '-01')::date
+ '1 month'::interval - '1 day'::interval
)::date;
WHILE to_char(result, 'DY') <> $2 LOOP
result := result - '1 day'::interval ;
END LOOP;
RETURN result ;
END;
$$ language 'plpgsql';
select lastWeekdayDate('2007-07-03', 'THU');
lastweekdaydate
-
2007-07-26
(1 row)
Hello,
Can I know how to get the date of each month's last Thursday please?
For example, something like
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
Thank you!
CREATE OR REPLACE FUNCTION lastday (date,int) RETURNS DATE AS '
DECLARE
result date;
last_day date;
last_dow int;
BEGIN
last_day := $1 + ''1 month''::interval - ''1 day''::interval;
last_dow := EXTRACT(dow FROM last_day)::int - $2;
RETURN last_day + (''1 day''::interval * last_dow);
END;
' LANGUAGE plpgsql;
=# select lastday('2007-04-01', 5);
lastday
2007-04-26
(1 row)
The second parameter is the day of the week that you want, which has the
same spec as EXTRACT(dow FROM...). Values are from 0-6 where 0 is Sunday.
Enjoy!
Nick
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Date for a week day of a month
On Jul 3, 2007, at 13:27 , Emi Lu wrote:
Can I know how to get the date of each month's last Thursday please?
For example, something like
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
Using a function for calculating the first occurrence of a particular
day of the week in a month from a previous post[1], this should work.
Natural language processing left as an exercise to the reader :)
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;
$_$;
COMMENT ON FUNCTION first_dow_of_month(DATE, INTEGER) IS
'first_dow_of_month(date, integer) returns the first occurrence of a
particular weekday in '
'a given month. The first argument supplies the month (as a date),
and the second '
'argument is the day of the week index as returned by extract(''dow'')';
CREATE OR REPLACE FUNCTION nth_dow_of_month (
DATE -- date in target month
, INTEGER -- day of week index
, INTEGER -- zero-based ordinal day of week index,
-- e.g., 0 is first, 1 is second, -1 is last.
)
RETURNS DATE
IMMUTABLE
STRICT
LANGUAGE sql AS $_$
SELECT CASE
WHEN $3 >= 0 THEN
first_dow_of_month($1, $2) + $3 * 7
ELSE
first_dow_of_month(($1 + interval '1 month')::date, $2)
+ $3 * 7
END;
$_$;
COMMENT ON FUNCTION nth_dow_of_month(DATE, INTEGER, INTEGER) IS
'nth_dow_of_month(date, integer, integer) returns the nth occurrence
of a particular '
'weekday in a given month. The first argument supplies the month (as
a date). '
'The second argument supplies the day of the week index as returned
by extract(''dow''). '
'The third argument supplies the zero-based index of the desired
occurrence, '
'e.g. 0 indicates the first occurrence and 1 indicates the second. A
negative index will '
' count from the end of the month, i.e., -1 is the last occurrence,
-2 is the second to last '
'occurrence. No bounds checking is done to ensure that the returned
date is within the '
'specified month.';
SELECT current_date
, nth_dow_of_month(current_date, 3, 0) as first_wed
, nth_dow_of_month(current_date, 3, 1) as second_wed
, nth_dow_of_month(current_date, 3, 2) as third_wed
, nth_dow_of_month(current_date, 3, -2) as second_to_last_wed
, nth_dow_of_month(current_date, 3, 4) as third_wed
, nth_dow_of_month(current_date, 3, -1) as last_wed
, nth_dow_of_month(current_date, 3, 8) as ninth_wed;
date| first_wed | second_wed | third_wed |
second_to_last_wed | third_wed | last_wed | ninth_wed
+++
++++
2007-07-03 | 2007-07-04 | 2007-07-11 | 2007-07-18 |
2007-07-18 | 2007-08-01 | 2007-07-25 | 2007-08-29
(1 row)
Hope this helps.
Michael Glaesemann
grzm seespotcode net
[1](http://archives.postgresql.org/pgsql-sql/2007-06/msg00017.php)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Date for a week day of a month
Emi Lu wrote:
Hello,
Can I know how to get the date of each month's last Thursday please?
For example, something like
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
Thank you!
CREATE OR REPLACE FUNCTION lastday (date,int) RETURNS DATE AS '
DECLARE
result date;
last_day date;
last_dow int;
BEGIN
last_day := $1 + ''1 month''::interval - ''1 day''::interval;
last_dow := EXTRACT(dow FROM last_day)::int - $2;
RETURN last_day + (''1 day''::interval * last_dow);
END;
' LANGUAGE plpgsql;
=# select lastday('2007-04-01', 5);
lastday
2007-04-26
(1 row)
The second parameter is the day of the week that you want, which has the
same spec as EXTRACT(dow FROM...). Values are from 0-6 where 0 is Sunday.
Enjoy!
Nick
---(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
Re: [GENERAL] Date for a week day of a month
am Tue, dem 03.07.2007, um 14:27:24 -0400 mailte Emi Lu folgendes:
> Hello,
>
> Can I know how to get the date of each month's last Thursday please?
>
> For example, something like
>
> Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
> Result: 2007-04-26
Write you own function. You can get the last date for a month like this:
select '2007-04-01'::date+'1month'::interval-'1day'::interval;
You can get the weekday with:
select extract('dow' from
'2007-04-01'::date+'1month'::interval-'1day'::interval);
Now you know the weekday. If this value = 4, its okay. If not, subtract
days until the date is a Thursday.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Date for a week day of a month
Hello,
Can I know how to get the date of each month's last Thursday please?
For example, something like
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
Thank you!
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
