mysql between query

Question

I have this scheme:

--------------------------
| ID | data  | timestamp |
--------------------------

how i can make a query to show only today values? I now i need to use the BETWEEN statement, any idea?

What format is the timestamp? is it a MySQL DATETIME or a Unix timestamp? — konsolenfreddy
– konsolenfreddy, Commented Mar 11, 2011 at 20:42

Brad Christie · Accepted Answer · 2011-03-11 19:53:07Z

1

SELECT   *
FROM     mytable
WHERE    DATE(timestamp) = CURDATE()

Or so I think.

answered Mar 11, 2011 at 19:53

Brad Christie

102k16 gold badges160 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

I've inserted some bulk data into my db, but that query doesn't work.

@greenbandit: What's the datatype of "timestamp"?

varchar, since your last respond... seems like i need to change it.

@greenbandit: I would agree, especially for indexing purposes. But you can cast/convert it to a datetime for comparison, though pretty slow.

@greenbandit: glad to hear. ;-)

Marc B · Accepted Answer · 2011-03-11 19:53:54Z

1

select ID, date, timestamp
from yourtable
where DATE(`timestamp`) = CURDATE()

answered Mar 11, 2011 at 19:53

Marc B

362k44 gold badges433 silver badges508 bronze badges

no rows? or rows filled with blanks? Obviously, check if you've got any records that have today's date for the timestamp.

thanks, solved, bad field type, set up to timestamp and works like a charm.