Showing posts with label MySQL. Show all posts
Showing posts with label MySQL. Show all posts

Thursday, July 11, 2013

Query from Today and Last XX Days - MySQ

This is a common line i always add to my queries in MySQL and just wanted to back it up here . How do you go about in 1 line with querying data for the last xx days :) having the current date or specific date you want to start from. Yes i got an answer for you :). First thing is you need to apply DATE_FORMAT in the SELECT clause, not the WHERE clause:
SELECT  DATE_FORMAT(createDate, '%m/%d/%Y')
FROM    refugeeTable
WHERE   createDate BETWEEN CURDATE() - INTERVAL 60 DAY AND CURDATE()
Along the way i found out that the CURDATE() returns only the DATE portion of the date. In otherwords, if you store createDate as a DATETIME with the time portion filled, this query will not select the today's records.
In this case, you'll need to use SYSDATE instead:
SELECT  DATE_FORMAT(createDate, '%m/%d/%Y')
FROM    refugeeTable
WHERE   createDate BETWEEN SYSDATE() - INTERVAL 60 DAY AND SYSDATE()
OR (specific dates)
SELECT  DATE_FORMAT(createDate, '%m/%d/%Y')
FROM    refugeeTable
WHERE   createDate BETWEEN ‘2013-07-01’ - INTERVAL 60 DAY AND ‘2013-07-30’

Wednesday, July 3, 2013

Query MySQL with RMySQL

I’m currently using this more frequently on day to day basis and just wanted to document it here. I use these steps to connect to our live MySQL database from R all the time, and the setup is so simple. Follow my previous blog post on installing RMySQL package, connect to a database (here any MySQL instance), set up a function to make querying easier, and query the database to return results as a data frame.
# First lets make sure we Load the package
>library(RMySQL)
# Then the next step is to Set up a connection to the database. I’m using a "localhost" just for this example blog post, but feel free to use any host IPs to your servers. I'm found of running ?Functionname() to get details - so feel free to run ?MySQL() what most functions do.


>conn <- dbConnect(MySQL(), user="xxxxx", password="xxxxxx",dbname="refugees" host="localhost")
# Here is where the main fun begins, the Function to make it easier to query
>query <- function(...) dbGetQuery(conn, ...)
# Run your query at this point through R and that’s it.
>query("SELECT name, age FROM refugees;")

Correction: Due to change in R version the above stopped working and i was getting this error

"Error in mysqlNewConnection(drv, ...) :

  RS-DBI driver: (Failed to connect to database: Error: Access denied for user 'root'@'localhost' (using password: YES)
)"

Fix below:
Set the driver first

> drv = dbDriver("MySQL")

>conn <- dbConnect(drv, user="xxxxx", password="xxxxxx",dbname="refugees" host="localhost")
worked and the silly error was fixed. 

Friday, June 28, 2013

RMySQL on Ubuntu 12.04

At our little start-up we run our system on MySQL and i constantly want to play around with data to have an overview of how users are registering, searching etc As you might have noticed from my previous blogs, i'm sticking to R for all my analyses so even MySQL will be accessed from R so reason why i need to install RMySQL


I kicked off my R tool by typing R at the prompt and running.


> install.packages(“RMySQL”)


This loads and throws me these errors


“ERROR: configuration failed for package ‘RMySQL’ ...”


I looked up online and there seems to be a dependency for just a linux package "libmysqlclient-dev" which contains MySQL so i went ahead an installed that too.


$sudo apt-get install libmysqlclient-dev


$sudo apt-get install r-cran-rmysql


then finally go back to the R shell
> install.packages(“RMySQL”)


and woohooo that runs smoothly on my Ubuntu 12.04 Linux Machine.