CODEWITHSUNDEEP
mysql
Lee J
Lee J

Reputation: 29

Make a query that will get a specific month from my sales table

In my sales table have the date column and the date is formatted like this January 9, 2018, 5:06 pm. How can I specifically get all the sales made from the month of January 2018 using MySQL?

Upvotes: 0

Views: 119

Answers (3)

Dan
Dan

Reputation: 864

Since the column isn't already in a DATE format, let's assume it has to stay in its current format for the application purposes and add an auxiliary DATE column to select on (because SELECTs will be more optimized this way).

First we add a VIRTUAL column (requires MySQL 5.7+), which is a calculated column that is calculated at runtime but not stored on disk. We don't need to store this column on disk because we're going to index it later. Our VIRTUAL column will convert the application date's format to a MySQL DATE.

Replace str_date with your current date column:

ALTER TABLE `my_table`
ADD `virtual_date` DATETIME AS (STR_TO_DATE(`str_date`, '%M %d, %Y, %l:%i %p')) VIRTUAL AFTER `date`;

Now add the INDEX:

ALTER TABLE my_table ADD INDEX (`virtual_date`);

Now we can perform a properly indexed SELECT:

SELECT *
FROM my_table
WHERE virtual_date >= '2018-01-01'
AND virtual_date <= '2018-01-31'

or

SELECT *
FROM my_table
WHERE virtual_date >= '2018-01-01'
AND virtual_date <= LAST_DAY('2018-01-01')

The MySQL DATE format is YYYY-MM-DD. The latter query is logically simpler; the LAST_DAY() function will give you the last DATE of the month given a DATE or DATETIME value.

Upvotes: 0

Kosh
Kosh

Reputation: 18378

Since

the date is formatted like this January 9, 2018, 5:06 pm

and this is not recognizable time format for MySQL I belive the solution is:

SELECT * FROM table WHERE date like 'January%2018%';

I know this is not a "best practice" as well as storing time in a wrong type.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

assuming you date column is a date data type columns you can use year() and month() function 

select * from my_table 
where year(my_date_col) = 2018
and month(my_date_col) = 1

Upvotes: 1

Related Questions