Mysql date interval

Question

I am trying to select rows which are within a certain time period:

$sql = mysql_query(" SELECT * FROM table WHERE ... AND 
       date BETWEEN ('$date' - Interval 2 YEAR) AND ('$date' + Interval 2 YEAR)
       OR date ISNULL");

The format of the dates saved in this column are mm/dd/yyyy

a. how do I select the time interval? currently it is selecting all the rows, not just those within this date range.

b. Am I using the corret syntax to make sure we include all null options as well?

Update.

Following the answers and comments I changed the column type to "Date" (it was varchar), and have also changed the current type of the existing entries to the correct date format.

I now have 2 questions: a. This is still not working... What else might be the problem? b. I want to change in PHP where the entry is saved. How ahould I go about this? Is the following correct:

 $new_date = mysql_query("STR_TO_DATE($old_date_string,'%m/%d/%Y')");

Solution.

First, I changed the column type to DATE (from VARCHAR).

Second, I moved and converted all the existing entries from their existing column and type (str) to new type (date) and new column.

Third, I added parentheses to the "date" section.

Fourth, I changed ISNULL to IS NULL - addin a space, which changes it from a function to a statement.

Fifth, I am now updating my script so that in the future dates will be saved to the new column in the new type, converting them to the correct fprmat before saving them to the database.

Thankyou @nnicholas and everybody.

Solution 1

If you are really storing your dates as strings I suggest you update your table with something like the following -

-- add a new column of type DATE
ALTER TABLE `table` ADD COLUMN `date_new` DATE AFTER `date`;
-- populate the new column from the old one
UPDATE `table` SET `date_new` = STR_TO_DATE(`date`,'%m/%d/%Y');
-- drop the old column
ALTER TABLE `table` DROP COLUMN `date`;
-- rename the new column
ALTER TABLE `table` CHANGE `date_new` `date` DATE;

After making these changes handling dates will be much easier for you. When inserting the dates that you are receiving you simply use STR_TO_DATE('new_value','%m/%d/%Y') to convert the date during the insert -

mysql_query("INSERT INTO `table` (field1, field2, `date`) 
               VALUES('value1', 'value2', STR_TO_DATE('$old_date_string','%m/%d/%Y')");

This assumes that $old_date_string has already been sanitised.

With your table structure updated the standard date arithmetic functions will work as intended.

$sql = mysql_query("SELECT *
                    FROM table
                    WHERE ...
                    AND (`date` BETWEEN ('$date' - INTERVAL 2 YEAR) AND ('$date' + INTERVAL 2 YEAR) OR `date` IS NULL)");

$date must be in Y-m-d format.

Solution 2

You can't do date comparisons on it if it's not stored in the database as a date.

You could do something very ugly casting it to a date with STR_TO_DATE() and then comparing but I'd suggest reworking your table structure to store this date as a DATE object.

SELECT * FROM table WHERE ... AND 
    STR_TO_DATE(date, '%m/%d/%Y') BETWEEN ('$date' - Interval 2 YEAR) AND ('$date' + Interval 2 YEAR)
    OR date ISNULL

This assumes that $date is in the correct MySQL DATE format of 2012-03-14 (for 14th March 2012)

Solution 3

You have to CAST/CONVERT your date field that you are getting from MySQL (you really called a date field 'date'? that's a bad idea).

The best answer is to convert your date column to an actual DATE type. You should also not really use the mm/dd/yyyy format as its ambiguous.

Solution 4

You need to enclose the entire date checking part in brackets, like so:

$sql = mysql_query(" SELECT * FROM table WHERE ... AND 
       (
       date BETWEEN ('$date' - Interval 2 YEAR) AND ('$date' + Interval 2 YEAR)
       OR date ISNULL
       )");

And it should work as expected.

Apart from the missing brackets, your condition seems to take into account NULLs all right (i.e. NULLs would be included in the output regardless of the argument passed).

UPDATE

It seems I was mistaken in presuming that the date column was the date type. @James C has got the point, as well as @nnichols, and their suggestion about changing the column type seems the way to go in your situation.

View more solutions

Comments

[Lucy Weatherford]

I added that, but it is still giving me all the results.. :(

[nnichols]

This would work but the cost a converting the string to a date for every row whenever you need to do any date comparison would be quite high.

[James C]

I agree, it's horribly inefficient. That's why I suggested reworking the column to a DATE type :)

[Andriy M]

@LucyWeatherford: James C's suggestion seems to be the way to go for you. Presently, you'll need to replace all occurrences of date in your query with something like STR_TO_DATE(date, '%m/%d/%Y'), but in the future that same operation should be done at the stage of receiving/importing the data into this table, and the date column should be the date type, of course.

[James C]

I don't think either would work as even as a string mm/dd/yyyy isn't something MySQL can parse. You'd have to use something like STR_TO_DATE() to parse the strange format and return a DATE object

[Andriy M]

The brackets around the OR part would still be needed.

[Lucy Weatherford]

Okay, I made those changes, but still, I'm gettin too many results for my query... :(

[nnichols]

Now things are in a better place to help. What exactly do you want to return?

[nnichols]

Are you wanting all records where the date is either NULL or BETWEEN (input_date - 2 years) AND (input_date + 2 years)?

[Lucy Weatherford]

yes, meaning I want everything between these 4 years, if I know the date's value. But I want to ADD also the entries where the date is unknown.

[nnichols]

I have added the query above. Maybe you were still missing the parentheses as pointed out by @AndriyM in his answer earlier.

[nnichols]

let us continue this discussion in chat

[Lucy Weatherford]

@Ben these are not my actual names, I don't call a table "table" either... Thanks for the advice thouh, I am converting the date column to an actual date type.