通过PDO::sqliteCreateFunction在sqllite中实现timestampdiff

$db = new PDO('sqlite:database.db');

// 创建timestampdiff函数
$db->sqliteCreateFunction('timestampdiff', function($unit, $start, $end) {
    $start = strtotime($start);
    $end = strtotime($end);

    $diff = $end - $start;

    switch ($unit) {
        case 'SECOND':
            return $diff;
        case 'MINUTE':
            return $diff / 60;
        case 'HOUR':
            return $diff / 3600;
        case 'DAY':
            return $diff / 86400;
        case 'MONTH':
            return $diff / 2592000;
        case 'YEAR':
            return $diff / 31536000;
        default:
            return null;
    }
});

// 使用timestampdiff函数查询时间差
$query = $db->query("SELECT timestampdiff('SECOND', '2022-01-01 00:00:00', '2022-01-01 00:01:30') AS diff");
$result = $query->fetch(PDO::FETCH_ASSOC);

echo "时间差：" . $result['diff'] . "秒";