Mysql – How to pass, specific day month and year in the below query

date formatdatetimeMySQL

I have registration according to the data in a table and I want to show the whole month, I found the easy solution with below query but I do not know how can I pass specific day, month and year in below query?

SELECT AAA.date_field,
    IFNULL(BBB.val,0) val
FROM
(
    SELECT date_field
    FROM
    (
        SELECT MAKEDATE(YEAR('2014-02-06'),1) 
             + INTERVAL (MONTH('2014-02-06')-1) MONTH 
             + INTERVAL daynum DAY date_field
        FROM
        (
            SELECT t*10+u daynum FROM
            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
            UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
        ) AA
    ) AA WHERE MONTH(date_field) = MONTH('2014-02-06')
) AAA LEFT JOIN (SELECT date_field,val FROM school) BBB USING (date_field)

Can you please help me how can I pass the day month and year in the above query?

Best Answer

First create a table for as calendar in your database and fill it with few years data.

CREATE TABLE `calendar` (
`date_field` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then prepare your query like :

set @dy='6',@mnth='2',@yr='2016';

select c.date_field,IFNULL(t.val,0) val
from calendar c
  left outer join 
   (SELECT date_field,val FROM school) t 
    USING (date_field)
 where if(@dy='',1,day(c.date_field)=@dy)
   and if(@mnth='',1,month(c.date_field)=@mnth)
   and if(@yr='',1,year(c.date_field=@yr);

Related Solutions

Mysql – selecting the month day and year from a transaction table

You will want to use the DATE_FORMAT function in MySQL to format the date for each query.

-- query 1
select date_format(date, '%m-%d') `m-d`,
  sum(amount) Total
from mytable
group by date_format(date, '%m-%d');

See SQL Fiddle with Demo. Gives a result:

|   M-D | TOTAL |
-----------------
| 04-08 |    50 |
| 05-01 |   200 |
| 05-03 |   400 |

-- query 2
select date_format(date, '%Y-%m') `Y-m`,
  sum(amount) Total
from mytable
group by date_format(date, '%Y-%m');

See SQL Fiddle with Demo. Gives the result:

|     Y-M | TOTAL |
-------------------
| 2013-04 |    50 |
| 2013-05 |   600 |

Mysql – Partition by year and sub-partition by month thesql

I had to do the same thing and solved slightly differently. Basically as far as I understand by reading the docs MySQL subpartitioning does not support partition types besides HASH and KEY.

In MySQL 5.6, it is possible to subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use either HASH or KEY partitioning. This is also known as composite partitioning.

This means that we can't determine in what subpartition a record will end up. It's up to MySQL. So I don't think it's wise giving such names to your subpartitions (i.e. january) because no way you're going to know if stuff created on that month will end up there. This because you can't subpartition by MONTH(nav_date) but only by HASH(MONTH(nav_date)) or KEY(MONTH(nav_date)).

So to solve the problem I decided to create a new month column in my table and then I added an index to it. Then I subpartioned by KEY(month) without caring about the subpartition names. This way in MySQL 5.6 I could select the main partition from the FROM clause and the subpartition by specifying the month in the WHERE clause.

Follows the full example:

CREATE TABLE `my_example` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `value` double DEFAULT NULL,
    `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
    `timestamp` datetime NOT NULL,
    `last_modified` datetime NOT NULL,
    `month` tinyint(1) NOT NULL,
    PRIMARY KEY (`id`,`timestamp`, `month`),
    KEY `in_is_deleted` (`is_deleted`),
    KEY `in_last_modified` (`last_modified`),
    KEY `in_timestamp` (`timestamp`),
    KEY `in_month` (`month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE (YEAR(`timestamp`))
SUBPARTITION BY KEY (`month`) 
SUBPARTITIONS 12 (
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION p2014 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015); -- will go through all twelve subpartitions
EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015) WHERE month = 9; -- will look only into one subpartition

Best Answer

Related Solutions

Mysql – selecting the month day and year from a transaction table

Mysql – Partition by year and sub-partition by month thesql

Related Question