MySQL - Plus/Subtract days from a date based search

PHP
1

I have a form where a user can select a holiday “destination” and a “departure date”.

In addition the user can choose between 3 options.

  1. Search exact date
  2. +/- 3 days
  3. +/- 2 days
  4. +/- 1 days
    … etc

The “departure date” is being converted into Unix Timestamp.

What I’m wanting is to is add/subtract (x days) from the departure date so that it creates a date range to search in.

This is what I have so far.


; // ts_todays_date = timestamp of todays date
; //
SELECT DestinationName, UNIX_TIMESTAMP(DepartureDate) as uts_DepDate FROM `holiday_offers` WHERE
UNIX_TIMESTAMP(DepartureDate) = "$ts_todays_date" AND LCASE(`DestinationName`) ="$somelocation"
ORDER BY uts_DepDate DESC

At the moment I can do the exact date, but I am not sure how to go about making MySQL add or subtract days from this search, or create a date range based upon what the user selected.

Thanks

2

1 day is equal to 60 seconds * 60 minutes * 24 hours.

3

what types of values are being stored in the table? INTEGERs? DATEs? or DATETIMEs?

and if you are storing INTEGERs in the table, where do these values come from?

are they ~always~ set to midnight?

4

Use DATE_ADD/[url=http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-sub]DATE_SUB in the WHERE clause.

5

you could do that, but be careful, if you apply a function to a table column, this usually results in an index scan, or, worse, a table scan (i.e. very poor performance)

the best strategy is to use something like this –

WHERE datecol BETWEEN [I]somevalue1[/I] AND [I]somevalue2[/I]

or, if you are using datetime values (not date values), then this –

WHERE datetimecol >= [I]somevalue1[/I]
  AND datetimecol  < [I]somevalue2[/I]

in order to come up with somevalue1 and somevalue2, yes, you could use date functions here

for example, find all transactions for yesterday where the transactions have datetime values –

WHERE datetimecol >= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
  AND datetimecol  < CURRENT_DATE

:slight_smile:

6

Wow, lots of replies!

are they ~always~ set to midnight?

No. They are not … The csv field is text data and I import it as a DATE field (I guess I could as easily change that to a UNIX_TIMESTAMP).

I like the idea of using;


WHERE datecol BETWEEN somevalue1 AND somevalue2

Which of course somevalue1 and somevalue2 have to be generated beforehand.

I thought by using the DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
command I would be able to say 2 DAYS, or X days where X=amount entered.

I’m going to play around locally with all the answers and see what the best and most accurate result is.

Thanks a lot.

7

Hi.

I’m not sure if I’m doing this right.

Firstly, I have to keep referencing to my “formatted date” and also, it never returns anything, despite there being data for the date range.

I have not closed the date range because I want to see if it’ll return anything.


 SELECT * , FROM_UNIXTIME( DepartureDate, '%d-%m-%Y' ) AS ddFormatted
FROM `offers`
WHERE FROM_UNIXTIME( DepartureDate, '%d-%m-%Y' ) &gt;= DATE_SUB( "09-05-2009", INTERVAL -1
DAY )
ORDER BY `DepartureDate` ASC
LIMIT 0 , 30

In my LOAD INFILE I could use @, can I also use it here to stop me constantly referencing to FROM_UNIXTIME( DepartureDate, ‘%d-%m-%Y’ )?

8

since it appears that you have a choice, my strong advice is to use the DATETIME datatype for your table columns

if you do that, there will be far fewer difficulties

9

Oh okay.

Its strange, I was always told to use a timestamp or INT field as it was meant to be better, but I guess its not an issue if I can get this run.

Thanks

10

i will agree that some people think that the unix epoch integer is better (php programmers mainly, and please don’t shoot me, but that’s my impression)

it isn’t, DATETIME is better

:slight_smile:

11

OK.

I’ve changed the fields in the database to datetime, and the query;


SELECT *
FROM `offers`
WHERE
DepartureDate &gt;= DATE_SUB( "2009-05-09 00:00:00", INTERVAL 7
DAY )
AND DepartureDate &lt; DATE_SUB( "2009-05-09 00:00:00", INTERVAL -7
DAY )
ORDER BY `DepartureDate` ASC

This seems to be outputting something (I’ve tried it for +/- 30 days) but what I don’t understand is why the INTERVAL -7 DAY has to appear after the INTERVAL 7 DAY.

It just looks odd as I’m wanting it to be searching a period BEFORE/AFTER the date submitted.

I’m doing some tests to see if indeed the data is outputting correctly.

12

you have this –


WHERE DepartureDate >= DATE_SUB( "2009-05-09 00:00:00", INTERVAL 7 DAY )
  AND DepartureDate  < DATE_SUB( "2009-05-09 00:00:00", INTERVAL -7 DAY )

i would immediately rewrite this (i never actually code a midnight time) –


WHERE DepartureDate >= DATE_SUB('2009-05-09', INTERVAL 7 DAY )
  AND DepartureDate  < DATE_SUB('2009-05-09', INTERVAL -7 DAY )

then i would remove the double negative (subtracting -7 is the same adding 7) –


WHERE DepartureDate >= DATE_SUB('2009-05-09', INTERVAL 7 DAY )
  AND DepartureDate  < DATE_ADD('2009-05-09', INTERVAL 7 DAY )

and now the intent of this filter is a lot clearer: “after 7 days before the date, before 7 days after the date”

to generalize this so that it runs on any given date –


WHERE DepartureDate >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY )
  AND DepartureDate  < DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY )

and finally, simplify the syntax a little –


WHERE DepartureDate >= CURRENT_DATE - INTERVAL 7 DAY
  AND DepartureDate  < CURRENT_DATE + INTERVAL 7 DAY

:slight_smile:

13

Ah that’s very clear! Thanks a lot. That’s probably why I was getting erroneous data returned.

Shouldn’t CURRENT_DATE be the date the user inputted?

14

if that’s what the app needs, yes

make sure it reaches mysql in yyyy-mm-dd format

:slight_smile:

15

Okay. Many thanks.

It seems to be working fine now!

Just doing some more tests.

Thanks a lot.