Announcement

Announcement Module
Collapse
No announcement yet.

INT vs. TIMESTAMP data types

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • INT vs. TIMESTAMP data types

    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!

  • #2
    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

    Comment


    • #3
      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.

      Comment


      • #4
        Thanks for clearling that up and the helpful tips chaps!

        Comment

        Working...
        X