# INT vs. TIMESTAMP data types

**URL:** <https://forums.percona.com/t/int-vs-timestamp-data-types/712>\
**Category:** Other MySQL® Questions\
**Created:** [April 11, 2008, 2:56pm UTC](https://forums.percona.com/t/int-vs-timestamp-data-types/712 "2008-04-11T14:56:49Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Speeple](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Speeple](https://forums.percona.com/u/Speeple)\
**Post date:** [April 11, 2008, 2:56pm UTC](https://forums.percona.com/t/int-vs-timestamp-data-types/712/1 "2008-04-11T14:56:49Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![carpii](https://avatars.discourse-cdn.com/v4/letter/c/b5ac83/32.png) [@carpii](https://forums.percona.com/u/carpii)\
**Post date:** [April 11, 2008, 7:31pm UTC](https://forums.percona.com/t/int-vs-timestamp-data-types/712/2 "2008-04-11T19:31:35Z")

</div>

It will not be able to use the index for the YEAR and MONTH parts.  
But it will still use the index to narrow down the author

---

<div class="post-metadata">

**Author:** ![Hubert\_Roksor](https://avatars.discourse-cdn.com/v4/letter/h/8baadc/32.png) [@Hubert\_Roksor](https://forums.percona.com/u/Hubert_Roksor)\
**Post date:** [April 12, 2008, 2:05am UTC](https://forums.percona.com/t/int-vs-timestamp-data-types/712/3 "2008-04-12T02:05:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Speeple](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Speeple](https://forums.percona.com/u/Speeple)\
**Post date:** [April 12, 2008, 5:37am UTC](https://forums.percona.com/t/int-vs-timestamp-data-types/712/4 "2008-04-12T05:37:33Z")

</div>

Thanks for clearling that up and the helpful tips chaps!