PHP/Mysql wildcard query help

[rscrsc]

Hi I am trying to run a query on using PHP as my front end. I have everything working upto this point, but I am stuck at the most important part of my php/mysql code.

here is what I want to query.
IP_address and Timestamp > these are two tables that I am to be queried when displaying results. Below is a sample of how the database columns look like.

ip_address Timestamp
192.168.1.1 2006-01-23 10:07:35

Sample query that works when both date and time are entered.
select timestamp, signature from acid_event where timestamp = '2006-01-22 12:14:59'

How do I run this same query but without entering the second part (time)

I can run a query for the ip address just fine. The problem is that I want to be able to query for both the IP address and the Timestamp. My question is how to I query the timestamp column just by using the 2006-01-23 and leaving out the time. So just make sure I am being clear if I wanted to do a query for anything that happened on 2006-01-23 from 192.168.1.1 how would I go about that?

thanks for the help in advance.

[arran]

Hey rscrsc,

Try something like:

select timestamp, signature from acid_event where date(timestamp) = '2006-01-22'

arran.

[rscrsc]

I get the following error "error in your sql syntax near '(timestamp) = '2006-01-22' at line 3
any other ideas.

thanks for your response!

[sned]

What about trying something like :

SELECT timestamp, signature FROM acid_event WHERE DATE_FORMAT(timestamp, '%Y-%c-%d') = '2006-01-22'

-sned

see the mysql manual [dev.mysql.com] on date and time functions

[rscrsc]

Thanks for your help Sned. I get an "empty set" message when I run that command, looks like it didnt like the command. Does anyone else have any ideas?

thanks again for everyones help.

[scriptmasterdel]

Howabout something like ...

WHERE substring(timestamp,0,10) = '2006-01-22'

.. in your where clause?

Del

[SeanW]

timestamp is a datetime column so you can't select parts of it.

SELECT timestamp, signature
FROM acid_event
WHERE timestamp >='2006-01-22 00:00:00' AND timestamp <= '2006-01-22 23.59:59'

Sean

[arran]

My original solution using date() works with datetime fields - I'm not sure why it's giving you a syntax error. Could be related to the fact that you are using a (semi) reserved column name (timestamp).

arran.

[coopster]

It could also be your MySQL version as DATE() is only available as of MySQL 4.1.1. DATE_FORMAT should fit the bill though and sned was close, but got the wrong specifier in there with '%c' -- try '%m' instead:

-- 
-- c = Month, numeric (0..12) 
-- m = Month, numeric (00..12) 
--  
SELECT timestamp, signature FROM acid_event WHERE DATE_FORMAT(timestamp, '%Y-%m-%d') = '2006-01-22'

SUBSTRING would work as well, but it would start at position 1:

WHERE substring(timestamp,1,10) = '2006-01-22'

And arran is quite right, it's just not good practice to use reserved words for column names or table names. You may want to consider ALTERing that column name.

[rscrsc]

Figure it out here is what I did in case someone runs into the same problem.

select timestamp, signature from acid_event where timestamp like "2006-01-24%"

thanks for the help!