I have a mysql table with year (YEAR(4)) and month (TINYINT) columns. What is the best way to select all records in a range, e.g. 2009-10 .. 2010-02 ?
views:
73answers:
3
+1
A:
Use:
WHERE (year = 2009 AND month >= 10)
OR (year = 2010 AND month <= 2)
...or, using UNION ALL:
SELECT t.*
FROM TABLE t
WHERE year = 2009 AND month >= 10
UNION ALL
SELECT t.*
FROM TABLE t
WHERE year = 2010 AND month <= 2
UNION ALL is faster than UNION, but won't remove duplicates.
OMG Ponies
2010-02-28 01:00:03
But what if you have from October 2007 to February 2010? You cannot just substitute the dates.
Daniel Vassallo
2010-02-28 01:02:52
@Daniel Vassallo: I would store the values as DATETIME, which would alleviate complications for queries like what you suggest. If needed, I'd create a view to break out the day/month/year.
OMG Ponies
2010-02-28 01:10:39
@OMG: Yes, DATETIME would be ideal, but I think the OP does not have a DAY value. Just month and year.
Daniel Vassallo
2010-02-28 01:13:55
@Daniel: That is your assumption, not fact.
OMG Ponies
2010-02-28 01:22:06
Let's consider it an "educated guess" :)
Daniel Vassallo
2010-02-28 01:25:59
@OMG,@Daniel There's no 'day' in the table. Every record represents a period of month, for example 2010-02. If I use a single `DATE` column, it would be 2010-02-00 which seems a bit strange.
eugene y
2010-02-28 09:57:07
+1
A:
You may want to consider creating another column called year_month which stores the date in YYYYMM format, such as 201002 for February 2010. Make sure to create an index on it, and you may also create two triggers to automatically update the column ON INSERT and ON UPDATE.
Then you would be able to use:
WHERE year_month BETWEEN 200710 AND 201002
This would use the index on year_month.
Daniel Vassallo
2010-02-28 01:07:41
+2
A:
Here is a generalized solution:
Select...
From ...
Where ( Year = <StartYear> And Month >= <StartMonth> )
Or ( Year > <StartYear> And Year < <EndYear> )
Or ( Year = <EndYear> And Month <= <EndMonth> )
Thomas
2010-02-28 01:09:31