[ale] mysql sql question

[Richard Bronosky]

I disagree with the choice to separate date and time instead of just
having a single datetime. But I will forgive that. The reason is that
when you are doing datetime math you have to be concerned about deltas
that span days. That is to say that "since the last hour" from (the
current time as I write this)  "2010-12-17 00:52:10" is "2010-12-16
23:52:10". So now you have to worry about doing datetime math for 2
columns. But, I'm not going to let that stop me from rocking this
question.

Here is the secret to solving your problem:
(ale_temp at localhost) [ale_temp]> SELECT NOW(),
DATE(SUBTIME(NOW(),'1:00:00')), TIME(SUBTIME(NOW(),'1:00:00'));
+---------------------+--------------------------------+--------------------------------+
| NOW()               | DATE(SUBTIME(NOW(),'1:00:00')) |
TIME(SUBTIME(NOW(),'1:00:00')) |
+---------------------+--------------------------------+--------------------------------+
| 2010-12-17 05:52:10 | 2010-12-16                     | 23:52:10
                 |
+---------------------+--------------------------------+--------------------------------+

Now, I'm pretty sure you can take it from here, but I'll go ahead and
sow you the solution:
SELECT * FROM fyre.ips WHERE date = DATE(SUBTIME(NOW(),'1:00:00')) AND
time >=TIME(SUBTIME(NOW(),'1:00:00'));

Keep this bookmarked: http://j.mp/myFunc I lived by it when I was a DB.

On Fri, Dec 17, 2010 at 12:21 AM, Chuck Payne <terrorpup at gmail.com> wrote:
> I need to ask anyone that is good with sql,
>
> I have this table where I entrying in some data, what like to know is
> what has been entry in the last hour
>
> desc fyre.ips;
> +---------+--------------+------+-----+------------+----------------+
> | Field   | Type         | Null | Key | Default    | Extra          |
> +---------+--------------+------+-----+------------+----------------+
> | id      | int(4)       | NO   | PRI | NULL       | auto_increment |
> | ip      | varchar(16)  | NO   | UNI |            |                |
> | date    | date         | NO   |     | 0000-00-00 |                |
> | time    | time         | NO   |     | 00:00:00   |                |
> | country | varchar(255) | NO   |     |            |                |
> | code    | varchar(255) | NO   |     |            |                |
> | city    | varchar(255) | NO   |     |            |                |
> | guessed | varchar(255) | NO   |     |            |                |
> +---------+--------------+------+-----+------------+----------------+
>
> I am trying this sql statment, but I am getting nothing...
>
> select * from fyre.ips where date = "2010-12-17" and time >=
> TIME(now() - INTERVAL 1 HOUR);
>
> Is sql right? If not what am I doing wrong
>
> --
> -----------------------------------------
> Discover it! Enjoy it! Share it! openSUSE Linux.
> -----------------------------------------
> openSUSE -- en.opensuse.org/User:Terrorpup
> openSUSE Ambassador/openSUSE Member
> skype,twiiter,identica,friendfeed -- terrorpup
> freenode(irc) --terrorpup/lupinstein
>
> Have you tried SUSE Studio? Need to create a Live CD,  an app you want
> to package and distribute , or create your own linux distro. Give SUSE
> Studio a try. www.susestudio.com.
>
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://mail.ale.org/mailman/listinfo/ale
> See JOBS, ANNOUNCE and SCHOOLS lists at
> http://mail.ale.org/mailman/listinfo
>



-- 
.!# RichardBronosky #!.