Which is the best way to check if timestamp/datetime belongs to a specified date

Question

Sometimes I have a datetime or timestamp columns in the database and, for example, I need to select all records with timestamp updated today.

I usually do it like this:

SELECT * FROM mytable WHERE CAST(TS AS DATE) = CURDATE();  
SELECT * FROM mytable WHERE CAST(TS AS DATE) = '2009-11-01';

Also, it's possible to use DATE() function instead of cast:

SELECT * FROM mytable WHERE DATE(TS) = CURDATE();

The question is which way is more correct and faster? Because I'm not sure CAST for all records is a very good idea, maybe there is a better way...

Answer 1

I would think its faster to actually not run a function on the database column but rather do something like this

SELECT * FROM mytable WHERE TS >= UNIX_TIMESTAMP('2009-11-01 00:00:00') AND TS <= UNIX_TIMESTAMP('2009-11-01 23:59:59');

That way the DB will only need to run the two functions one time each and can use the index of the column TS.

Answer 2

Run some tests.

I know our Data Warehouse uses the DATE() method and they process many millions of transactions a day, so it cannot be too bad.

Answer 3

assuming you have an index on ts, this will be the fastest way because it will be able to use that index:

SELECT *
  FROM mytable
 WHERE ts >= CURDATE()
   AND ts < (CURDATE() + INTERVAL 1 DAY)

Answer 4

Ok, I made some tests, here are results. First value is with index on TS column, second value is without index on TS column.

SELECT * FROM parts WHERE CAST(TS AS DATE) = DATE('2009-10-01');

2.1 sec, 2.1 sec

SELECT * FROM parts WHERE DATE(TS) = DATE('2009-10-01');

2.1 sec, 2.1 sec

SELECT * FROM parts WHERE TS >= DATE('2009-10-01') AND TS < (DATE('2009-10-01') + INTERVAL 1 DAY);

0.1 sec, 2.15 sec

SELECT * FROM parts WHERE TS >= '2009-10-01' AND TS < '2009-10-01 23:59:59';

0.1 sec, 2.15 sec

---

So, as you can see, there is no difference if we don't have an index on TS column. But there is a very big difference when we have index. When we use CAST() or DATE() on indexed column, the index can not be used anymore, so we get bad results.

As for me, I would choose this solution:

SELECT * FROM parts WHERE TS >= DATE('2009-10-01') AND TS < (DATE('2009-10-01') + INTERVAL 1 DAY);

I think it's the most elegant.

PS. I'm still looking for even better solutions, so if you have one - please share.