SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
Thread: Selecting Records Based On Time!
Hybrid View
-
Jun 24, 2004, 17:04 #1
- Join Date
- Jan 2004
- Location
- New York, NY
- Posts
- 621
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Selecting Records Based On Time!
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.SK
-
Jun 24, 2004, 17:11 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:select foo from yourtable where `timestamp` between from_unixtime(old_time) and now()
-
Jun 24, 2004, 17:32 #3
- Join Date
- Jan 2004
- Location
- New York, NY
- Posts
- 621
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
PHP 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';
}
SK
-
Jun 24, 2004, 21:08 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jun 25, 2004, 12:23 #5
- Join Date
- Jan 2004
- Location
- New York, NY
- Posts
- 621
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
SK
-
Jun 25, 2004, 15:11 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jun 25, 2004, 17:11 #7
- Join Date
- Jan 2004
- Location
- New York, NY
- Posts
- 621
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
Code: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;
SK
-
Jun 25, 2004, 17:46 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jun 25, 2004, 18:34 #9
- Join Date
- Jan 2004
- Location
- New York, NY
- Posts
- 621
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I fixed it. Just did 4 seperate queries. And did not use the FROM_UNIXTIME().
SK
-
Jun 25, 2004, 20:41 #10
- Join Date
- Jan 2004
- Location
- New York, NY
- Posts
- 621
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Nope didn't work! It shows ALL records instead...
SK
Bookmarks