pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedstart and end of the week
5+ messages / 4 participants
[nested] [flat]
* start and end of the week
@ 2002-09-26 10:56 John Sebastian N. Mayordomo <quadratini@sni.ph>
2002-09-26 13:26 ` Re: start and end of the week Bruno Wolff III <bruno@wolff.to>
2002-09-26 15:55 ` Re: start and end of the week Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 2 replies; 5+ messages in thread
From: John "Sebastian N. Mayordomo" @ 2002-09-26 10:56 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: start and end of the week
2002-09-26 10:56 start and end of the week John Sebastian N. Mayordomo <quadratini@sni.ph>
@ 2002-09-26 13:26 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2002-09-26 13:26 UTC (permalink / raw)
To: John Sebastian N. Mayordomo <quadratini@sni.ph>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: start and end of the week
2002-09-26 10:56 start and end of the week John Sebastian N. Mayordomo <quadratini@sni.ph>
@ 2002-09-26 15:55 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-09-26 16:20 ` Re: start and end of the week Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 5+ messages in thread
From: Jean-Luc Lachance @ 2002-09-26 15:55 UTC (permalink / raw)
To: John Sebastian N. Mayordomo <quadratini@sni.ph>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: start and end of the week
2002-09-26 10:56 start and end of the week John Sebastian N. Mayordomo <quadratini@sni.ph>
2002-09-26 15:55 ` Re: start and end of the week Jean-Luc Lachance <jllachan@nsd.ca>
@ 2002-09-26 16:20 ` Bruno Wolff III <bruno@wolff.to>
2002-09-26 16:29 ` Re: start and end of the week Dmitry Tkach <dmitry@openratings.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2002-09-26 16:20 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: John Sebastian N. Mayordomo <quadratini@sni.ph>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: start and end of the week
2002-09-26 10:56 start and end of the week John Sebastian N. Mayordomo <quadratini@sni.ph>
2002-09-26 15:55 ` Re: start and end of the week Jean-Luc Lachance <jllachan@nsd.ca>
2002-09-26 16:20 ` Re: start and end of the week Bruno Wolff III <bruno@wolff.to>
@ 2002-09-26 16:29 ` Dmitry Tkach <dmitry@openratings.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dmitry Tkach @ 2002-09-26 16:29 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-09-26 16:29 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-26 10:56 start and end of the week John Sebastian N. Mayordomo <quadratini@sni.ph>
2002-09-26 13:26 ` Bruno Wolff III <bruno@wolff.to>
2002-09-26 15:55 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-09-26 16:20 ` Bruno Wolff III <bruno@wolff.to>
2002-09-26 16:29 ` Dmitry Tkach <dmitry@openratings.com>
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