Mysql – Query to select specific columns based on current date

MySQLmysql-5.5query

I have a table named 'mrtg' that has four columns:

mysql> select * from mrtg;
+----------+------+------+------+
| city     | day1 | day2 | day3 |
+----------+------+------+------+
| Delhi    |    2 |    6 |    9 |
| Mumbai   |    1 |    3 |    8 |
| Banglore |    4 |    1 |    6 |
+----------+------+------+------+
3 rows in set (0.00 sec)

If current date = '2019-09-01' then I want to select as

SELECT CITY, DAY1 FROM MRTG;

If current date = '2019-09-02' then I want to select as

SELECT CITY, DAY1, DAY2 FROM MRTG;

If current date = '2019-09-02' then I want to select as

SELECT CITY, DAY1, DAY2, DAY3 FROM MRTG;

and so on.

How can I write a query for this?

The values represent day-wise data for a whole month. I do wonder about this type of table structure, but it's the client requirement. I need to find a solution to fetch only the columns from day 1 to the current date.

Best Answer

If you were able to start with a new table I would use this structure to store the data:

CREATE TABLE mrtg (
    city varchar(30)
    , logdate date
    , value int
)

This way you can store data for any time period and work out the days needed from the date.

Since you can't create that we'll have to see if we can modify what you've got in a query. I've had a play and this will get the data into a table format that I believe would be easier to work with:

select data.*
  from mrtg
  left join (
    select city, 1 as day, day1 as datavalues from mrtg
    union all
    select city, 2 as day, day2 from mrtg
    union all
    select city, 3 as day, day3 from mrtg
    #.... 
    #union all
    #select city, 4 as day, day4 from mrtg
  ) data
  on mrtg.city = data.city
  where data.day <= DAYOFMONTH(STR_TO_DATE('2019-08-02','%Y-%m-%d'))

You'll have to add as many union all elements as you have columns but then you can filter on the number of days you want. You will now be able to GROUP BY and perform any calculations you need to.

select data.city, sum(data.datavalues) as TotalToDate
  from mrtg
  left join (
    select city, 1 as day, day1 as datavalues from mrtg
    union all
    select city, 2 as day, day2 from mrtg
    union all
    select city, 3 as day, day3 from mrtg
    #.... 
    #union all
    #select city, 4 as day, day4 from mrtg
  ) data
  on mrtg.city = data.city
  where data.day <= DAYOFMONTH(STR_TO_DATE('2019-08-02','%Y-%m-%d'))
  GROUP BY city

For keeping your main queries cleaner and smaller I'd recommend creating a VIEW for the subquery with all of the UNION ALL statements in.

Related Solutions

How to Display Monthly Attendance Report in MySQL

This type of rotation of data from columns to rows is known as a PIVOT. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression to get the result.

My first suggestion would be to determine if you have a calendar table or a table that contains all of the dates that you want to display. If not, then I would suggest creating one similar to the following:

CREATE TABLE calendar (`Date` datetime) ;

INSERT INTO calendar (`Date`)
VALUES
    ('2013-06-01 00:00:00'),
    ('2013-06-02 00:00:00'),
    ('2013-06-03 00:00:00'),
    ('2013-06-04 00:00:00'),
    ('2013-06-05 00:00:00'),
    ('2013-06-06 00:00:00'),
    ('2013-06-07 00:00:00'),
    ('2013-06-08 00:00:00'),
    ('2013-06-09 00:00:00'),
    ('2013-06-10 00:00:00');

This will make it possible for you to generate a list of all of the dates that you want to display.

Second, you will need to generate the list of each student and each date. You can do this by using a CROSS JOIN between your tbl_admission and the calendar table:

select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a;

See Demo. Once you have this list, then you can use a LEFT JOIN to your existing tbl_absentees table to get the result:

select 
  ca.studentname,
  ca.rollno,
  ca.class,
  max(case when ca.date = '2013-06-01' then coalesce(p.status, 'P') end) `2013-06-01`,
  max(case when ca.date = '2013-06-02' then coalesce(p.status, 'P') end) `2013-06-02`,
  max(case when ca.date = '2013-06-03' then coalesce(p.status, 'P') end) `2013-06-03`,
  max(case when ca.date = '2013-06-04' then coalesce(p.status, 'P') end) `2013-06-04`,
  max(case when ca.date = '2013-06-05' then coalesce(p.status, 'P') end) `2013-06-05`,
  max(case when ca.date = '2013-06-06' then coalesce(p.status, 'P') end) `2013-06-06`,
  max(case when ca.date = '2013-06-07' then coalesce(p.status, 'P') end) `2013-06-07`,
  max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-08`,
  max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-09`,
  max(case when ca.date = '2013-06-10' then coalesce(p.status, 'P') end) `2013-06-10`
from
(
  select c.date, a.studentname, a.rollno, a.class
  from calendar c
  cross join tbl_admission a
) ca
left join tbl_absentees p
  on ca.rollno = p.rollno
  and ca.date = p.date
group by ca.studentname, ca.rollno, ca.class
order by ca.rollno;

See SQL Fiddle with Demo. Of course for your request, you most likely want to query the data based on a date range so you will not want to hard-code the values. If that is the case, then you will need to look at using a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN ca.date = ''',
      date_format(date, '%Y-%m-%d'),
      ''' THEN coalesce(p.status, ''P'') END) AS `',
      date_format(date, '%Y-%m-%d'), '`'
    )
  ) INTO @sql
FROM calendar
where date>='2013-06-01'
  and date <= '2013-06-05';

SET @sql 
  = CONCAT('SELECT ca.studentname,
              ca.rollno,
              ca.class, ', @sql, ' 
            from
            (
              select c.date, a.studentname, a.rollno, a.class
              from calendar c
              cross join tbl_admission a
            ) ca
            left join tbl_absentees p
              on ca.rollno = p.rollno
              and ca.date = p.date
            where ca.date>=''2013-06-01''
              and ca.date <= ''2013-06-05''
            group by ca.studentname, ca.rollno, ca.class
            order by ca.rollno');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo. Both of these queries will give a result similar to:

| STUDENTNAME | ROLLNO | CLASS | 2013-06-01 | 2013-06-02 | 2013-06-03 | 2013-06-04 | 2013-06-05 | 2013-06-06 | 2013-06-07 | 2013-06-08 | 2013-06-09 | 2013-06-10 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|       Naren |      1 |    22 |          A |          A |          A |          A |          P |          P |          P |          P |          P |          P |
|       Srinu |      2 |    22 |          P |          P |          P |          P |          P |          P |          P |          P |          P |          P |
|        Blah |      3 |    22 |          A |          P |          P |          P |          P |          P |          P |          P |          P |          P |

Mysql – query records to fetch by time stamp

Since the seconds are from 0 (1970-01-01 00:00:00 UTC), you should look for every multiple of 60

SELECT * FROM mytable WHERE MOD(TimeStamp,60)=0;

or if TimeStamp is indexed, you can do

SELECT T.* FROM
(SELECT TimeStamp FROM mytable WHERE MOD(TimeStamp,60)=0) M
INNER JOIN mytable T USING (TimeStamp);

Give it a Try !!!

SUGGESTION #1

You should store the timestamp of the minute and index it

ALTER TABLE mytable ADD COLUMN MinuteTimeStamp AFTER TimeStamp;
UPDATE mytable SET MinuteTimeStamp = TimeStamp - MOD(TimeStamp,60);
ALTER TABLE mytable ADD INDEX MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey);

Then, you can do MIN aggregation on MinuteTimeStamp.

SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp;

and use it get those records

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

It was tactfully pointed out that triggers would degrade performance

Perhaps doing INSERTs like this may help

INSERT INTO mytable (UniqueKey,TimeStamp,MinuteTimeStamp) VALUES
(
    uniquevalue,
    UNIX_TIMESTAMP(NOW()),
    UNIX_TIMESTAMP(NOW() - INTERVAL SECOND(NOW()) SECOND)
);

SUGGESTION #2

Since you have over 1000 columns (Ugh), perhaps a table of those minute timestamps would be better.

CREATE TABLE MinuteKeys
(
    MinuteTimeStamp INT UNSIGNED NOT NULL,
    UniqueKey INT UNSIGNED NOT NULL,
    PRIMARY KEY (UniqueKey)
    KEY MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey)
) ENGINE=MyISAM;
ALTER TABLE MinuteKeys DISABLE KEYS;
INSERT INTO MinuteKeys SELECT TimeStamp - MOD(TimeStamp,60),UniqueKey FROM mytable;
ALTER TABLE MinuteKeys ENABLE KEYS;

Then, you could use that table for the aggregation

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM MinuteKeys GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

EPILOGUE

Other suggestions are possible but you should really consider normalization of the table

See my post Too many columns in MySQL as to why