CODEWITHSUNDEEP
phpmysqlmysqli
Mubin
Mubin

Reputation: 4425

Filter data by date?

I've column that contains data like so,

07/2002
05/2005
04/2000

month/year

can I filter out data using query, i.e 

SELECT * FROM `table` WHERE `fr` < '07/2002'

it should return 04/2000

is't possible using MySQL or I've to use other language to select and filter out data like PHP?

Upvotes: 3

Views: 1113

Answers (2)

amdixon
amdixon

Reputation: 3833

plan

  • use str_to_date to convert the start of month values to dates and compare

query

select *
from `table`
where str_to_date(concat('01/', fr), '%d/%m/%Y') 
      < 
      str_to_date('01/07/2002', '%d/%m/%Y')
;

output

+---------+
| fr      |
+---------+
| 04/2000 |
+---------+

sqlfiddle

note

while the above is a solution to the question as asked, it is just dealing with the symptoms not the underlying issue.

the real issue is the storage type being used to store date information consider using actual dates to store this information. this causes the following symptoms :

symptoms

  • complexity : we have to perform further manipulations to transform into the date type we can use
  • performance ( see above )
  • maintenance ( see this question )

we can instead fix this issue where it is caused by changing the storage type to correctly reflect the semantic contents ( its date information and should be able to be compared in this way simply )

fix

alter table `table` add column fr_datetype date not null;

update `table`
set fr_datetype = str_to_date(concat('01/', fr), '%d/%m/%Y')
;

-- test conversion to date type
select 
'error converting' as test_conversion
from `table`
where concat(lpad(extract(month from fr_datetype), 2, '0'), '/', extract(year from fr_datetype)) <> fr
;

-- only finalise this based on successful completion of above select ( no rows returned )
alter table `table` drop column fr;
alter table `table` change column fr_datetype fr date not null;

simplified solution

select *
from `table`
where fr < '2002-07-01'
;

Upvotes: 2

Martin Jo&#243;
Martin Jo&#243;

Reputation: 325

Use MySQL built in date format, so you can perform queries like this:

WHERE DATE_FORMAT(date_field, '%m') < 7
AND DATE_FORMAT(date_field, '%y') < 2002

Or a simpler solution is to use timestamps (that stores seconds), and you can do things like:

WHERE timestamp_field < 1027407492

Or if you'd like to use dates as you're using it now (not recommended), store them in two columns. One for month and other one for year, then you can query like this:

WHERE month_field < 7
AND WHERE year_field < 2002

I recommend timestamps.

Upvotes: 0

Related Questions