Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: select data for all days of previous month

    Hi guys

    Was wondering the best way to specify to select data from a table for all the days of the previous month?

    Regards
    Shajju

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You neither provided any details on how your table is structured, nor did you provide any example that would give us a hint on how the "data" looks like.

    Your question basically says something like: "I have this thing which doesn't work, now can I fix this"

    Please read the links in my signature to learn how to post better question in the future

    Im risking a wild guess anyway. Something like this should do the job:
    Code:
    select * 
    from the_table
    where the_date_column >= trunc(trunc(sysdate, 'month') - 1)  
      and the_date_colum < trunc(sysdate, 'month')
    You will of course need to adjust this query to the actual table structure you are using.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thanks and sorry. I used the condition
    Code:
    where the_date_column >= trunc(trunc(sysdate, 'month') - 1)  
      and the_date_colum < trunc(sysdate, 'month')
    but this only gave me 31 03 2013.

    I was hoping to get all 31 days.

    The table contains busy hour values i.e., the busiest hour of the day.

    Code:
    E.g.,	DAY	                                COL2	        COL3 
     31/03/2013 03:00:00    	                1.017  	        75

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Again: show us some sample data and the expected output. As it stands I have no idea what you are after.

    How many rows does the table contain for march?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How about
    Code:
    where date_column between trunc(add_months(sysdate, -1), 'mm') 
                          and trunc(last_day(add_months(sysdate, -1)))

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    trunc(sysdate, 'month') returns April 1st, if used with < it will select everything in March (regardless of the time value in the referenced columns).

    Your expression trunc(last_day(add_months(sysdate, -1))) returns 2013-03-31 00:00:00, so even if you use <= in the comparison (to catch rows from March, 31st), you won't get rows that have e.g. 2013-03-31 23:15:00 which would be included with my expression

    Again: show us some sample data and the expected output. As it stands I have no idea what you are after.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Thanks guys.

    This is what I was looking for:

    Code:
    where datetime between trunc(add_months(sysdate, -1), 'mm') and trunc(sysdate, 'month')-1/3600
    Advice appreciated.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    where datetime between trunc(add_months(sysdate, -1), 'mm') and trunc(sysdate, 'month')-1/3600
    You should trunc(sysdate, 'month')-1/86400

    (One second = 1/86400, one minute = 1/3600)

    And now I see why my initial condition was wrong. I incorrectly assumed that trunc() would "trunc" to the beginning of the month, but it actually rounds...
    Last edited by shammat; 04-02-13 at 10:22.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •