Showing posts from April, 2012

MySQL time filtering tricks

I needed to filter rows using timestamp columns but with extra in-deep conditions such as:

- Looking up rows with the "lastRestart" column 2 minutes (or more) before now.
`lastRestart` < (NOW() - INTERVAL 2 MINUTE)

- Or instead of the literal value '2', you can reference a coloumn by it's name:
`lastRestart` < (NOW() - INTERVAL `Frequency` MINUTE)

- Comparing time fields with timestamps. Which means that you need to compare only hours, minutes and seconds with timestamp values\coloumns.
start_timeofday <= CAST(NOW() AS TIME)-- start_timeofday is a time coloumn.

Copying tables on mysql

Here is a snippet to copy tables (possibly from one database to another):

ipk is a schema, ipk_v2 as another schema. Here I'm trying to copy a table called "tclrequest" from ipk to ipk_v2

USE ipk_v2;CREATE TABLE `table11` LIKE `ipk`.`tclrequest`; -- A temp tableINSERT INTO tclrequest SELECT * FROM ipk.tclrequest;

You'll need to delete the temp table when you are done.

Detecting devices on a local network using ping

I'm borrowing this idea from here. If you continue reading the post you'll find many innovative ideas in the comments but I just have a slight improvement to the original line of batch code:

for ip in $(seq 1 254); do ping -c 1 192.168.1.$ip>/dev/null; [ $? -eq 0 ] && echo "192.168.1.$ip UP" || : ; doneYou can speed things up by adding the -W parameter to the ping command to decrease the timeout to 1 second instead of the default which is 3 seconds.
for ip in $(seq 1 254); do ping -W 1 -c 1 192.168.1.$ip>/dev/null; [ $? -eq 0 ] && echo "192.168.1.$ip UP" || : ; done
...for windows
for /L %I in (1,1,254) DO ping -w 30 -n 1 192.168.1.%I | find "bytes="