kreut — 2011-05-25T13:24:12-04:00 — #1
This is my first time using DateDiff and it looks pretty straightforward; I fear, however, that I keep getting the following error:
Incorrect parameter count in the call to native function 'DATEDIFF' :injured:
My goal in the code below is to get all records that are more than 3 days old; the Timestamp variable looks like: 2011-05-22 20:26:42
SELECT * FROM `homepage_homeschool_schedule` WHERE DATEDIFF(DD,Timestamp,CURRENT_TIMESTAMP)>3
Any help would be appreciated.
starlion — 2011-05-25T13:59:49-04:00 — #2
Are you using MySQL? (I ask because you wrapped your code in mySQL formatting forum-code) If so, you do have the wrong parameter count.
MySQL :: MySQL 5.1 Reference Manual :: 11.7 Date and Time Functions
r937 — 2011-05-25T14:17:59-04:00 — #3
best advice ever: in order to allow an index on the column to be used, and therefore to make your query perform well, it is important never to apply a function to a column
so your DATEDIFF approach is dead right out of the starting gate
a wise man once said that it is far better to get the correct answer after a while than a wrong answer immediately...
and to be sure, DATEDIFF can give you the right answer, but it's not efficient
if you're trying to find "more than 3 days old" you should do it this way --
WHERE `timestamp` < CURRENT_DATE - INTERVAL 3 DAY
note that you shouldn't use a reserverd word like TIMESTAMP for the name of a column
kreut — 2011-05-25T14:19:54-04:00 — #4
WHERE dbTime > (SELECT CURRENT_TIME - INTERVAL 3 DAY)
r937 — 2011-05-25T14:21:25-04:00 — #5
no, the other way around, and without the parentheses, and using CURRENT_DATE instead, and without the extra SELECT keyword
kreut — 2011-05-25T14:23:05-04:00 — #6
You are correct... and thanks for the link! I read through the page, and have tried the following:
WHERE dbTime < (SELECT CURRENT_TIME - INTERVAL 3 DAY)
I changed TIMESTAMP to read dbTime because I thought that maybe using TIMESTAMP as a variable name wouldn't be a good idea. Regardless, now I have no error, but whether by inequality is less than or greater than, I get no rows back.
My goal is to get all records with dbTimes that are earlier than 3 days in the past. Any additional thoughts or hints would be appreciated.
r937 — 2011-05-25T14:37:47-04:00 — #7
did you try it the way i suggested?
WHERE dbtime < CURRENT_DATE - INTERVAL 3 DAY
kreut — 2011-05-25T14:45:30-04:00 — #8
Thank you for the solution and your patient response: due to a lag in receiving your messages, it only seemed as though I wasn't taking your advice right off the bat.
Have a great day...