How can I Compare timestamps?

[landysaccount]

Hello.

 

I have a table where I save a record and use time() for the timestamp.

 

create table network_access(
      id  INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      ip  VARCHAR(15) NOT NULL,
      mac VARCHAR(17),
      timevisit INT(11) NOT NULL default '0',
      primary key ( id )
);

 

I'm trying to retreive all records or access for certain days, for ei., I would like to see today's, yesterday's, and so on access.

 

$day    = date( 'd' ); 
$month  = date( 'n' ); 
$year   = date( 'Y' );
$my_timestamp = mktime( 0, 0 , 0, $month, $day, $year ); // create 10/16/2009 at 12:00am

 

I'm trying to retreive the records by doing:

 

"select * from network_access order by timevisit > $my_timestamp"

 

but, get all the records in the table. If I

 

"select * from network_access order by timevisit > $my_timestamp"

 

I get the same thing.

 

How can I compare the timestamp values?

 

Thanks in advanced for your help.

 

 

 

 

[xtopolis]

Make your "timevisit INT(11) NOT NULL default '0'," into a proper timestamp or datetime data type column.  This will make everything a lot easier.  Typically the time is stored in the "YYYY-MM-DD HH:MM:SS" format.

 

After you set that column type to be a datetime/timestamp type you can simply write queries like:

 

Show from the past 24 hours

SELECT * FROM network_access
  WHERE timevisit > DATE_SUB(NOW(), INTERVAL 1 DAY) 
    ORDER BY timevisit ASC

 

Show between 2 days

SELECT * FROM network_access 
  WHERE timevisit BETWEEN $date1 AND $date2 
    ORDER BY timevisit ASC

 

Those queries may not be exact but will point you in the right direction.

[landysaccount]

 

Ok. I understand that but I can't change it,

 

Logically isn't this true:

 

1255662000 < 1255742769

 

The first one is the timestamp for 16-oct-2009 at 12:00am, the second one is a timestamp later that day.

 

So, I guess I have to compare these value with php instead? I didn't want to go through all the records since there are over 3000 records in that table. It will take "some" time to go through the entire table.

 

Any other idea?

 

[Dorky]

if ($whatever1 == $whatever2)
{
//do something
}

or

if ($whatever1 !== $whatever2)
{
//do something
}

the ! indicates false

[GoneNowBye]

never save a formatted time.

 

time in MySQL (unix Epoch) = UNIX_TIMESTAMP() - yeah this works. weirdly not in the query browser, but it does in a code statement.

 

Time in PHP = time()

 

Have fun

 

Also look up the PHP date function there are other functions with it which can get the timestamp out.

 

 

[PFMaBiSmAd]

"select * from network_access order by timevisit > $my_timestamp"

The above query makes little sense. It is only ordering rows based on if that greater-than comparison is false (0) or true (1). All the rows (in the order they are stored in the table) where timevisit is not greater-than $my_timestamp will be selected first, followed by all the remaining rows (in the order they are stored in the table) where timevisit is greater-than $my_timestamp will be selected next.

 

To select the rows where timevisit > $my_timestamp, you need to use a WHERE clause. To order the rows by timevisit, use it an ORDER BY clause -

 

"select * from network_access WHERE timevisit > $my_timestamp ORDER BY timevisit"

 

However, as xtopolis posted, using a Unix Timesetamp has a number of limitations that make it more difficult and slow to do things like get all the values for a specific day or a range of days/separated by each day...