CODEWITHSUNDEEP
phpmysql
Comum
Comum

Reputation: 453

Ordered SQL timestamp extraction

First of all, I've tried searching for this subject and got nowhere (can't seem to find the proper key words), so if this is a duplicate, please do tell.

I've been trying to fetch some time_stamp from my database and getting them ordered as intervals. For example I run a query such as 

"SELECT `time_stamp` FROM registo albufeira ".$nome."` WHERE `cota` > '".$piInfo['cota max']."'"

and use a php code to arrange intervals of it.

For example if I have the time_stamps that correspond to the following dates:

2014-12-30 23:51:00
2014-12-30 23:52:00
2014-12-30 23:53:00
2014-12-30 23:54:00
2014-12-31 01:35:00
2014-12-31 01:36:00
2014-12-31 01:37:00

I'd get an intervals array like:

[0] => Array
    [0] => 2014-12-30 23:51:00
    [1] => 2014-12-30 23:54:00
[1] => Array
    [0] => 2014-12-31 01:35:00
    [1] => 2014-12-31 01:37:00

I have managed to get the php code to get the wanted result, however I feel like this can be achieved using a single SQL query. Can anyone point me in the right direction?

Upvotes: 1

Views: 27

Answers (2)

axiac
axiac

Reputation: 72276

Try this:

SELECT DATE(time_stamp) AS `day`, MIN(time_stamp) AS first, MAX(time_stamp) AS last
FROM `registo albufeira`      # fix the table name
WHERE `cota` > '...'          # put your condition here
GROUP BY DATE(time_stamp)

It will produce something like:

+------------+---------------------+---------------------+
| day        | first               | last                |
+------------+---------------------+---------------------+
| 2014-12-30 | 2014-12-30 23:51:00 | 2014-12-30 23:54:00 |
| 2014-12-31 | 2014-12-31 01:35:00 | 2014-12-31 01:37:00 |
+------------+---------------------+---------------------+

then you can store it in the desired format from the PHP code.

$data = array();
while ($row = mysqli_fetch_assoc($result)) {
    $data[] = array($row['first'], $row['last']);
}

The code above will produce the data structure you dumped in the question but I strongly recommend you use string keys ('day', 'first', 'last' whatever you want) instead of numeric ones:

$data = array();
while ($row = mysqli_fetch_assoc($result)) {
    $data[$row['day']] = $row;
}

Add a print_r($data); and check the difference.

Update:

If the length of an interval is not a day but 300 seconds then replace DATE(time_stamp) with UNIX_TIMESTAMP(time_stamp) DIV 300.

Upvotes: 1

donald123
donald123

Reputation: 5749

SQL Query could be

SELECT 
  DATE_FORMAT(`time_stamp`,'%y-%m-%d') AS day_id, 
  MIN(`time_stamp`) AS start_ts,    MAX(`time_stamp`) AS end_ts 
FROM registo
GROUP BY DATE_FORMAT(`time_stamp`,'%y-%m-%d')

Upvotes: 1

Related Questions