Archive for the ‘MySql’ Category

MySQL Date Age Calculation

Wednesday, July 2nd, 2008

I have always had the need to calculate the age as year and month from a MySQL field of type date. No doubt you can achieve this with a bit of PHP, but when you require to do the conversion right from MySQL, you can only go as far as getting the age in “years”.

I have used here a MySQL stored routine to achive this:

DELIMITER $$
DROP FUNCTION IF EXISTS `my_database`.`getage` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getage`(pdate DATE) RETURNS CHAR(30) DETERMINISTIC
BEGIN
DECLARE years INT;
DECLARE months INT;
DECLARE days INT;
 
DECLARE current_year INT;
DECLARE current_month INT;
DECLARE current_day INT;
 
DECLARE year_diff INT;
DECLARE month_diff INT;
DECLARE day_diff INT;
 
DECLARE yearstring CHAR(6);
DECLARE monthstring CHAR(7);
 
SELECT YEAR(pdate) INTO years;
SELECT MONTH(pdate) INTO months;
SELECT DAY(pdate) INTO days;
 
SELECT YEAR(CURRENT_DATE()) INTO current_year;
SELECT MONTH(CURRENT_DATE()) INTO current_month;
SELECT DAY(CURRENT_DATE()) INTO current_day;
 
SELECT (current_year - years) INTO year_diff;
SELECT (current_month - months) INTO month_diff;
SELECT (current_day - days) INTO day_diff;
 
if (current_month < months) THEN
	SET month_diff = (((months - 12) * -1) + current_month);
	SET year_diff = (year_diff - 1);
END IF;
 
if ( month_diff = 1 ) THEN
	SET monthstring = "month";
ELSE
	SET monthstring = "months";
END IF;
 
if ( year_diff = 1 ) THEN
	SET yearstring = "year";
ELSE
	SET yearstring = "years";
END IF;
 
if ( year_diff = 0 ) THEN
	RETURN CONCAT_WS(' ', month_diff, monthstring);
ELSE
	if (month_diff > 0) THEN
		RETURN CONCAT_WS(' ',year_diff, yearstring, month_diff, monthstring);
	ELSE
		RETURN CONCAT_WS(' ',year_diff, yearstring);
	END IF;
END IF;
 
END $$
 
DELIMITER ;

You can then make use of the stored routine to convert Dates into ages by doing so:

SELECT getage(BirthDate) as UserAge FROM my_database.users

Tags: ,
Posted in MySql | No Comments »

MySQL Forge is Open

Monday, August 13th, 2007

Use the Forge!

The Forge: home of MySQL Community
MySQL Forge
MySQL Forge is the place where MySQL AB, the company behind the popular Open Source Database (MySQL) and the community meet and work together.

  • If you have a cool project? Give it a home on Projects.
  • Want to share a cool snippet? Paste it to Snippets.
  • Curious about MySQL AB’s development plans? Browse the Worklog.
  • Do you want to share your ideas, contribute to MySQL projects, learn about the internals? Use the Wiki.

Posted in MySql | No Comments »

MySQL Database backup script

Thursday, January 25th, 2007

This script I have here has saved me a number of times from embarrassment. Many a times I happened to have “accidently” dropped an entire database (in phpmyadmin) while wanting to drop only a few tables. Apparently the big-fat “drop” button at the top navigation is related to the database than the tables you have selected in the listing.

The script has the ability to dump your database tables and upload them to an ftp server or email the compressed sql file to your email address. I haven’t written this entirely on my own but with help from various people on discussion forums. Post back your comments and improvements if any.

(more…)

Posted in MySql | 2 Comments »

Re-setting Mysql root password

Sunday, May 28th, 2006

If you have never set a root password for your MySQL database, then the server will not require a password at all for connecting as root. It is recommended to always set a password for each user and more importantly the mysql root user.

On the other hand, If you have set a root password, but forgot what it was, you can set a new password with the following procedure:

Stop MySql Service

First we need to stop the running process of the MySql server.
/etc/init.d/mysqld stop
(more…)

Posted in MySql | No Comments »