Selecting Records Based On Time!

[SharifTK]

How would I go on about selecting a record between a given time period in unix timestamp format and the current time?

For example:

$old_time = 1057106550;
$new_time = time();

How would I get any records made between the $old_time and $new_time vars? Note that my tables has a datetime field in it called "timestamp". Thanks.

[r937]

select foo
  from yourtable
 where `timestamp` 
       between from_unixtime(old_time) 
             and now()

note: this uses mysql's NOW() function

[SharifTK]

Ok let me just post what exactly i am trying to do:

I have 4 tables:

news:
id (int), timestamp (datetime)

articles:
id (int), timestamp (datetime)

reviews:
id (int), date_reviewed (date)

previews:
id (int), date_previewed (date)

I am basically trying to get all records created in theses tables between one given time and the current time.

My complete code:

$sql = "
SELECT 
t1.id, t2.id, t3.id, t4.id 
FROM 
news t1, articles t2, reviews t3, previews t4 
WHERE 
UNIX_TIMESTAMP(t1.timestamp) BETWEEN FROM_UNIXTIME('1087950019') AND now() 
AND 
UNIX_TIMESTAMP(t2.timestamp) BETWEEN FROM_UNIXTIME('1087950019') AND now() 
AND 
UNIX_TIMESTAMP(t3.date_reviewed) BETWEEN FROM_UNIXTIME('1087950019') AND now() 
AND 
UNIX_TIMESTAMP(t4.date_previewed) BETWEEN FROM_UNIXTIME('1087950019') AND now()";
$query = mysql_query($sql);

if(mysql_num_rows($query) != 0)
{
echo 'there are new records';
} 

Does that explain better?

[r937]

WHERE
UNIX_TIMESTAMP(t1.timestamp) <-- converts a datetime value into a unix timestamp value
BETWEEN FROM_UNIXTIME('1087950019') <-- converts a unix timestamp value into a datetime value
AND now() <-- a datetime value

so you should leave the t1.timestamp as a datetime value

did i mention that your choice of column name may be misleading

[SharifTK]

Well it looks like your query and my query is no different. So I tried mine again and again, I inserted a new record in the news table and tried to see if any rows were returned, but nothing!

[r937]

okay, let's get to the bottom of this

do a dump with phpmyadmin or whatever tool you have

i would like to have the CREATE TABLE statement, plus a few sample rows of data

then i can test the sql and determine exactly where it's not working

[SharifTK]

Sure, actually I think it would be better if I just do one table instead of 4 because it's just unnecassary to create a one severely complex query when 4 simple could do. Here is the table dump:

CREATE TABLE news (
  id int(11) NOT NULL auto_increment,
  timestamp datetime NOT NULL default '0000-00-00 00:00:00',
  authorid int(11) NOT NULL default '0',
  title text NOT NULL,
  teaser text NOT NULL,
  article text NOT NULL,
  icon_id int(11) NOT NULL default '0',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

[r937]

OH MY $DEITY

i am so sorry

i just looked at your query in detail for the first time

your problem is in the data, not the sql

and yes, now that you mention it, it would be better to combine those tables

let me know when you've done that and then let's try the sql again afterwards

[SharifTK]

I fixed it. Just did 4 seperate queries. And did not use the FROM_UNIXTIME().

[SharifTK]

Nope didn't work! It shows ALL records instead...