PHP queries daily data based on time period

Now need to query the date of the given day of data, ask you have any good way?

Reply to discussion (solution)

First, the database must have a field to save the date, and then group the statistics by date.

First, the database must have a field to save the date, and then group the statistics by date.

Hello, my database holds a date field in the form of a Unix timestamp, now is given a start time and end time, how can I isolate the data for each day?

Time stamp grouping is troublesome, first Use date conversion function to date, and then group, but this is less efficient, it is better to design the time to add a date field.

Can be detailed said, I still do not understand ha!

You can look at the MySQL date function, such as the field where you saved the timestamp t
You can do this statistic.
SELECT COUNT (*) as n,from_unixtime (' t ', '%y-$m-%d ') as D from ' TB ' GROUP by D ORDER by D
But this is inefficient, so it's best to calculate the date when you insert it.

Select Id,from_unixtime (Timefield, '%y%m%d ') as time from TableName where From_unixtime (Timefield, '%y%m%d ') =20150920

Hello, "Calculate date when inserting" refers to the date when the data is saved in the database, as in the 2015-10-08 format?
What I get now is the start time and end time of a time period, do you want to save this time to an array by day, and then press "daily" to execute the data on the day of the query?

Hello, "Calculate date when inserting" refers to the date when the data is saved in the database, as in the 2015-10-08 format?
What I get now is the start time and end time of a time period, do you want to save this time to an array by day, and then press "daily" to execute the data on the day of the query?

Yes, the date format is saved when inserting.
When there is a start time and an end time, the query will be more than less than the comparison character. If the amount of data is large, it is much faster to write and tidy up a new table every day.

 
  $v) {$datas [' Day_ '. Date (' MD ', $v [' timestamp '])][]= $v;//Group}var_dump ($datas [' day_0809 '])//For all data on the day of August 9; >

Hello, "Calculate date when inserting" refers to the date when the data is saved in the database, as in the 2015-10-08 format?
What I get now is the start time and end time of a time period, do you want to save this time to an array by day, and then press "daily" to execute the data on the day of the query?

Yes, the date format is saved when inserting.
When there is a start time and an end time, the query will be more than less than the comparison character. If the amount of data is large, it is much faster to write and tidy up a new table every day.

Got it, Doseha!

 
  $v) {$datas [' Day_ '. Date (' MD ', $v [' timestamp '])][]= $v;//Group}var_dump ($datas [' day_0809 '])//For all data on the day of August 9; >

3g Oil!

The time stamp has the advantage of timestamp
Using PHP to do data integration is possible.

The time stamp has the advantage of timestamp
Using PHP to do data integration is possible.

Hello, I also think so, but it is not clear what the time stamp and date format is more appropriate!

PHP Do data integration for small data volume, big data too resource-consuming, if you have 100W data need to occupy how much memory bandwidth? What about 100 million?

PHP Do data integration for small data volume, big data too resource-consuming, if you have 100W data need to occupy how much memory bandwidth? What about 100 million?

What can be done to minimize the consumption of resources?

Date-time functions are different for each type of database
If you have a plan to upsize to a large or distributed database, you must not use the date, Time Type field
Using UNIX timestamps eliminates the hassles of most date-time functions

Select *,from_unixtime (addtime, '%y%m%d ') as D from table where From_unixtime (Addtime, '%y%m%d ') >=20150101 and From_ Unixtime (addtime, '%y%m%d ') <=20150201;

$d 1 = strtotime (' 2015-01-01 ');
$d 2 = strtotime (' 2015-02-01 ');
$sql = "SELECT * from Tbl_name where field between $d 1 and $d 2"

No format conversion when querying, natural efficiency is much higher

Database storage time stamp is very good not to change your query conditions can be given to the date of the establishment of a time stamp and then to the library AH

Thank you for your answer, not only solved my problem also let me learn more knowledge, thank you very much!!!

Database storage time stamp is very good not to change your query conditions can be given to the date of the establishment of a time stamp and then to the library AH

The time stamp is stored in the database, and it is not convenient to query the daily data for a period of time on a daily basis.
The database table (the Time field should be a timestamp format, which is written directly in the date format for display):
Time | 　　Type | Number
2015-10-14 1 20
2015-10-14 2 15
2015-10-14 3 35
2015-10-15 3 35

The following results are needed:
Time | Total sales Category
2015-10-14 on 3
2015-10-15 on 1

There's nothing here.

Select Timestamp, sum (quantity) from table group by floor (timestamp/86400)

There's nothing here.

Select Timestamp, sum (quantity) from table group by floor (timestamp/86400)

Wow, still can use, educated, thank you!!!

