Searching database by date/time.

searching date/time

[Verity9802]

Hi all,

I'm working in this project that involves sensors sending in data every 3 hours, and the sensor data includes unix timestamps. These data is stored in a DB2 database and I need some PHP help in sorting these data out. Basically, I would like the user to query the database for a specific period of time and get back the relevant data for that period. A simple example would be :

From: dd/mm/yyyy
To : dd/mm/yyyy

The unix timestamps send by the sensors are stored in a table under the column name Timestamps. I suppose the easiest way to go about solving this problem would be to query this the Timestamp column but I'm not too sure how to go about doing this. How do I go about converting the data from the example showed above to a PHP query? I believe the most relevant example scenario would be a website analysis where the webmaster is able to find out the hits for a specific period of time. Any sort of help would be greatly appreciated. Cheers.

[httpwebwitch]

use mktime() [ca3.php.net] to construct a Unix timestamp for the beginning and end of the period.

$starttime=mktime([int hour [, int minute [, int second [, int month [, int day [, int year [, int is_dst]]]]]]])

$endtime=mktime([int hour [, int minute [, int second [, int month [, int day [, int year [, int is_dst]]]]]]])

Then construct your query to look for timestamps that fall between them.

$query="SELECT * FROM tablename WHERE timestamp<=".$endtime." AND timestamp>=".$starttime."";

[Verity9802]

Thanks, that worked just fine. Cheers again!