[PHP] Re: Date/Time Query Help
On Tue, 24 Jul 2001 15:09:14 -0400, [EMAIL PROTECTED] (Ryan Shrout) wrote: I need a MySQL query that will select all the entries in it made within the last hour. I tried this, but it is not working correctly, what am I doing wrong? SELECT * FROM session WHERE (session.Date DATE_SUB(NOW(), INTERVAL 1 HOUR)) Now() will return the current time. by definition, at the time it's running, there will be zero or almost zero (depends on how fast the query runs... someone might insert a row between when the now() gets executed and when the query ends) rows. you are basically asking for rows that were inserted from now up to one hour in the future. those rows aren't there yet. try testing for less than. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] Re: Date/Time Query Help
Think of it this way: WHERE session.Date DATE_SUB(NOW(), INTERVAL 1 HOUR) is equal to: WHERE 2001-07-24 15:03:24 SUBTRACTION( 2001-07-24 15:30:21, 00:01:00) = WHERE 2001-07-24 15:03:24 2001-07-24 14:30:21 That seems right now, but it doesn't work Ryan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 24, 2001 2:58 PM To: [EMAIL PROTECTED] Subject: [PHP] Re: Date/Time Query Help On Tue, 24 Jul 2001 15:09:14 -0400, [EMAIL PROTECTED] (Ryan Shrout) wrote: I need a MySQL query that will select all the entries in it made within the last hour. I tried this, but it is not working correctly, what am I doing wrong? SELECT * FROM session WHERE (session.Date DATE_SUB(NOW(), INTERVAL 1 HOUR)) Now() will return the current time. by definition, at the time it's running, there will be zero or almost zero (depends on how fast the query runs... someone might insert a row between when the now() gets executed and when the query ends) rows. you are basically asking for rows that were inserted from now up to one hour in the future. those rows aren't there yet. try testing for less than. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Re: Date/Time Query Help
On Tue, 24 Jul 2001 15:23:10 -0400, [EMAIL PROTECTED] (Ryan Shrout) wrote: Think of it this way: WHERE session.Date DATE_SUB(NOW(), INTERVAL 1 HOUR) is equal to: WHERE 2001-07-24 15:03:24 SUBTRACTION( 2001-07-24 15:30:21, 00:01:00) = WHERE 2001-07-24 15:03:24 2001-07-24 14:30:21 That seems right now, but it doesn't work DATE_SUB. heh. that'll teach me not to read the whole post and THINK about it :). -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] Re: Date/Time Query Help
Heh. :) So, does anyone know what I am doing wrong? Ryan Shrout -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 24, 2001 3:34 PM To: [EMAIL PROTECTED] Subject: Re: [PHP] Re: Date/Time Query Help On Tue, 24 Jul 2001 15:23:10 -0400, [EMAIL PROTECTED] (Ryan Shrout) wrote: Think of it this way: WHERE session.Date DATE_SUB(NOW(), INTERVAL 1 HOUR) is equal to: WHERE 2001-07-24 15:03:24 SUBTRACTION( 2001-07-24 15:30:21, 00:01:00) = WHERE 2001-07-24 15:03:24 2001-07-24 14:30:21 That seems right now, but it doesn't work DATE_SUB. heh. that'll teach me not to read the whole post and THINK about it :). -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Re: Date/Time Query Help
On Tue, 24 Jul 2001 15:23:10 -0400, [EMAIL PROTECTED] (Ryan Shrout) wrote: WHERE session.Date DATE_SUB(NOW(), INTERVAL 1 HOUR) is equal to: WHERE 2001-07-24 15:03:24 SUBTRACTION( 2001-07-24 15:30:21, 00:01:00) = WHERE 2001-07-24 15:03:24 2001-07-24 14:30:21 i just tested on my box (dialup connection logs, start is the datetime the subscriber logged on) and when i do: select login,start,end,secs from log where secs.startdate_sub(now(),interval 1 hour) i get (it's after 3AM here... so the set is small): +--+-+-+--+ | login| start | end | secs | +--+-+-+--+ | costales | 2001-07-25 02:52:35 | 2001-07-25 03:27:54 | 2119 | | utopia | 2001-07-25 03:19:38 | 2001-07-25 03:19:41 |3 | +--+-+-+--+ works for me. maybe it's a data problem? what version of mysql are you using? what happens when you do: select Date from session order by date desc limit 10; ? tiger -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] Re: Date/Time Query Help
When I run: SELECT * FROM session WHERE session.Date DATE_SUB(NOW(), INTERVAL 1 HOUR) I get an empty set, but there are entries that satisfy it. When I run: select Date from session order by Date desc limit 10 I get: +-+ | Date| +-+ | 2001-07-24 04:00:45 | | 2001-07-24 04:00:40 | | 2001-07-24 04:00:20 | | 2001-07-24 03:22:24 | | 2001-07-24 03:02:25 | | 2001-07-24 02:53:55 | | 2001-07-24 02:27:09 | | 2001-07-24 02:24:54 | +-+ 8 rows in set (0.00 sec) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 24, 2001 3:43 PM To: [EMAIL PROTECTED] Subject: Re: [PHP] Re: Date/Time Query Help On Tue, 24 Jul 2001 15:23:10 -0400, [EMAIL PROTECTED] (Ryan Shrout) wrote: WHERE session.Date DATE_SUB(NOW(), INTERVAL 1 HOUR) is equal to: WHERE 2001-07-24 15:03:24 SUBTRACTION( 2001-07-24 15:30:21, 00:01:00) = WHERE 2001-07-24 15:03:24 2001-07-24 14:30:21 i just tested on my box (dialup connection logs, start is the datetime the subscriber logged on) and when i do: select login,start,end,secs from log where secs.startdate_sub(now(),interval 1 hour) i get (it's after 3AM here... so the set is small): +--+-+-+--+ | login| start | end | secs | +--+-+-+--+ | costales | 2001-07-25 02:52:35 | 2001-07-25 03:27:54 | 2119 | | utopia | 2001-07-25 03:19:38 | 2001-07-25 03:19:41 |3 | +--+-+-+--+ works for me. maybe it's a data problem? what version of mysql are you using? what happens when you do: select Date from session order by date desc limit 10; ? tiger -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
