Datetime / Timestamp to be handled in PHP-MySQL

Question

I would like to know which one is the best option between datetime and timestamp to be handled in php-mysql.

The things I'll need to do will basically be: order by date both ways, and select all the rows with a certain year. Is it even possible?

Can I select all the rows with a certain year or month in php-mysql? Would be like SELECT * FROM my_table WHERE my_date.year = 2014 for example?

What is "best" depends on the situation and is not a useful general question, so I removed that part. — poolie, May 07 '16 at 16:03

score 1 · Answer 1 · edited May 23 '17 at 12:08

1

Here is an OOP way of doing what you want, references are below.

namespace Connection;
class Database
{
    protected $Con;
    public function __construct()
    {
        $this->Con = new PDO("MySQL:host=X;dbname=X", "user", "pass");
    }
    public function GetInstance()
    {
        return $this->Con;
    }
}

// end class

$sql = "SELECT * FROM Table WHERE DATE_FORMAT(Column, %Y) = 2014";
use Connection;
$smpt = ((new Database)->GetInstance()
                      ->Prepare($sql))
                      ->execute();
foreach ($smpt->fetchAll() as $row):
    echo $row['Column'];
endforeach;

Look at this StackOverflow answer for the difference between DateTime and TimeStamp.

References:

Date Format

edited May 23 '17 at 12:08

Community

1
1

answered May 07 '16 at 13:01

Jaquarh

6,493
7
34
86

Thanks a lot, very exhaustive answer :) – May 07 '16 at 13:02
1

No problem, good luck with your project! – Jaquarh May 07 '16 at 13:04
1

Wrapping the column reference in a function in a condition *disables* MySQL from using an index range scan operation. A *much* better pattern, in terms of database performance is to use conditions on bare columns. e.g. **`WHEREt.dtcol >= '2014-01-01 AND t.dtcol < '2015-01-01'`** – spencer7593 May 07 '16 at 13:05
I assumed that OP only wanted to match the **Year**, although that would be a better solution in terms of the SQL query. Nice addition @spencer7593 – Jaquarh May 07 '16 at 13:07

score 1 · Accepted Answer · answered May 07 '16 at 13:17

For optimal database performance, use conditions on bare columns. Don't wrap them in functions. For example, assuming dtcol is datatype DATE, DATETIME or TIMESTAMP...

  SELECT t.dtcol 
    FROM mytable t
   WHERE t.dtcol >= '2014-01-01' 
     AND t.dtcol  < '2015-01-01'

This could also be expressed:

   WHERE t.dtcol >= '2014-01-01' 
     AND t.dtcol  < '2014-01-01' + INTERVAL 1 YEAR

If there is an index with dtcol as the leading column, MySQL can use an index range scan operation.

DON'T do this:

   WHERE YEAR(t.dtcol) = 2014

This will force MySQL to evaluate the function on every row in the table, and the compare the result from the function to the literal.

You won't notice any performance difference on small, test tables. But you will notice it (in a slowly painful way) on large tables in a busy system.

There are some significant differences between DATETIME and TIMESTAMP.

DATETIME supports a much larger range of values.

Currently, TIMESTAMP is implemented as a Unix-style 32-bit "seconds since the beginning of the epoch", with a range of values from '1970-01-01 00:00:01' up through some time on '2038-01-19'.

Datetime / Timestamp to be handled in PHP-MySQL

2 Answers2