mysql queries the specified date and time within the sql statement implementation principle and code

  • 2020-05-27 04:34:49
  • OfStack

When designing a database tutorial, it is important to note that the time field is int(11). In this case, what is saved in the database is a number font date timestamp. We can use the mktime function to figure out the current date timestamp and add or subtract it to OK
/ / 1 month
$lastMonth = mktime(date( ' h'),date( ' i'),date( ' s'),date( ' m')-1,date( ' d'),date( ' y')); 
$where .=  "  dtime > $lastMonth " ; 

/ / 3 months
$lastThreeMonth = mktime(date( ' h'),date( ' i'),date( ' s'),date( ' m')-3,date( ' d'),date( ' y')); 
$where .=  "  dtime > $lastThreeMonth " ; 

$sql = "select * from testtable".$where
The principle is:
If it is a month, it is the current month minus the time you want to count. If I want to query all records in the database three months before today, our statements are as follows: mktime(date(' h'),date(' i'),date(' s'),date(' m')-3,date(' d'),date(' y').
Within 7 days :mktime(date(' h'),date(' i'),date(' s'),date(' m'),date(' d')-7,date(' y'));
1 small time :mktime(date(' h')-1,date(' i'),date(' s'),date(' m'),date(' d'),date(' y'));
Day 1: last month mktime (0, 0, date (' m ',01) - 1, date (' Y '));
Last day of last month :mktime(0,0,0,date(' m'),0,date(' y'));
Day 1 of the month: this is simple, that is 01;
The last day of the month: this will use the date function, date function has a parameter t, which is used to calculate the last day; Such as: date (' t ')
The other way is the same.

Related articles: