如何在2小时之间从mysql中检索计数

Consider the following table:

CREATE TABLE `trans` (
  `transid` varchar(255) NOT NULL,
  `affid` varchar(255) NOT NULL,
  `timestamp` varchar(255) NOT NULL,
  UNIQUE KEY `transid` (`transid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

and PHP code:

case "today":
    $sale=[];
    for ($i = 0 ; $i < 23 ; $i++) {
        $hours[]= $i;
        $clicks[$i]=$api["clicks"][$i];
    }    
    break;

I want to add into the $sale array today's count of sales for each hour. Something like: $hours[5] = select count(*) from trans where .. date is between today's 04:59:59 - 06:00:00.

Something like this:

<?php
$from=strtotime("".date("Y-m-d")." 04:59:59");
$to=strtotime("".date("Y-m-d")." 06:00:00");
?>

"SELECT * FROM `your_table` 
WHERE `date` > '".$from."' 
AND `date` < '".$to."'"

maybe?

You can use gmdate() if you need the current UTC time instead of the local time which you get with date().

First of all, if you are trying to use unix timestamps for your timestamp field, stop it now. You are already making your life painful. Use a MySQL timestamp field. There are honestly very few use cases where it is a good idea to use a unix timestamp field.

If you had a timestamp field you could do something like

SELECT HOUR(`timestamp`) AS `hour`, SUM(`transid`) AS `transaction_count`
FROM trans
WHERE `timestamp` LIKE CONCAT(DATE(NOW()),'%')
GROUP BY `hour`
ORDER BY `hour` ASC

That would give you the sales count for every hour of the current day. Make sure you add an index on timestamp for optimal query performance.

If you don't have a timestamp, then you need to utilize extra FROM_UNIXTIME() conversions, which would prevent you from being able to use an index on timestamp