1 1 Vote

MySQL: Search for today's records in TIMESTAMP field?

Question by Chematik | 25/12/2012 at 10:53

I would like to select all data records, that where created on today's date.

The problem: The column is of the type TIMESTAMP and therefore has no unique value for "today", so, finally the time is different at every line. It would be a very simple solution to format the column as DATE, but I also have to store the time.

How can I nevertheless select all rows in a TIMESTAMP column that have today's date?

ReplyPositiveNegativeDateVotes

Axuter

Show Profile | Message
Avatar
22 Votes

You can use the following query, where "col" is your TIMESTAMP column/field:

... WHERE DATE(col) = CURDATE()

With this, you are rewriting each TIMESTAMP formatted date into a DATE so that you can compare the result with CURDATE(), the current date. And so, you will find the correct matching records.
27/12/2012 at 17:45

ReplyPositive Negative

Computer Expert

Show Profile | Message
Avatar
2Best Answer
2 Votes

Of course, the solution suggested by Axuter is correct and working, but to perform this query, a full table scan is required and all records have to be gone through and calculated (bad for performance).

Better is the folliwing variant:

... WHERE col >= CURRENT_DATE
      AND col <  CURRENT_DATE + INTERVAL 1 DAY

If you have an index on the column, with this solution it can be searched much more efficient. We are simply using the upper and lower bounds with respect to the present day (greater than or equal to today's date and smaller than today's date + 1 day).
08/01/2013 at 10:51

ReplyPositive Negative
Reply

Related Topics

MySQL: Whole Word Search
Open Question | 1 Answer

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own articles on askingbox.com. How to do.