It is possible to method to choose rows from the DB in which the timestamp is within a particular year? I do not possess a specific timestamp, only a range (ex. all timestamps inside the year 2009). It is possible to method of doing this? How else might Time passes about doing something similar to this? Interesting help!



WHERE timestamp_col BETWEEN STR_TO_DATE('2009-01-01', '%Y-%m-%d') 
                        AND STR_TO_DATE('2009-12-31', '%Y-%m-%d')

Any functions carried out on the column imply that a catalog, if a person is available for your column, cannot be used.

I made use of the STR_TO_DATE function to make sure that whatever date provided like a string might be construed by MySQL like a TIMESTAMP.


Use FROM_UNIXTIME such as this:

  FROM_UNIXTIME(my_timestamp, '%Y') AS year
  FROM_UNIXTIME(my_timestamp, '%Y') = 2009;

Where 'my_timestamp' may be the title of the timestamp column.

Alternatively you may also convert it to some DATETIME

Should you convert it to datetime it can be done using the mysql DATE_FORMAT function which enables you to definitely have a DATETIME and format it as being to start dating ?. Then group with that column.

  private function _formatDate() {
    if ($this->_granularity == 'month') {
      return '%y/%M';
    }elseif($this->_granularity == 'day') {
      return '%y/%M/%d';

  public function getmyquery() {
    $query = "
 SELECT count( * ) as visits, DATE_FORMAT( `myOriginalDateField` , '".$this->_formatDate()."' ) AS mydate
 FROM `mys`
 WHERE id = ".$this->_Id."
 GROUP BY mydate
 ORDER BY mydate ASC
    return $query

As easy as:

SELECT * FROM table WHERE YEAR(timestamp) = '1999'

An essential accessory for the superb suggestions already given here: should you intend on performing this question as part of rendering your website (instead of running this like a one-off report), you need to really consider performance. Indexes will not assist you to much if you are publish-processing the column value having a function before evaluating it to something.

For the reason that situation, I'd consider developing a separate database column that consists of Only the year, or simply the month, etc.