Sun Microsystems, Inc. today announced MySQL™ 5.4, a new version of the world’s most popular open source database, designed to deliver significant performance and scalability improvements to MySQL applications. A preview version of MySQL 5.4 is available now for download at http://www.mysql.com/5.4.

MySQL 5.4 Features & Benefits

A number of new enterprise features and compelling fixes are planned for MySQL 5.4, including:

• Scalability improvements — these fixes allow the InnoDB storage engine to scale up to 16-way x86 servers and 64-way CMT servers, more than doubling its previous capability;
• Subquery optimizations — improves the performance of analytic query operations, with some subqueries now executing in a fraction of the time compared to previous MySQL versions;
• New query algorithms — utilizes main memory to speed up the execution time of multi-way joins, especially for MySQL Cluster because the number of round-trips between the server and cluster nodes is minimized;
• Improved stored procedures — enables more robust error management through the implementation of the SIGNAL/RESIGNAL functions, so applications can more easily rely on stored procedures for business logic;
• Improved prepared statements — Output parameters will now be supported in prepared statements, which increases their functionality;
• Improved Information Schema — provides more metadata access to parameters and data return types that stored procedures use, which allows much more information to be made available for developers using connectors such as ODBC and JDBC;
• Improved DTrace support — improves diagnostics and troubleshooting capabilities for MySQL on the Solaris(TM) Operating System.

Some time we have to collect last 7 or 15 days or X days (or month, year or week) data from MySQL table. For example let us find out who are the members logined in our forum in last week. One site may be interested in knowing new new users registered for newsletter. Here irrespective of the date values we want the records of last X days from today, or we can say that the records between today and last X days ( month , year or week) are required.

We will use the MySQL function CURDATE() to get the today’s date.

To get the difference in today date and previous day or month we have to use the MySQL function DATE_SUB

DATE_SUB is a MySQL function which takes date expression, the interval and the constant to return the date value for further calculation.

Here are some sample queries on how to get the records as per requirements . 

select * from tbl_members where `created_on` >= DATE_SUB(CURDATE(), INTERVAL 15 DAY)

The above query will return last 15 days records. Note that this query will return all future dates also. To exclude future dates we have to modify the above command a little by using between query to get records. Here is the modified one.

SELECT * FROM tbl_members WHERE `created_on` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 15 DAY ) AND CURDATE( )

Let us try to get records added in last one month

select * from tbl_members where `created_on` >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

Here also future records will be returned so we can take care of that by using BETWEEN commands if required.

select * from tbl_members where `created_on` >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

You can easily make out what the above query will return.

We can collect records between a particular date ranges by using between command and DATE_SUB. Here are some queries to generate records between two date ranges.

select * from tbl_members where `created_on` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 3 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 0 MONTH )

This query will return records between last three months. This query again we will modify to get the records between three moths and six months.

select * from tbl_members where `created_on` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 3 MONTH )

Now let us change this to get records between 6 month and 12 month.

select * from tbl_members where `created_on` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 12 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 6 MONTH )

With this you can understand how the records between a month range or a year range can be collected from a table. Note that the months ranges are calculated starting from current day. So if we are collecting records of last three months and we are in 20th day of 1st month then records of 20th day of 1st month we will get but the records of 19th day of 1st month will be returning on next query that is between 3 months and 6 months.

Now let us try a different requirement. How to get the records of the working days of the week so far ? If today is Thursday then records from Monday to Thursday should be returned. We will discuss this in our next section >>.

Here is the sql code to create and fill the table with records

CREATE TABLE tbl_members (
    `id` int(2) NOT NULL auto_increment,
    `user_name` varchar(50) NOT NULL ,
    `created_on` datetime NOT NULL default ’0000-00-00 00:00:00′,
   
    UNIQUE KEY `id` (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

INSERT INTO tbl_members VALUES (1, ‘example1@example.com’, ’2008-03-20 00:00:00′);
INSERT INTO tbl_members VALUES (2, ‘example2@example.com’, ’2008-04-16 23:40:30′);
INSERT INTO tbl_members VALUES (3, ‘example3@example.com’, ’2007-11-08 15:02:15′);
INSERT INTO tbl_members VALUES (4, ‘example4@example.com’, ’2006-05-10 10:10:10′);
INSERT INTO tbl_members VALUES (5, ‘example5@example.com’,  ’2006-05-10 10:10:10′);
INSERT INTO tbl_members VALUES (6, ‘example6@example.com’,  ’2006-05-10 10:10:10′);