How to define YYYY-MM columns in MySQL

I have a need to store only YYYY-MM in a table column within MySQL.  What is the best format to use to accomplish this?  This field will be matched against dates entered by users to get a range of dates from the table.  For example, the user enters 2010-02 to 2010-04, I'd like to select
the rows from table that are in and between these dates.

COL1             COL2         ROW
2009-11         value1       0
2009-12         value2       1
2010-01         value3       2
2010-02         value4       3
2010-03         value5       4
2010-04         value6       5

So if user enters range of 2010-02 to 2010-04, I'd return ROWS 3,4,5
mamusciaAsked:
Who is Participating?
 
thedwillCommented:
YYYY-MM
Year = substr(date,0,4)
Month = substr(date,5,2)

Select * from table where substr([datecolumn]0,4) = substr([yourstartdate]0,4) AND  
substr([datecolumn],5,2) >= substr([yourstartdate],5,2) AND
substr([datecolumn],5,2) <= substr([yourenddate],5,2)

datecolumn = the column of the table to reference
yourstartdate = begin date
yourenddate = end date

0
 
tdotCommented:
Hi mamuscia
Unix time stamp
0
 
mamusciaAuthor Commented:
Can you give me an example of how to use a Unix time stamp for this?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
tdotCommented:
0
 
mamusciaAuthor Commented:
This article didn't explain the use of unix timestamps.  I changed my table back to use DATE and will simply ignore the DAY part fo the date for my purposes, so that I can use a query that goes after YEAR(col1) and MONTH(col1) to get matches.
0
 
mamusciaAuthor Commented:
Duh, thanks.  Sometimes the obvious is not so obvious.
I think I'll use this so I can keep my columns as varchar.
0
 
thedwillCommented:
Yea - the whole timestamp <--> php <--> mysql datetime is quite code cumbersome sometimes.  I'm sure there is a 'right' way to do it, but sometimes the simpler solutions make more sense than  Convert-In and store - read and Convert back to display...
Thanks
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.