Current Location: Home> Latest Articles> PHP and Oracle Database Time Series Data Processing Techniques

PHP and Oracle Database Time Series Data Processing Techniques

M66 2025-07-01

Introduction

Time series data is a commonly used data type in modern applications, recording values that change over time. In PHP development, working with Oracle databases to process such data is a common need. This article will share some techniques for handling time series data in PHP using Oracle database, and will provide code examples to help developers better understand these operations.

Connecting to Oracle Database

In PHP, the PDO extension is typically used to connect to an Oracle database. Below is a simple code example for establishing a connection:

<?php
$host = 'localhost';
$port = '1521';
$sid = 'ORCL';
$dsn = "oci:dbname=//(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host)(PORT=$port))(CONNECT_DATA=(SID=$sid)))";
$user = 'username';
$password = 'password';
try {
    $pdo = new PDO($dsn, $user, $password);
    echo "Connected to Oracle database successfully!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Creating a Time Series Table

In Oracle database, you can use the TIMESTAMP type to store time series data. Below is an example of code to create a time series table:

<?php
$sql = "CREATE TABLE time_series_data (
    id INT PRIMARY KEY,
    timestamp_col TIMESTAMP,
    value NUMBER
)";
$pdo->exec($sql);
echo "Time series table created successfully!";
?>

Inserting Time Series Data

When inserting time series data, you can use the TO_TIMESTAMP function to convert a date string into Oracle's TIMESTAMP type. Below is an example of code to insert time series data:

<?php
$id = 1;
$timestamp = '2022-01-01 00:00:00';
$value = 10;
$sql = "INSERT INTO time_series_data (id, timestamp_col, value)
        VALUES (:id, TO_TIMESTAMP(:timestamp, 'YYYY-MM-DD HH24:MI:SS'), :value)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->bindParam(':timestamp', $timestamp, PDO::PARAM_STR);
$stmt->bindParam(':value', $value, PDO::PARAM_INT);
$stmt->execute();
echo "Time series data inserted successfully!";
?>

Querying Time Series Data

When querying time series data, you can use the TO_CHAR function to convert a TIMESTAMP type date to a string, and the TO_TIMESTAMP function to convert a string back into TIMESTAMP for comparison. Below is an example of code to query time series data:

<?php
$startTimestamp = '2022-01-01 00:00:00';
$endTimestamp = '2022-01-02 00:00:00';
$sql = "SELECT id, TO_CHAR(timestamp_col, 'YYYY-MM-DD HH24:MI:SS') AS timestamp, value
        FROM time_series_data
        WHERE timestamp_col >= TO_TIMESTAMP(:startTimestamp, 'YYYY-MM-DD HH24:MI:SS')
        AND timestamp_col < TO_TIMESTAMP(:endTimestamp, 'YYYY-MM-DD HH24:MI:SS')";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':startTimestamp', $startTimestamp, PDO::PARAM_STR);
$stmt->bindParam(':endTimestamp', $endTimestamp, PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $row) {
    echo "ID: " . $row['id'] . " Timestamp: " . $row['timestamp'] . " Value: " . $row['value'] . "\n";
}
?>

Conclusion

In PHP development, using an Oracle database to handle time series data is a common need. This article introduces the techniques for connecting to the Oracle database, creating time series tables, inserting, and querying time series data. Through practical code examples, it aims to help readers handle time series data more efficiently in Oracle databases.

Related