[Resolved] Displaying current dates records from timestamp

[Stu]

Hi 🙂

In my table, I have a column which is set as a timestamp. What I am needing to do, is to get all the records that are for the current date (for ease of explanation at this stage). I expected to be able to use LIKE 20041022% as part of my querying but that isn't working.

The database is MySQL.

Any suggestions would be greatfully received 🙂

[xblue]

just to make sure: from your example i assume you or not talking about unix timestamp, right?

from version 4.1.1 there is the DATE() function which "Extracts the date part of the date". it then should be simply

WHERE DATE(timestamp_column) = CURDATE()

with older versions, there may be different approaches. you could try

WHERE timestamp_column BETWEEN CONCAT( CURDATE( ) , '000000' ) AND CONCAT( CURDATE( ) , '235959' )

(this does not look particularly elegant to me, though, and I cannot seem to remember whether BETWEEN is exclusive or inclusive the border values.)

another one:

WHERE timestamp_column BETWEEN CURDATE( ) - INTERVAL 1 SECOND AND CURDATE( ) + INTERVAL 1 DAY

(same doubts about the borders here)

however, there may be a lot more ways which may be more efficiently.

[edit: uh, I didn't mean to use the non-existing DATE() function in the pre-4.1.1 examples. copy&paste error 🙁]

[Stu]

Fantastic!

Thanks very much. Your code just needed a little tweaking (replacing all the date()'s with date_format()). Easy enough to fix!

Once again, thanks very much 🙂