SEO

#1 (**permalink**) 02-28-2008, 08:52 AM

Hi,

I have to select elements that are not older than 2 months (=60 days).
The table has a standard DATE column with format (YYYY-MM-DD), called
'date'.

I tried a lot of queries, one of them is following:

SELECT * FROM mytable WHERE date > (SYSDATE()-60);

So basically i'm telling mysql to select elements where the date is
bigger (= older) than the actual system date diminished with 60 days.
But it does't return anything (and yes I have elements newer than 2
months in the table :-)

I suppose something is wrong with the use of SYSDATE. Anyone any
experience with this ?

#2 (**permalink**) 02-28-2008, 08:52 AM

b.coolsaet@gmail.com wrote:

> Hi,
>
> I have to select elements that are not older than 2 months (=60 days).
> The table has a standard DATE column with format (YYYY-MM-DD), called
> 'date'.
>
> I tried a lot of queries, one of them is following:
>
> SELECT * FROM mytable WHERE date > (SYSDATE()-60);
>
> So basically i'm telling mysql to select elements where the date is
> bigger (= older) than the actual system date diminished with 60 days.
> But it does't return anything (and yes I have elements newer than 2
> months in the table :-)
>
> I suppose something is wrong with the use of SYSDATE. Anyone any
> experience with this ?

The user manual has a lot of experience of this and explains how to do
it.

Think about it. SYSDATE() "Returns the current date and time as a value
in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether
the function is used in a string or numeric context." (quoted from
manual)

If you simply substract 60 from this, how is MySQL to know 60 what?
60 years?
60 seconds?
60 bars of chocolate?

Try:
SELECT * FROM mytable WHERE date > (SYSDATE()-INTERVAL 60 DAY);

as the manual suggests
http://dev.mysql.com/doc/refman/5.0/...functions.html

#3 (**permalink**) 02-28-2008, 08:52 AM

I think that it would be better with

SELECT * FROM mytable WHERE date > adddate(sysdate(), -60)

Which is a spin off from "date_add(date, -60, DAY)"

You can swap DAY for anything like:

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

Hope that helps.

"Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
news:1167826364.552539.166600@n51g2000cwc.googlegr oups.com...
>
> b.coolsaet@gmail.com wrote:
>
>> Hi,
>>
>> I have to select elements that are not older than 2 months (=60 days).
>> The table has a standard DATE column with format (YYYY-MM-DD), called
>> 'date'.
>>
>> I tried a lot of queries, one of them is following:
>>
>> SELECT * FROM mytable WHERE date > (SYSDATE()-60);
>>
>> So basically i'm telling mysql to select elements where the date is
>> bigger (= older) than the actual system date diminished with 60 days.
>> But it does't return anything (and yes I have elements newer than 2
>> months in the table :-)
>>
>> I suppose something is wrong with the use of SYSDATE. Anyone any
>> experience with this ?
>
> The user manual has a lot of experience of this and explains how to do
> it.
>
> Think about it. SYSDATE() "Returns the current date and time as a value
> in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether
> the function is used in a string or numeric context." (quoted from
> manual)
>
> If you simply substract 60 from this, how is MySQL to know 60 what?
> 60 years?
> 60 seconds?
> 60 bars of chocolate?
>
> Try:
> SELECT * FROM mytable WHERE date > (SYSDATE()-INTERVAL 60 DAY);
>
> as the manual suggests
> http://dev.mysql.com/doc/refman/5.0/...functions.html
>

#4 (**permalink**) 02-28-2008, 08:52 AM

Sean wrote:

> I think that it would be better with
>
> SELECT * FROM mytable WHERE date > adddate(sysdate(), -60)
>
> Which is a spin off from "date_add(date, -60, DAY)"
>
> You can swap DAY for anything like:
>
> MICROSECOND
> SECOND
> MINUTE
> HOUR
> DAY
> WEEK
> MONTH
> QUARTER
> YEAR
>
>
> Hope that helps.
>
Why would it be better?
And why wouldn't you use subdate rather than adddate with a negative
argument?

#5 (**permalink**) 02-28-2008, 08:52 AM

"Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
news:1167832817.316730.194960@s34g2000cwa.googlegr oups.com...
>
> Sean wrote:
>
>> I think that it would be better with
>>
>> SELECT * FROM mytable WHERE date > adddate(sysdate(), -60)
>>
>> Which is a spin off from "date_add(date, -60, DAY)"
>>
>> You can swap DAY for anything like:
>>
>> MICROSECOND
>> SECOND
>> MINUTE
>> HOUR
>> DAY
>> WEEK
>> MONTH
>> QUARTER
>> YEAR
>>
>>
>> Hope that helps.
>>
> Why would it be better?
> And why wouldn't you use subdate rather than adddate with a negative
> argument?
>

Sorry, you're right.

I was implying that this would be a better option than SYSDATE()-INTERVAL 60
DAY and overlooked "sub_date", which of course makes even more sense.

SA

#6 (**permalink**) 02-28-2008, 08:52 AM

Sean wrote:

> "Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
> news:1167832817.316730.194960@s34g2000cwa.googlegr oups.com...
> >
> > Sean wrote:
> >
> >> I think that it would be better with
> >>
> >> SELECT * FROM mytable WHERE date > adddate(sysdate(), -60)
> >>
> >> Which is a spin off from "date_add(date, -60, DAY)"
> >>
> >> You can swap DAY for anything like:
> >>
> >> MICROSECOND
> >> SECOND
> >> MINUTE
> >> HOUR
> >> DAY
> >> WEEK
> >> MONTH
> >> QUARTER
> >> YEAR
> >>
> >>
> >> Hope that helps.
> >>
> > Why would it be better?
> > And why wouldn't you use subdate rather than adddate with a negative
> > argument?
> >
>
> Sorry, you're right.
>
> I was implying that this would be a better option than SYSDATE()-INTERVAL 60
> DAY and overlooked "sub_date", which of course makes even more sense.
>
> SA
But why is subdate (or date_sub) better than the opure maths approach
of
SYSDATE()-INTERVAL 60 ?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Trouble using SYSDATE()