Postgres Pro
Downloads
Home   >   mailing lists

Thread: Getting the week of a date

Getting the week of a date

From
"Kumar"
Date:
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
 

Re: Getting the week of a date

From
Robert Creager
Date:
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

Re: Getting the week of a date

From
Richard Huxton
Date:
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

Re: Getting the week of a date

From
sad
Date:
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)

Re: Getting the week of a date

From
sad
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)

Re: Getting the week of a date

From
"Kumar"
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.orgso that your     message can get through to the mailing list cleanly

Re: Getting the week of a date

From
"Kumar"
Date:
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