[ale] mysql sql question

Chuck Payne terrorpup at gmail.com
Fri Dec 17 08:52:49 EST 2010


On Fri, Dec 17, 2010 at 1:04 AM, Richard Bronosky <Richard at bronosky.com> wrote:
> 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 #!.
>
> _______________________________________________
> 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
>

Richard,

Again thanks. That worked great.

mysql -ss fyre -e "SELECT ip,country,city FROM fyre.ips WHERE date =
DATE(SUBTIME(NOW(),'1:00:00')) AND time
>=TIME(SUBTIME(NOW(),'1:00:00'));"

83.234.207.47    BELGIUM         Brussels

Now I can get an hourly status.

Thanks


-- 
-----------------------------------------
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.



More information about the Ale mailing list