Date calculations

Atari · #1 (**permalink**) 03-03-08, 21:25

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.

r937 · #2 (**permalink**) 03-03-08, 21:54

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?

r937 · #3 (**permalink**) 03-03-08, 21:55

by the way, only mysql will allow the date for id=2, and only in earlier versions

xtremenw · #4 (**permalink**) 03-07-08, 04:19

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)

r937 · #5 (**permalink**) 03-07-08, 06:58

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

xtremenw · #6 (**permalink**) 03-07-08, 22:19

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.

r937 · #7 (**permalink**) 03-07-08, 22:25

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

xtremenw · #8 (**permalink**) 03-07-08, 22:38

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech