pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting the week of a date
7+ messages / 4 participants
[nested] [flat]
* Getting the week of a date
@ 2004-02-16 12:10 Kumar <sgnerd@yahoo.com.sg>
0 siblings, 3 replies; 7+ messages in thread
From: Kumar @ 2004-02-16 12:10 UTC (permalink / raw)
To: pgsql-sql
Dear Friends,
Postgres 7.3.4 on RH Linux7.2.
While this works for month and why not for week
test=# select date_trunc('month',current_date + ('5 month')::INTERVAL);
date_trunc
---------------------
2004-07-01 00:00:00
(1 row)
test=# select date_trunc('week',current_date + ('5 month')::INTERVAL);
ERROR: TIMESTAMP units 'week' not supported
test=#
Any idea on how to find the 3 rd Wednesday of any given month.
Thanks
Kumar
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Getting the week of a date
@ 2004-02-16 12:43 Robert Creager <Robert_Creager@LogicalChaos.org>
parent: Kumar <sgnerd@yahoo.com.sg>
2 siblings, 1 reply; 7+ messages in thread
From: Robert Creager @ 2004-02-16 12:43 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
--Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG
Content-Type: text/plain; charset=US-ASCII
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
When grilled further on (Mon, 16 Feb 2004 17:40:08 +0530),
"Kumar" <sgnerd@yahoo.com.sg> confessed:
> Dear Friends,
>
> Postgres 7.3.4 on RH Linux7.2.
>
> While this works for month and why not for week
>
date_trunc (obviously) doesn't support week. I ran into this a while ago, and
came up with this function. I left the function signature the same as
date_trunc, even though I don't use the first argument. I did only minor
testing (10 years or so), so no guarantee about it's correctness. And it's kind
of slow...
CREATE OR REPLACE FUNCTION date_trunc_week( text, timestamp )
RETURNS timestamp AS '
DECLARE
reading_time ALIAS FOR $2;
year timestamp;
dow integer;
adjust text;
week text;
BEGIN
year := date_trunc( ''year''::text, reading_time );
week := date_part( ''week'', reading_time ) - 1 || '' week'';
dow := date_part( ''dow'', year );
-- If the dow is less than Thursday, then the start week is last year
IF dow <= 4 THEN
adjust := 1 - dow || '' day'';
ELSE
adjust := 8 - dow || '' day'';
END IF;
RETURN year + adjust::interval + week::interval;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;
--
05:37:49 up 1 day, 13:20, 2 users, load average: 0.09, 0.36, 0.63
Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003
--Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG
Content-Type: application/pgp-signature
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)
iEYEARECAAYFAkAwuvwACgkQLQ/DKuwDYzm2xACdGIajt5kT/heTMhmiyIqa63Gb
9isAn2WgpI2A2GLiOaLFLbyjjZMmy6JS
=Hhtd
-----END PGP SIGNATURE-----
--Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG--
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Getting the week of a date
@ 2004-02-16 13:03 Richard Huxton <dev@archonet.com>
parent: Kumar <sgnerd@yahoo.com.sg>
2 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2004-02-16 13:03 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; pgsql-sql
On Monday 16 February 2004 12:10, Kumar wrote:
>
> test=# select date_trunc('week',current_date + ('5 month')::INTERVAL);
> ERROR: TIMESTAMP units 'week' not supported
Try EXTRACT(week FROM ...)
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Getting the week of a date
@ 2004-02-16 13:08 sad <sad@bankir.ru>
parent: Kumar <sgnerd@yahoo.com.sg>
2 siblings, 0 replies; 7+ messages in thread
From: sad @ 2004-02-16 13:08 UTC (permalink / raw)
To: pgsql-sql
On Monday 16 February 2004 15:10, you wrote:
>
> Any idea on how to find the 3 rd Wednesday of any given month.
SELECT
1-(to_char(date_trunc('month', now()::timestamp),'D'))::INT2 + 7*3-3
replace now with any date and you'll the the day number of a third Wed in that
month.
7 is a constant (factor 3 is a desired week number)
-3 is number of days to step back from sunday to a desired day of week
(-3 stands for Wed)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Getting the week of a date
@ 2004-02-16 13:23 sad <sad@bankir.ru>
parent: Robert Creager <Robert_Creager@LogicalChaos.org>
0 siblings, 1 reply; 7+ messages in thread
From: sad @ 2004-02-16 13:23 UTC (permalink / raw)
To: pgsql-sql
EXCUSE ME, GUYS !
i forgot to add one monome:
7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4)
which is stands for skip a first week of month in case it is not consist Wed
finally the select will be similar the following
SELECT 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4) + 1 -
(to_char(date_trunc('month',now()),'D'))::INT2 + 7*3-3 ;
4 - is a number of Wed in a week (in postgresql numeration)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Getting the week of a date
@ 2004-02-17 05:10 Kumar <sgnerd@yahoo.com.sg>
parent: sad <sad@bankir.ru>
0 siblings, 1 reply; 7+ messages in thread
From: Kumar @ 2004-02-17 05:10 UTC (permalink / raw)
To: sad <sad@bankir.ru>; pgsql-sql
Seems a part of your function always returns '0'
select 1 - (to_char(date_trunc('month',now()),'D'))::INT2
because while we use date_trunc it will always return the first day of the
month and when it get subtracted by '1' it be always zero. Is there any
reason why you have included that?
Thanks
Kumar
----- Original Message -----
From: "sad" <sad@bankir.ru>
To: <pgsql-sql@postgresql.org>
Sent: Monday, February 16, 2004 6:53 PM
Subject: Re: [SQL] Getting the week of a date
EXCUSE ME, GUYS !
i forgot to add one monome:
7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4)
which is stands for skip a first week of month in case it is not consist Wed
finally the select will be similar the following
SELECT 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4) + 1 -
(to_char(date_trunc('month',now()),'D'))::INT2 + 7*3-3 ;
4 - is a number of Wed in a week (in postgresql numeration)
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Getting the week of a date
@ 2004-02-17 05:46 Kumar <sgnerd@yahoo.com.sg>
parent: Kumar <sgnerd@yahoo.com.sg>
0 siblings, 0 replies; 7+ messages in thread
From: Kumar @ 2004-02-17 05:46 UTC (permalink / raw)
To: sad <sad@bankir.ru>; pgsql-sql
I am sorry. I didn't read the doc properly. I understood why it is written
in that way.
Sorry for the trouble. Thanks. It worked fine for me.
Thanks
Kumar
----- Original Message -----
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "sad" <sad@bankir.ru>; <pgsql-sql@postgresql.org>
Sent: Tuesday, February 17, 2004 10:40 AM
Subject: Re: [SQL] Getting the week of a date
> Seems a part of your function always returns '0'
> select 1 - (to_char(date_trunc('month',now()),'D'))::INT2
>
> because while we use date_trunc it will always return the first day of the
> month and when it get subtracted by '1' it be always zero. Is there any
> reason why you have included that?
>
> Thanks
> Kumar
>
> ----- Original Message -----
> From: "sad" <sad@bankir.ru>
> To: <pgsql-sql@postgresql.org>
> Sent: Monday, February 16, 2004 6:53 PM
> Subject: Re: [SQL] Getting the week of a date
>
>
> EXCUSE ME, GUYS !
>
> i forgot to add one monome:
> 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4)
> which is stands for skip a first week of month in case it is not consist
Wed
>
> finally the select will be similar the following
>
> SELECT 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4) + 1 -
> (to_char(date_trunc('month',now()),'D'))::INT2 + 7*3-3 ;
>
> 4 - is a number of Wed in a week (in postgresql numeration)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-02-17 05:46 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-16 12:10 Getting the week of a date Kumar <sgnerd@yahoo.com.sg>
2004-02-16 12:43 ` Robert Creager <Robert_Creager@LogicalChaos.org>
2004-02-16 13:23 ` sad <sad@bankir.ru>
2004-02-17 05:10 ` Kumar <sgnerd@yahoo.com.sg>
2004-02-17 05:46 ` Kumar <sgnerd@yahoo.com.sg>
2004-02-16 13:03 ` Richard Huxton <dev@archonet.com>
2004-02-16 13:08 ` sad <sad@bankir.ru>
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