| INT vs. TIMESTAMP data types [message #2934] |
Fri, 11 April 2008 16:26  |
 Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
Hi,
Currently for a blog platform I have:
year=YEAR
month=TINYINT
day=TINYINT
timestamp=INT
I was contemplating using the TIMESTAMP type.
To get previous usage like yearly monthly etc archives I would do:
WHERE author=4 AND YEAR(timestamp)=2008 AND MONTH(timestamp)=4
Would this utilize an index on INDEX(author, timestamp) or would the functions YEAR() and MONTH() which extract kill the index usage?
Cheers!
Martin Gallagher | Speeple: The latest news
|
|
|
|
| Re: INT vs. TIMESTAMP data types [message #2937 is a reply to message #2934 ] |
Sat, 12 April 2008 03:35  |
Hubert Roksor
Messages: 3
Registered: April 2008 |
Junior Member |
|
|
You can work around using YEAR() by converting the date boundaries to timestamps. For instance, that clause can use your whole index:
WHERE author=4
AND timestamp BETWEEN UNIX_TIMESTAMP('2008-04-01') AND UNIX_TIMESTAMP(LAST_DAY('2008-04-01'))
Of course, you can also convert the dates using your favourite programming language if you prefer.
|
|
|
|
Members
Search
Help
Register
Login
Home
