因为有张表的数据保存格式不是时间戳,而是year,month,day三个字段来保存(索引字段),所以搜索时间范围查库有了一些难度,譬如我想搜索表名字为 da,时间区间为2018-8-2 到 2019-8-2的字段想要得到sql 语句为:
/**
* 有些sql表中存在年月日字段但是搜索的是时间戳,所以根据时间戳转化成相应的sql语句
* 搜索时间范围为 2018-8-2 - 2019-8-2 ,表为 da 结果为:
*(1 != 1) OR (da.year = 2018 AND da.month = 9) OR (da.year = 2018 AND da.month = 10) OR (da.year = 2018 AND da.month = 11)
* OR (da.year = 2018 AND da.month = 12) OR (da.year = 2019 AND da.month = 7) OR (da.year = 2019 AND da.month = 6)
* OR (da.year = 2019 AND da.month = 5) OR (da.year = 2019 AND da.month = 4) OR (da.year = 2019 AND da.month = 3)
* OR (da.year = 2019 AND da.month = 2) OR (da.year = 2019 AND da.month = 1) OR (da.year = 2018 AND da.month = 8 AND da.day = 2)
* OR(da.year = 2018 AND da.month = 8 AND da.day = 3) OR(da.year = 2018 AND da.month = 8 AND da.day = 4) OR(da.year = 2018 AND da.month = 8 AND da.day = 5)
* OR(da.year = 2018 AND da.month = 8 AND da.day = 6) OR(da.year = 2018 AND da.month = 8 AND da.day = 7) OR(da.year = 2018 AND da.month = 8 AND da.day = 8)
* OR(da.year = 2018 AND da.month = 8 AND da.day = 9) OR(da.year = 2018 AND da.month = 8 AND da.day = 10) OR(da.year = 2018 AND da.month = 8 AND da.day = 11)
* OR(da.year = 2018 AND da.month = 8 AND da.day = 12) OR(da.year = 2018 AND da.month = 8 AND da.day = 13) OR(da.year = 2018 AND da.month = 8 AND da.day = 14)
* OR(da.year = 2018 AND da.month = 8 AND da.day = 15) OR(da.year = 2018 AND da.month = 8 AND da.day = 16) OR(da.year = 2018 AND da.month = 8 AND da.day = 17)
* OR(da.year = 2018 AND da.month = 8 AND da.day = 18) OR(da.year = 2018 AND da.month = 8 AND da.day = 19) OR(da.year = 2018 AND da.month = 8 AND da.day = 20)
* OR(da.year = 2018 AND da.month = 8 AND da.day = 21) OR(da.year = 2018 AND da.month = 8 AND da.day = 22) OR(da.year = 2018 AND da.month = 8 AND da.day = 23)
* OR(da.year = 2018 AND da.month = 8 AND da.day = 24) OR(da.year = 2018 AND da.month = 8 AND da.day = 25) OR(da.year = 2018 AND da.month = 8 AND da.day = 26)
* OR(da.year = 2018 AND da.month = 8 AND da.day = 27) OR(da.year = 2018 AND da.month = 8 AND da.day = 28) OR(da.year = 2018 AND da.month = 8 AND da.day = 29)
* OR(da.year = 2018 AND da.month = 8 AND da.day = 30) OR(da.year = 2018 AND da.month = 8 AND da.day = 31)(da.year = 2019 AND da.month = 8 AND da.day = 2)
* OR(da.year = 2019 AND da.month = 8 AND da.day = 1)
* @param $begin_time
* @param $end_time
* @param {$tableName}
* @return string
*/
具体方法:
public function TimestampConversionYearMonthDaySql($begin_time,$end_time,$tableName): string
{
$andWhere = '(1 != 1)';
if ($begin_time && $end_time && $end_time > $begin_time) {
$beginYear = (int)date('Y', $begin_time);
$beginMonth = (int)date('m', $begin_time);
$beginDay = (int)date('d', $begin_time);
$endYear = (int)date('Y', $end_time);
$endMonth = (int)date('m', $end_time);
$endDay = (int)date('d', $end_time);
if ($beginYear === $endYear) {
if ($beginMonth === $endMonth) {
if ($beginDay === $endDay) {
$andWhere .= ' OR ';
$andWhere .= "({$tableName}.year = $beginYear AND {$tableName}.month = $beginMonth AND {$tableName}.day = $beginDay)";
} else if ($beginDay === 1 && $endDay === (int)date('d', mktime(0, 0, 0, $endMonth + 1, 0, $endYear))) {
$andWhere .= ' OR ';
$andWhere .= "({$tableName}.year = $beginYear AND {$tableName}.month = $beginMonth)";
} else {
$andWhere .= ' OR ';
for ($day = $beginDay; $day <= $endDay; $day++) {
$andWhere .= "({$tableName}.year = $beginYear AND {$tableName}.month = $beginMonth AND {$tableName}.day = $day)";
if ($day < $endDay) {
$andWhere .= ' OR ';
}
}
}
} else if ($endMonth > $beginMonth + 1) {
$andWhere .= ' OR ';
for ($mon = $beginMonth + 1; $mon <= $endMonth - 1; $mon++) {
$andWhere .= "({$tableName}.year = $beginYear AND {$tableName}.month = $mon) ";
if ($mon < $endMonth - 1) {
$andWhere .= ' OR ';
}
}
if ($beginDay === 1) {
$andWhere .= ' OR ';
$andWhere .= "({$tableName}.year = $beginYear AND {$tableName}.month = $beginMonth)";
if ($endDay === (int)date('d', mktime(0, 0, 0, $endMonth + 1, 0, $endYear))) {
$andWhere .= ' OR ';
$andWhere .= "({$tableName}.year = $endYear AND {$tableName}.month = $endMonth)";
} else {
$andWhere .= ' OR ';
for ($day = 1; $day <= $endDay; $day++) {
$andWhere .= "({$tableName}.year = $endYear AND {$tableName}.month = $endMonth AND {$tableName}.day = $day)";
if ($day < $endDay) {
$andWhere .= ' OR ';
}
}
}
} else {
$andWhere .= ' OR ';
$beginMonEndDay = (int)date('d', mktime(0, 0, 0, $beginMonth + 1, 0, $beginYear));
for ($day = $beginDay; $day <= $beginMonEndDay; $day++) {
$andWhere .= "({$tableName}.year = $beginYear AND {$tableName}.month = $beginMonth AND {$tableName}.day = $day)";
if ($day < $beginMonEndDay) {
$andWhere .= ' OR ';
}
}
if ($endDay === (int)date('d', mktime(0, 0, 0, $endMonth + 1, 0, $endYear))) {
$andWhere .= ' OR ';
$andWhere .= "({$tableName}.year = $endYear AND {$tableName}.month = $endMonth)";
} else {
$andWhere .= ' OR ';
for ($day = 1; $day <= $endDay; $day++) {
$andWhere .= "({$tableName}.year = $endYear AND {$tableName}.month = $endMonth AND {$tableName}.day = $day)";
if ($day < $endDay) {
$andWhere .= ' OR ';
}
}
}
}
} else {
$andWhere .= ' OR ';
if ($beginDay === 1) {
$andWhere .= "({$tableName}.year = $beginYear AND {$tableName}.month = $beginMonth)";
if ($endDay === (int)date('d', mktime(0, 0, 0, $endMonth + 1, 0, $endYear))) {
$andWhere .= ' OR ';
$andWhere .= "({$tableName}.year = $endYear AND {$tableName}.month = $endMonth)";
} else {
$andWhere .= ' OR ';
for ($day = 1; $day <= $endDay; $day++) {
$andWhere .= "({$tableName}.year = $endYear AND {$tableName}.month = $endMonth AND {$tableName}.day = $day)";
if ($day < $endDay) {
$andWhere .= ' OR ';
}
}
}
} else {
$beginMonEndDay = (int)date('d', mktime(0, 0, 0, $beginMonth + 1, 0, $beginYear));
for ($day = $beginDay; $day <= $beginMonEndDay; $day++) {
$andWhere .= "({$tableName}.year = $beginYear AND {$tableName}.month = $beginMonth AND {$tableName}.day = $day)";
if ($day < $beginMonEndDay) {
$andWhere .= ' OR ';
}
}
if ($endDay === (int)date('d', mktime(0, 0, 0, $endMonth + 1, 0, $endYear))) {
$andWhere .= ' OR ';
$andWhere .= "({$tableName}.year = $endYear AND {$tableName}.month = $endMonth)";
} else {
$andWhere .= ' OR ';
for ($day = 1; $day <= $endDay; $day++) {
$andWhere .= "({$tableName}.year = $endYear AND {$tableName}.month = $endMonth AND {$tableName}.day = $day)";
if ($day < $endDay) {
$andWhere .= ' OR ';
}
}
}
}
}
} else {
if ($beginYear < $endYear - 1) {
$andWhere .= ' OR ';
for ($year = $beginYear + 1; $year <= $endYear - 1; $year++) {
$andWhere .= "({$tableName}.year = $year)";
if ($year < $endYear - 1) {
$andWhere .= 'OR';
}
}
}
if ($beginMonth === 1 && $beginDay === 1) {
$andWhere .= ' OR ';
$andWhere .= "({$tableName}.year = $beginYear)";
}
if ($endMonth === 12 && $endDay === (int)date('d', mktime(0, 0, 0, 1, 0, $beginYear + 1))) {
$andWhere .= ' OR ';
$andWhere .= "({$tableName}.year = $endYear)";
}
if ($beginMonth < 12) {
$andWhere .= ' OR ';
for ($mon = $beginMonth + 1; $mon <= 12; $mon++) {
$andWhere .= "({$tableName}.year = $beginYear AND {$tableName}.month = $mon)";
if ($mon < 12) {
$andWhere .= ' OR ';
}
}
}
if ($endMonth > 1) {
$andWhere .= ' OR ';
for ($mon = $endMonth - 1; $mon >= 1; $mon--) {
$andWhere .= "({$tableName}.year = $endYear AND {$tableName}.month = $mon)";
if ($mon > 1) {
$andWhere .= ' OR ';
}
}
}
if ($beginDay === 1) {
$andWhere .= ' OR ';
$andWhere .= "({$tableName}.year = $beginYear AND {$tableName}.month = $beginMonth)";
} else {
$andWhere .= ' OR ';
$beginMonEndDay = date('d', mktime(0, 0, 0, $beginMonth + 1, 0, $beginYear));
for ($day = $beginDay; $day <= $beginMonEndDay; $day++) {
$andWhere .= "({$tableName}.year = $beginYear AND {$tableName}.month = $beginMonth AND {$tableName}.day = $day)";
if ($day < $beginMonEndDay) {
$andWhere .= ' OR';
}
}
}
if ($endDay === (int)date('d', mktime(0, 0, 0, $endMonth + 1, 0, $endYear))) {
$andWhere .= ' OR ';
$andWhere .= "({$tableName}.year = $endYear AND {$tableName}.month = $endMonth)";
} else {
$andWhere .= ' OR ';
for ($day = $endDay; $day >= 1; $day--) {
$andWhere .= "({$tableName}.year = $endYear AND {$tableName}.month = $endMonth AND {$tableName}.day = $day)";
if ($day > 1) {
$andWhere .= ' OR';
}
}
}
}
}
return $andWhere;
}