php如何查询本年的数据

# PHP如何查询本年的数据

在Web开发中，按时间范围筛选数据是常见需求。本文将详细介绍PHP中查询本年度数据的多种方法，涵盖不同数据库系统和日期处理技巧。

## 一、获取当前年份

### 1.1 使用date()函数
```php
$currentYear = date('Y'); // 返回4位数字年份，如2023

1.2 使用DateTime对象

$now = new DateTime();
$currentYear = $now->format('Y');

二、MySQL数据库查询

2.1 使用YEAR()函数

$sql = "SELECT * FROM table_name WHERE YEAR(date_column) = YEAR(CURDATE())";

2.2 范围查询（推荐）

$yearStart = $currentYear.'-01-01';
$yearEnd = $currentYear.'-12-31';

$sql = "SELECT * FROM table_name 
        WHERE date_column BETWEEN '$yearStart' AND '$yearEnd'";

2.3 使用预处理语句防止SQL注入

$stmt = $pdo->prepare("SELECT * FROM table_name 
                      WHERE date_column BETWEEN ? AND ?");
$stmt->execute([$yearStart, $yearEnd]);

三、PostgreSQL数据库查询

3.1 使用EXTRACT函数

$sql = "SELECT * FROM table_name 
        WHERE EXTRACT(YEAR FROM date_column) = EXTRACT(YEAR FROM CURRENT_DATE)";

3.2 日期范围查询

$sql = "SELECT * FROM table_name 
        WHERE date_column >= DATE_TRUNC('year', CURRENT_DATE)
        AND date_column < DATE_TRUNC('year', CURRENT_DATE + INTERVAL '1 year')";

四、SQLite数据库查询

4.1 使用strftime函数

$sql = "SELECT * FROM table_name 
        WHERE strftime('%Y', date_column) = strftime('%Y', 'now')";

五、MongoDB查询

$start = new MongoDB\BSON\UTCDateTime(strtotime($yearStart) * 1000);
$end = new MongoDB\BSON\UTCDateTime(strtotime($yearEnd) * 1000);

$filter = [
    'date_field' => [
        '$gte' => $start,
        '$lte' => $end
    ]
];
$query = new MongoDB\Driver\Query($filter);

六、Eloquent ORM查询

6.1 基础查询

$results = Model::whereYear('created_at', date('Y'))->get();

6.2 范围查询

$results = Model::whereBetween('created_at', [
    $currentYear.'-01-01 00:00:00',
    $currentYear.'-12-31 23:59:59'
])->get();

七、性能优化建议

1. 索引优化：确保日期字段已建立索引
2. 避免函数运算：WHERE YEAR(column) = 2023会导致全表扫描
3. 使用EXPLN分析查询执行计划
4. 分页处理大数据集：

$perPage = 50;
$results = Model::whereYear('date_column', $currentYear)
             ->paginate($perPage);

八、完整示例代码

<?php
// 数据库连接
$host = 'localhost';
$dbname = 'test_db';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 获取当前年份
    $currentYear = date('Y');
    $yearStart = "$currentYear-01-01";
    $yearEnd = "$currentYear-12-31";
    
    // 预处理语句
    $stmt = $pdo->prepare("
        SELECT id, title, created_at 
        FROM articles 
        WHERE created_at BETWEEN :start AND :end
        ORDER BY created_at DESC
    ");
    
    $stmt->bindParam(':start', $yearStart);
    $stmt->bindParam(':end', $yearEnd);
    $stmt->execute();
    
    // 获取结果
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // 输出结果
    echo "<h2>$currentYear 年数据</h2>";
    echo "<ul>";
    foreach ($results as $row) {
        echo "<li>{$row['title']} - {$row['created_at']}</li>";
    }
    echo "</ul>";
    
} catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}
?>

九、常见问题解答

Q1: 如何查询特定年份而非当前年份？

$targetYear = 2022; // 指定查询年份
$sql = "SELECT * FROM table WHERE YEAR(date_column) = $targetYear";

Q2: 查询结果为空可能的原因？

• 日期字段格式不正确
• 数据库中没有符合条件的数据
• 时区设置问题（建议统一使用UTC）

Q3: 如何按月份分组统计？

$sql = "SELECT 
          MONTH(date_column) as month, 
          COUNT(*) as count 
        FROM table 
        WHERE YEAR(date_column) = YEAR(CURDATE())
        GROUP BY MONTH(date_column)";

十、扩展应用

10.1 查询本季度数据

$quarter = ceil(date('n') / 3);
$startMonth = ($quarter - 1) * 3 + 1;
$endMonth = $quarter * 3;

$startDate = date("Y-m-d", strtotime("$currentYear-$startMonth-01"));
$endDate = date("Y-m-t", strtotime("$currentYear-$endMonth-01"));

10.2 动态生成年度报表

function generateAnnualReport($year) {
    $reportData = [];
    for ($month = 1; $month <= 12; $month++) {
        $start = "$year-$month-01";
        $end = date("Y-m-t", strtotime($start));
        
        $stmt = $pdo->prepare("SELECT SUM(amount) as total 
                              FROM sales 
                              WHERE sale_date BETWEEN ? AND ?");
        $stmt->execute([$start, $end]);
        $reportData[$month] = $stmt->fetchColumn();
    }
    return $reportData;
}

结语

本文详细介绍了PHP中查询本年度数据的各种方法，包括： - 不同数据库系统的特定语法 - ORM框架的便捷用法 - 性能优化建议 - 实际应用场景扩展

掌握这些技巧可以高效实现基于时间的查询需求，建议根据项目实际情况选择最适合的方案。 “`