Postgres Pro
Downloads
Home   >   mailing lists

Thread: grabbing date of last Sunday?

grabbing date of last Sunday?

From
"blackwater dev"
Date:
How can I grab the date from the last Sunday based on when I run the query?

For example I run it today, and I need to date of 10-5-08, if I ran it next week, I would want 10-12-08, etc.


Thanks!

Re: grabbing date of last Sunday?

From
Raymond O'Donnell
Date:
On 10/10/2008 16:29, blackwater dev wrote:
> How can I grab the date from the last Sunday based on when I run the query?

select
  current_date
    - (extract(dow from current_date) || ' days')::interval;

:-)

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Re: grabbing date of last Sunday?

From
Michael Glaesemann
Date:
On Oct 10, 2008, at 11:36 , Raymond O'Donnell wrote:

> On 10/10/2008 16:29, blackwater dev wrote:
>> How can I grab the date from the last Sunday based on when I run
>> the query?
>
> select
>  current_date
>    - (extract(dow from current_date) || ' days')::interval;

Concatenations in math always make me shudder (and the above will give
you a timestamp besides):

SELECT CURRENT_DATE,
        CURRENT_DATE - CAST(EXTRACT(DOW FROM CURRENT_DATE) as int) AS
date_integer_arithmetic,
        CAST(CURRENT_DATE - (EXTRACT(DOW FROM CURRENT_DATE) * INTERVAL
'1 DAY') AS DATE) AS date_interval_arithmetic,
        CAST(date_trunc('week', CURRENT_DATE) AS DATE) - 1 AS
non_standard;
     date    | date_integer_arithmetic | date_interval_arithmetic |
non_standard
------------+-------------------------+--------------------------
+--------------
  2008-10-10 | 2008-10-05              | 2008-10-05               |
2008-10-05
(1 row)

Michael Glaesemann
grzm seespotcode net