[Resolved] I feel like a retard: UNIX TIMESTAMP problem

SpanKie

Well, the main problem is I'm retarded and can't figure this out. Its probably the most easiest thing also. I've look through the documentation and a few website, I just can't find anything to help with this dump query for MySQL using PHP.

What I'm trying to do is basically read a log (which is stored in a mysql db). I want to display all entries for todays date. The dates in the table are stored in a UNIX TIMESTAMP, the feild is an INT(10).

Here is what I have for my query

$date = time();
$check_log = "SELECT * FROM log WHERE date = ".$date." AND page = '".$page."'";

Now, looks right ... but its not returning any records. I know there is records within the log table for today, just returns none. I have a feeling that it has to do with the TIME part. How can I have PHP/MYSQL just use the date and not the time.

Any help would be appriciated .. I'm just about to go bald over this simple thing ... just can't figure it out.

Thanks in advance!!

rinjani

I think the Mysql timestamp is the same as unix so something like this ought to work just to get a result them you can refiine it as needed. This should match everything for the current day:

$sel_date = date('Y-m-d');

$sql = "SELECT * FROM logtable WHERE timestamp LIKE '" . $sel_date . "%'";

rinjani

TheDefender

In order for what you want to do to work, everything including the month, day, year, hour, minutes and seconds would need to be exactly equal.

Look at the [man]date[/man] function in PHP and the DATE_FORMAT in MySQL, so you can make comparisons just using the month, day and year.

SpanKie

yeh, I wanna try to eliminate the time ...

rinjani

There are also a bunch of MYSQl date/time functions but they don't always work for me. I have had sucess and failure with
SELECT CURDATE()

SELECT CURDATE() + 0;

but it just doesn't always give me the correct result e.g.

SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 0 DAY) <= date_col;

Good luck

rinjani

TheDefender

Yeah, here's the link to the MySQL Manual for the date and time functions. Like I said, use DATE_FORMAT.

SpanKie

	$sel_date = date('Ymd');
	$check_log = "SELECT DATE_FORMAT(date, '%Y%m%d') as log_date, page FROM log WHERE log_date = ".$sel_date." AND page = '".$page."'";

I think I'm about to call it a day ... had enough of PHP with date/time stuff ... LOL

Still no workie ... 🙁
I just don't get it ... I tried several things and several functions for php/mysql.

Thanks for help everyone

TheDefender

You know what, my bad... FROM_UNIXTIME is the one you want... Sorry about that.

SpanKie

yup, got it now ... thanks again

wrapped both DATE_FORMAT(FROM_UNIXTIME()) and got the results I needed.

Now, just need to play around with the time part.

TheDefender

You don't even need to wrap the DATE_FORMAT around the FROM_UNIXTIME, because the FROM_UNIXTIME has a format arguement that uses the same attributes as DATE_FORMAT. Check out the example.

SELECT FROM_UNIXTIME(date,'%Y%m%d')........;

SpanKie

yeh, that was dumb of me. I have removed the DATE_FORMAT.

Now adding to this query, I have to figure out how to display only those records that are more than 1 hour older. So basically if the current time is 12:00pm .. only records before 11:00am return in the query on todays date.

Should be fun, considering the date part kicked my ass. At least I have a good start now.

Thanks again, much appriciated.

TheDefender

Same thing applies in all aspects... date() function, and FROM_UNIXTIME... Except, now you are wanting to deal with only te minutes and hours....

Actually, you're probably better off comparing the timestamps themselves, and then converting the output... (IE, you know 1 hour is 3600 seconds, so you can compare the timestamp minus 3600 (for example), to make the comparison...)

Either way, come on back if you need any more help.

SpanKie

thanks, I will post back my results..