mySql - Date_Sub using all variables

[redbrad0]

Is it possible for the Date_Sub to grab the variables all from the database? I seem to be getting errors with my query and just not sure if its even possible.

 

WHERE
SignupDate < Date_Sub(now() interval numericinterval intervalvalue)

 

In the database the following are the values

numericinterval = 4

intervalvalue = day

[Hooker]

What errors are you getting?

 

I'm 99% sure it doesn't but you might want to try:

WHERE
SignupDate < Date_Sub(now() interval tablename.numericinterval tablename.intervalvalue)

just incase.

[redbrad0]

Query I am trying to run, but if I replace _Test.Test_IntervalType with minute then it runs fine.

 

select
*
from
_TestUser,
_TestEmail,
_Test
where
TestUser_TestID=Test_ID
and
TestEmail_TestID=Test_ID
and
TestUser_SignUpDate < Date_Sub(now(), interval _TestEmail.TestEmail_EmailInterval [b]_Test.Test_IntervalType[/b])

 

Here is the dump of the test tables in using this.

CREATE TABLE `_Test` (
  `Test_ID` int(11) NOT NULL auto_increment,
  `Test_Name` varchar(100) default NULL,
  `Test_IntervalType` enum('minute','hour') default 'hour',
  PRIMARY KEY  (`Test_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
insert  into `_Test`(`Test_ID`,`Test_Name`,`Test_IntervalType`) values (1,'Test Name','hour');

CREATE TABLE `_TestEmail` (
  `TestEmail_ID` int(11) NOT NULL auto_increment,
  `TestEmail_TestID` int(11) default NULL,
  `TestEmail_EmailName` varchar(100) default NULL,
  `TestEmail_EmailInterval` int(11) default '0',
  PRIMARY KEY  (`TestEmail_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

insert  into `_TestEmail`(`TestEmail_ID`,`TestEmail_TestID`,`TestEmail_EmailName`,`TestEmail_EmailInterval`) values (1,1,'Test Email',4);

CREATE TABLE `_TestUser` (
  `TestUser_ID` int(11) NOT NULL auto_increment,
  `TestUser_TestID` int(11) default NULL,
  `TestUser_Name` varchar(50) default NULL,
  `TestUser_SignUpDate` datetime default NULL,
  PRIMARY KEY  (`TestUser_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

insert  into `_TestUser`(`TestUser_ID`,`TestUser_TestID`,`TestUser_Name`,`TestUser_SignUpDate`) values (1,1,'Brad','2008-08-16 14:14:14');

[toplay]

You can do a separate query first to retrieve your numericinterval and intervalvalue values and use those to form a new SQL query.

 

$sql = sprintf("...WHERE SignupDate < DATE_SUB(NOW() interval %0.0f %s)", $numericinterval, $intervalvalue); // Vars retrieved from previous query

[fenway]

Or you can get really fancy (read: bad idea) by SELECTing the desired query INTO a user variable and then executing it.

[Hooker]

Or you can get really fancy (read: bad idea) by SELECTing the desired query INTO a user variable and then executing it.

 

True, that's probably the only option for doing it exclusively with MySQL though.

[toplay]

The best is not to use DATE_SUB() in the 'WHERE' clause at all but an actual date. Execute the query to retrieve your values and use PHP strtotime() to calculate the date, then use that in your 'WHERE' query instead of DATE_SUB(). That will be the fastest.

[fenway]

The best is not to use DATE_SUB() in the 'WHERE' clause at all but an actual date. Execute the query to retrieve your values and use PHP strtotime() to calculate the date, then use that in your 'WHERE' query instead of DATE_SUB(). That will be the fastest.

But that precludes using DB date values....

[toplay]

Specifying a date/time in the "WHERE" clause will be faster than MySQL executing a function (DATE_SUB) every time.

 

FYI - extra info: Even if a column like SignupDate had a key/index MySQL may or likely would not use the index if used in a function (i.e. DATE_SUB(SignupDate...)).

 

[fenway]

Specifying a date/time in the "WHERE" clause will be faster than MySQL executing a function (DATE_SUB) every time.

 

FYI - extra info: Even if a column like SignupDate had a key/index MySQL may or likely would not use the index if used in a function (i.e. DATE_SUB(SignupDate...)).

 

Yes, but php takes time to calculate the date too...

 

And if you do the math on the value, not the column, the index can still be used.