Postgres Pro
Downloads
Home   >   mailing lists

Thread: start and end of the week

start and end of the week

From
John "Sebastian N. Mayordomo"
Date:

How do I get the start and end date of the present week?
Is this possible?

For example this week
Start = Sept. 22 
End   = Sept. 28

Thank you very much.

Re: start and end of the week

From
Bruno Wolff III
Date:
On Thu, Sep 26, 2002 at 18:56:46 +0800, "John Sebastian N. Mayordomo" <quadratini@sni.ph> wrote:
> 
> 
> How do I get the start and end date of the present week?
> Is this possible?
> 
> For example this week
> Start = Sept. 22 
> End   = Sept. 28

The following advice will work on 7.3. For 7.2.2 and earlier it might
not work during a week with a timezone change depending at what time
of day you switch between DST and ST. To fix this you need to cast
current_date to a timestamp without timezone, and I haven't been
able to figure out how to do that and have to run off to a meeting now.

For the first day of the current week use something like:

area=> select current_date - extract(dow from current_date) *
area-> '1 day'::interval;     ?column?
---------------------2002-09-22 00:00:00
(1 row)

For the last day of the week use something like:

area=> select current_date + (6 - extract(dow from current_date)) *
area-> '1 day'::interval;     ?column?
---------------------2002-09-28 00:00:00
(1 row)

Re: start and end of the week

From
Jean-Luc Lachance
Date:
How about:

select now() - date_part( 'DOW', now()) as starts_on,  now() -date_part( 'DOW', now()) + 6 as ends_on;


"John Sebastian N. Mayordomo" wrote:
> 
> How do I get the start and end date of the present week?
> Is this possible?
> 
> For example this week
> Start = Sept. 22
> End   = Sept. 28
> 
> Thank you very much.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: start and end of the week

From
Bruno Wolff III
Date:
On Thu, Sep 26, 2002 at 11:55:48 -0400, Jean-Luc Lachance <jllachan@nsd.ca> wrote:
> How about:
> 
> select now() - date_part( 'DOW', now()) as starts_on, 
>   now() -date_part( 'DOW', now()) + 6 as ends_on;

That won't work in 7.3.

The following works in both 7.2 and 7.3:
area=> select current_date - extract(dow from current_date)::int as start_date,
area-> current_date - extract(dow from current_date)::int + 6 as end_date;start_date |  end_date
------------+------------2002-09-22 | 2002-09-28
(1 row)

Extract returns double precision and so needs a cast to int to work.

Re: start and end of the week

From
Dmitry Tkach
Date:
Does any one know what is the reason not to put this logic into date_trunc () function?
It seems to work with pretty much *any* unit imaginable, *except* for 'week'...

Dima

Bruno Wolff III wrote:
> On Thu, Sep 26, 2002 at 11:55:48 -0400,
>   Jean-Luc Lachance <jllachan@nsd.ca> wrote:
> 
>>How about:
>>
>>select now() - date_part( 'DOW', now()) as starts_on, 
>>  now() -date_part( 'DOW', now()) + 6 as ends_on;
>>
> 
> That won't work in 7.3.
> 
> The following works in both 7.2 and 7.3:
> area=> select current_date - extract(dow from current_date)::int as start_date,
> area-> current_date - extract(dow from current_date)::int + 6 as end_date;
>  start_date |  end_date
> ------------+------------
>  2002-09-22 | 2002-09-28
> (1 row)
> 
> Extract returns double precision and so needs a cast to int to work.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>