Thursday, October 4, 2012

Display all Sunday in the given date in postgresql






here is the query to display all the Sunday amid in given two date




SELECT 
    cast( mydate as date) as sunday
FROM
    generate_series(timestamp '2011-01-01', '2011-03-01', '1 day') AS g(mydate) 
    
WHERE
    EXTRACT(DOW FROM mydate) = 0;



DOW = 0 means SUNDAY.

output of the above query is


   sunday
--------
2-1-2011
9-1-2011
16-1-2011
23-1-2011
30-1-2011
6-2-2011
13-2-2011
20-2-2011
27-2-2011 


if you want to get display all the week day amid in given date

in where condition use  !=


SELECT 
   cast( mydate as date) as sunday.
FROM
    generate_series(timestamp '2011-01-01', '2011-03-01', '1 day') AS g(mydate) 
    
WHERE
    EXTRACT(DOW FROM mydate) != 0;

above query will return all the day of the week except sunday

-----------------------------------------------------------------------------------------------------------------------------------

alternative for cast in select clause we can also use

    EXTRACT (Day from mydate ) || '-' ||  EXTRACT (month from mydate ) || '-' || EXTRACT (year from mydate )