I need more coffee.....

I have a 'DATE' column [NOW()] so my records are timestamped. Can't
quite figure out the correct query for pulling records between now and
a given period of time (24 hours, ten minutes, whatever...) What am I
missing for that last bit?


SELECT * FROM `my_table` WHERE `my_date` BETWEEN NOW() AND ???

TIA.
../JLK

On 24 May, 11:58, Akhenaten <jonko...@gmail.com> wrote:
> I need more coffee.....
>
> I have a 'DATE' column [NOW()] so my records are timestamped. Can't
> quite figure out the correct query for pulling records between now and
> a given period of time (24 hours, ten minutes, whatever...) What am I
> missing for that last bit?
>
> SELECT * FROM `my_table` WHERE `my_date` BETWEEN NOW() AND ???
>
> TIA.
> ./JLK

It's not what you're missing from the last bit, it's the first bit
that's the problem.

A DATE column will only hold a date. To work with intervals of
minutes, you need a DATETIME or TIMESTAMP column. If you have a DATE
column, your records are date stamped, not timestamped. Once you have
a timestamp you can use the TIMESTAMPADD() function to get the
interval.

You will find details of this hidden away in the manual. The hide it
really well as they put it in the section called "Date and Time
Functions". Go figure. Could they have made it more difficult to find?

On May 24, 6:58 am, Akhenaten <jonko...@gmail.com> wrote:
> I need more coffee.....
>
> I have a 'DATE' column [NOW()] so my records are timestamped. Can't
> quite figure out the correct query for pulling records between now and
> a given period of time (24 hours, ten minutes, whatever...) What am I
> missing for that last bit?
>
> SELECT * FROM `my_table` WHERE `my_date` BETWEEN NOW() AND ???
>
> TIA.
> ./JLK

One of the ways to achieve this is to use the DATE_SUB function.

http://dev.mysql.com/doc/refman/5.0/...ction_date-sub

Nb.

select * from `my_table` where `my_date` > DATE_SUB(NOW(), INTERVAL 1
DAY)
and `my_date` <
DATE_SUB(NOW(), INTERVAL 1 HOUR) ;


Regards,
Bill

>
> It's not what you're missing from the last bit, it's the first bit
> that's the problem.
>
> A DATE column will only hold a date. To work with intervals of
> minutes, you need a DATETIME or TIMESTAMP column. If you have a DATE
> column, your records are date stamped, not timestamped. Once you have
> a timestamp you can use the TIMESTAMPADD() function to get the
> interval.
>
> You will find details of this hidden away in the manual. The hide it
> really well as they put it in the section called "Date and Time
> Functions". Go figure. Could they have made it more difficult to find?


Correction - the column is in a timestamp format, just unsure as how
to reference my column as the date. Those nice little date/time
functions (ref'd here: http://dev.mysql.com/doc/refman/5.0/...functions.html)
are severely lacking in both explanation and example (IMHO).

On May 24, 6:13 am, Bill Turczyn <bturc...@gmail.com> wrote:
> On May 24, 6:58 am, Akhenaten <jonko...@gmail.com> wrote:
>
> > I need more coffee.....
>
> > I have a 'DATE' column [NOW()] so my records are timestamped. Can't
> > quite figure out the correct query for pulling records between now and
> > a given period of time (24 hours, ten minutes, whatever...) What am I
> > missing for that last bit?
>
> > SELECT * FROM `my_table` WHERE `my_date` BETWEEN NOW() AND ???
>

>
> select * from `my_table` where `my_date` > DATE_SUB(NOW(), INTERVAL 1
> DAY)
> and `my_date` <
> DATE_SUB(NOW(), INTERVAL 1 HOUR) ;
>
> Regards,
> Bill

Thank you Bill...just the example I needed.