MySQL: Search for today's records in TIMESTAMP field?
Question by Chematik | 2012-12-25 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?
Related Topics
MySQL: Delete Data from Table - Difference between TRUNCATE, DELETE and DROP
Tutorial | 0 Comments
How to Replace multiple Texts at the same Time
Tutorial | 0 Comments
MySQL: How many records have been found or are affected by my query?
Info | 0 Comments
The Askingbox Search
Info | 0 Comments
MySQL: Combine full text search with LIKE search for words with 3 letters
Tutorial | 2 Comments
MySQL: Display search results on multiple pages
Tutorial | 0 Comments
MySQL: Regular Expressions in MySQL queries
Info | 0 Comments
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 article on askingbox.com. That’s how it’s done.
You can use the following query, where "col" is your TIMESTAMP column/field:
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.
2012-12-27 at 17:45
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 DAYIf 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).
2013-01-08 at 10:51