[SOLVED] Probably simple question about dates.

[RedMist]

I have data stored in the following format...

 

year, month, total (all smallints)

 

e.g.

 

2008 9 6

2008 8 6

2008 7 50

2008 6 12

2007 5 77

2007 3 19

 

How can I write a query to list the last 6 months totals?

 

MySQL 5.1

[PFMaBiSmAd]

Your first step would be to use a DATE data type. Your current scheme will require a slow complex query to accomplish any comparison, sorting, or retrieval of the data. Your scheme also requires 6 bytes to store what a DATE type stores in 3 bytes.

[RedMist]

Unfortunately, I have inherited this schema so I cannot change it.

[PFMaBiSmAd]

Backup your database; add a new DATE type column to the table; populate it with the equivalent date values from the existing data in the three columns; modify the query that inserts into the three columns to use a mysql STR_TO_DATE() function to put the same data into the new DATE type column; modify any query that retrieves from the three columns to use a mysql DATE_FORMAT() function to retrieve from the DATE type column in any format you want; test everything; delete the three smallint columns. To populate the new DATE type column from the existing data, use the mysql STR_TO_DATE() function in a simple UPDATE query.

 

[nadeemshafi9]

Unfortunately, I have inherited this schema so I cannot change it.

then you must take the dates and work with them in php, make a proper date time and use the examples in php.net/date and php.net/time

[nadeemshafi9]

Backup your database; add a new DATE type column to the table; populate it with the equivalent date values from the existing data in the three columns; modify the query that inserts into the three columns to use a mysql STR_TO_DATE() function to put the same data into the new DATE type column; modify any query that retrieves from the three columns to use a mysql DATE_FORMAT() function to retrieve from the DATE type column in any format you want; test everything; delete the three smallint columns. To populate the new DATE type column from the existing data, use the mysql STR_TO_DATE() function in a simple UPDATE query.

 

 

cmon man it requires about a 10,000 records to make a 5 sec difference in processing and its numerical so it will be quick, rember were XP developers in web apps that are comercial

[PFMaBiSmAd]

This could be done in a query with the existing scheme, but it will be slow as the two columns (I guess the day is not include) need to be retrieved from every row in the table and compared. A DATE type only requires that a single value be compared. Doing this in some slow parsed/tokenized/interpreted php code will be even slower.

 

It is always better to fix underlying problems as early as possible. Using a DATE type will also allow you to use 20-30 mysql date functions that will make doing everything with this date easier.

 

Fixing this problem will result in a simple WHERE clause in the query to get the data  -

 

... WHERE the_date_column >= CURDATE() - INTERVAL 6 MONTH

[RedMist]

Is there no way to "make" a date, on the fly, from the month and year numbers, and use that in a WHERE?

 

I have scoured the MySQL online manual, and I'm sure there must be a way.

[RedMist]

I didn't want to show what i've tried already, because I thought there might be a better way, but I was trying something like htis.....

 

SELECT *

FROM stats

WHERE DATE(CONCAT('01', '-', month, '-', year)) > datesub(curdate(), interval 6 month)

 

But obviously it doesn;t work, and I could not get it to...

[PFMaBiSmAd]

I would recommend using the mysql STR_TO_DATE() function to form the left-hand side of the comparison.

[RedMist]

In case anyone reads this thread, and needs to know the answer, this is what I did....

 

SELECT *
FROM mytable
WHERE STR_TO_DATE(concat(year,'/',month,'/01'), '%Y/%m/%d') > date_sub(curdate(), INTERVAL 6 month)

 

Thanks for the help