MySQL Date Math

SELECT DATE_SUB(CURDATE(), INTERVAL 30 DAY);
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
SELECT DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
SELECT DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
SELECT DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
SELECT DATE_SUB(CURDATE(), INTERVAL 18 MONTH)

SELECT DATE_SUB(CURDATE(), INTERVAL 24 MONTH)
SELECT DATE_SUB(CURDATE(), INTERVAL 10 YEAR)
SELECT DATE_SUB(CURDATE(), INTERVAL 100 YEAR)

1997-11-01

SELECT COUNT( `WB1` ) FROM `PB` WHERE `WB1` =  15 and `DDate` >= DATE_SUB(CURDATE(), INTERVAL 10 MONTH)



CURDATE()
  • Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context. 
    mysql> SELECT CURDATE();
        -> '2008-06-13'
mysql> SELECT CURDATE() + 0;
        -> 20080613

Open MYSQL for remote connections

https://rtcamp.com/tutorials/mysql/remote-access/

Change mysql config

Start with editing mysql config file

vim /etc/mysql/my.cnf

Comment out following lines.

#bind-address           = 127.0.0.1
#skip-networking

If you do not find skip-networking line, add it and comment out it.

Restart mysql server.

service mysql restart

Change GRANT privilege

You may be surprised to see even after above change you are not getting remote access or getting access but not able to all databases.

By default, mysql username and password you are using is allowed to access mysql-server locally. So need to update privilege.

Run a command like below to access from all machines.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Run a command like below to give access from specific IP.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

You can replace 1.2.3.4 with your IP. You can run above command many times to GRANT access from multiple IPs.

You can also specify a separate USERNAME & PASSWORD for remote access.

You can check final outcome by:

SELECT * from information_schema.user_privileges where grantee like "'USERNAME'%";

Finally, you may also need to run:

mysql> FLUSH PRIVILEGES;

Test Connection

From terminal/command-line:

mysql -h HOST -u USERNAME -pPASSWORD

If you get a mysql shell, don’t forget to run show databases; to check if you have right privileges from remote machines.

Concatenating CSV files

Many programs will log data to a CSV (comma separated value) file. These files are usually structured with a standard format. Let’s say you want to import the data into a database or spreadsheet. There is an alternative to importing 2, 5, 10 or 100+ files one at a time. You can concatenate the files into one file. Here is how you do it…

  1. Create a new directory on your hard drive (ie: C:\CSV-Files)
  2. Copy all the files into a new directory.
  3. Open a command window by clicking Start->Run. In the open line type “cmd” and hit return
  4. CD to the directory you created in step one (ie: cd C:\CSV-Files)
  5. Copy all the files to a single file… copy *.csv consolidated.csv
  6. You can now import consolidated.csv into your database or spreadsheet.