MySQL problem: date calculation

Hi folks,

I'm having a problem with MySQL. I have a table,
one of whose columns is called `date`, which is a timestamp.
I need to SELECT all the rows whose timestamps are
less than 7 days old. To my surprise, all of the examples
of using DATEDIFF from the MySQL online reference are failing.

For instance, this does not work:

SELECT * FROM mytable WHERE 7 > DATEDIFF(`date`,CURDATE());

Nor does this:

SELECT * FROM mytable WHERE 7 > DATEDIFF(`date`, NOW());

Any ideas?
Incidentally I'm accessing the db through php_admin,
and I am not sure how to get the MySQL version number
but it is not displayed anywhere.

Thanks.

plenty900@yahoo.com wrote:
> Hi folks,
>
> I'm having a problem with MySQL. I have a table,
> one of whose columns is called `date`, which is a timestamp.
> I need to SELECT all the rows whose timestamps are
> less than 7 days old. To my surprise, all of the examples
> of using DATEDIFF from the MySQL online reference are failing.
>
> For instance, this does not work:
>
> SELECT * FROM mytable WHERE 7 > DATEDIFF(`date`,CURDATE());
>
> Nor does this:
>
> SELECT * FROM mytable WHERE 7 > DATEDIFF(`date`, NOW());
>
> Any ideas?
> Incidentally I'm accessing the db through php_admin,
> and I am not sure how to get the MySQL version number
> but it is not displayed anywhere.
>
> Thanks.

Don't you want the parameters the other way around? I.e.:
DATEDIFF(NOW(),date_val);

You can find the server version of MySQL in PHPMyadmin on the root page
just under the host name called Server Version. (The client version is
across from it on the right).

plenty900@yahoo.com wrote:
> Hi folks,
>
> I'm having a problem with MySQL. I have a table,
> one of whose columns is called `date`, which is a timestamp.
> I need to SELECT all the rows whose timestamps are
> less than 7 days old. To my surprise, all of the examples
> of using DATEDIFF from the MySQL online reference are failing.
>
> For instance, this does not work:
>
> SELECT * FROM mytable WHERE 7 > DATEDIFF(`date`,CURDATE());
>
> Nor does this:
>
> SELECT * FROM mytable WHERE 7 > DATEDIFF(`date`, NOW());
>
> Any ideas?
> Incidentally I'm accessing the db through php_admin,
> and I am not sure how to get the MySQL version number
> but it is not displayed anywhere.
>
> Thanks.

you can also use interval.

<plenty900@yahoo.com> schreef in bericht
news:4b12fecb-d9b1-4097-b41d-98a65213d81c@i3g2000hsf.googlegroups.com...
> Hi folks,
>
> I'm having a problem with MySQL. I have a table,
> one of whose columns is called `date`, which is a timestamp.
> I need to SELECT all the rows whose timestamps are
> less than 7 days old. To my surprise, all of the examples
> of using DATEDIFF from the MySQL online reference are failing.
>
> For instance, this does not work:
>
> SELECT * FROM mytable WHERE 7 > DATEDIFF(`date`,CURDATE());
>
> Nor does this:
>
> SELECT * FROM mytable WHERE 7 > DATEDIFF(`date`, NOW());
>
> Any ideas?
> Incidentally I'm accessing the db through php_admin,
> and I am not sure how to get the MySQL version number
> but it is not displayed anywhere.
>
> Thanks.

Try:
SELECT *, DATEDIFF(`date`, NOW()) FROM mytable ;

and see for yourself what you're missing....

http://dev.mysql.com/doc/refman/5.0/...ction_datediff

> SELECT *, DATEDIFF(`date`, NOW()) FROM mytable ;

I get this:
MySQL said: Documentation
#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near '(`mydate`, NOW()) FROM `mytable`' at line 1

> http://dev.mysql.com/doc/refman/5.0/...nctions.html#f...

On that page, it suggests typing the following:

SELECT DATEDIFF( '1997-12-31 23:59:59', '1997-12-30' ) ;

Guess what? That gives:

MySQL said: Documentation
#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near '('1997-12-31 23:59:59','1997-12-30')' at line 1

On Fri, 04 Jan 2008 19:12:09 +0100, <plenty900@yahoo.com> wrote:

>
>> http://dev.mysql.com/doc/refman/5.0/...nctions.html#f...
>
> On that page, it suggests typing the following:
>
> SELECT DATEDIFF( '1997-12-31 23:59:59', '1997-12-30' ) ;
>
> Guess what? That gives:
>
> MySQL said: Documentation
> #1064 - You have an error in your SQL syntax. Check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near '('1997-12-31 23:59:59','1997-12-30')' at line 1

What version of mysql are you using? (DATEDIFF() was introduced in v4.1.1).
--
Rik Wasmus

> you can also use interval.

Thanks, that worked.

<plenty900@yahoo.com> schreef in bericht
news:7fb4b744-c243-4844-9611-37d3877361a3@5g2000hsg.googlegroups.com...
>
>> http://dev.mysql.com/doc/refman/5.0/...nctions.html#f...
>
> On that page, it suggests typing the following:
>
> SELECT DATEDIFF( '1997-12-31 23:59:59', '1997-12-30' ) ;
>
> Guess what? That gives:
>
> MySQL said: Documentation
> #1064 - You have an error in your SQL syntax. Check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near '('1997-12-31 23:59:59','1997-12-30')' at line 1
>

can you do this:
SHOW VARIABLES LIKE '%version%';

to show the version of mysql you are using
(see: http://dev.mysql.com/doc/refman/4.1/...variables.html)