Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    35

    Question Unanswered: Date calculations

    Hi guys,

    I know how to do this in a long-winded way, but was hopeful for some tricks from old pros.

    Consider a table with { id, date }; these are auto-increment integer and DATETIME columns respectively.

    How do I generate a query that returns all 'id's associated to date elements that have values not in this month, but in the previous X months?

    Ex, data:

    1, 2008-02-01
    2, 2008-02-30
    3, 2008-03-03
    4, 2007-11-01

    If I want the query for the previous two months, it should return ids:
    1,2

    DATE_SUB( INTERVAL, X MONTH ) won't do the trick (alone), because we don't want ranges in two months previous to today, but instead, all entries contained in Jan, Feb, (since we're now in March).

    Thanks as always.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first calculation: find the first day of the current month

    second calculation: subtract two months

    then in the WHERE clause, choose all dates which are greater than or equal to the second date, and less than the first

    simple, yes?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    by the way, only mysql will allow the date for id=2, and only in earlier versions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2008
    Location
    Tacoma, WA
    Posts
    11
    If you are still looking for more details, hope this will help.

    Code:
    select * from {table}
    where date between 
      date_sub(
        date_sub(current_date, interval day(current_date)-1 day), 
            interval 2 month)
      and date_sub(
        date_sub(current_date, 
          interval day(current_date)-1 day), 
          interval 1 month)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it helps, xtremenw, but it's slightly off in two ways

    let's use march as an example

    firstly, the bounds you have created are the first day of january and the first day of february

    this is not the two month span that was originally asked for, but your formula can be changed as required

    the second problem is that you include the upper bound, because you're using BETWEEN, and that's not right

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2008
    Location
    Tacoma, WA
    Posts
    11
    Quote Originally Posted by r937
    it helps, xtremenw, but it's slightly off in two ways

    let's use march as an example

    firstly, the bounds you have created are the first day of january and the first day of february

    this is not the two month span that was originally asked for, but your formula can be changed as required

    the second problem is that you include the upper bound, because you're using BETWEEN, and that's not right

    Code:
    select * from {table}
    where date_stamp between
      date_sub(
        date_sub(current_date, interval day(current_date)-1 day),
            interval 3 month)
      and date_sub(
        date_sub(current_date,
          interval day(current_date)-1 day),
          interval 1 month) - interval 1 day
    Good call! It was late and I really did not test it, I just got it to work.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    now you've changed it so that, assuming you run it this month (march), it would return rows from december 1 to midnight january 31st

    not exactly the previous two months (which would be january and february)

    also, note that all datetimes for the remainder of january 31st after that tiny little second at exactly midnight would not be included

    you simply must stop using BETWEEN and start thinking of greater than or equal to for the lower bound, but strictly less than for the upper bound

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2008
    Location
    Tacoma, WA
    Posts
    11


    I figured out what I was doing. I was trying things out against a date type and not datetime. He should get the general idea and be able to take it from there.
    Last edited by xtremenw; 03-07-08 at 23:43.

Posting Permissions

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