there is a table in the format

CREATE TABLE IF NOT EXISTS `statistic` ( `id` int(11) NOT NULL AUTO_INCREMENT, `id_user` int(11) NOT NULL, `id_org` int(11) NOT NULL, `id_driver` int(11) NOT NULL, `coins` int(11) NOT NULL, `data` bigint(20) NOT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=20110 ;

now I am making a simple sample from the table by the ID with such a query

 $result = $connection->query("SELECT * FROM statistic WHERE `id_user`=".$users[$i]['id']);

And there are dates in the range that you need to make a sample for example 20-03-2019 29-03-2019

Question: how is it possible to formulate a request in order to get only the necessary data sample as a result?

The date in the database is stored in the format of Unix tags.

  • Add in WHERE another selection condition - by dates. - Akina
  • Do not store the date in the database as unix tags, this is not convenient. But since it is stored in you like this, the data between метка1 and метка2 . And calculate the values ​​for meptoks before the request or in the request itself using unix_timestamp() . PS Never substitute variable values ​​directly into the query, use prepared queries and value binding. php.net/manual/ru/pdostatement.bindparam.php - Mike
  • How exactly does the value in the date field BIGINT and the date interpreted by it correspond? UNIX timestamp? If so, is it possible to guarantee the coincidence of the time zone of the stored data and the selection conditions? - Akina
  • Just in case: SELECT CAST(20190101121530 AS DATETIME); -> 2019-01-01 12:15:30 ... - Akina

1 answer 1

It is better to store the date in datetime format, but since you have a numeric type, you can somehow like this:

 $from = strtotime('20-03-2019'); $to = strtotime('29-03-2019' . ' 23:59:59'); $result = $connection->query("SELECT * FROM statistic WHERE `id_user`=".$users[$i]['id'] ." AND `data` >= {$from} AND `data` <= {$to}"); // Вариант 2 $result = $connection->query("SELECT * FROM statistic WHERE `id_user`=".$users[$i]['id'] ." AND `data` BETWEEN {$from} AND {$to}");