Adding automatic timestamps in SQL

 

Adding automatic timestamps in SQL is a common practice to track when rows are created or updated. Here’s how you can do it depending on your database — I’ll focus on SQLite, since your recent topic is about SQLite 3 and PHP interface.

---

🧩 1. Using Automatic Timestamps in SQLite

SQLite doesn’t have a built-in CURRENT_TIMESTAMP default like MySQL’s TIMESTAMP DEFAULT CURRENT_TIMESTAMP — but you can use default expressions with datetime('now').

✅ Example: Add a timestamp when inserting new records

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TEXT DEFAULT (datetime('now', 'localtime'))
);

Now when you insert a record:

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');

SQLite automatically adds the current local date and time to created_at.

Result:

id	name	email	created_at
1	Alice	alice@example.com	2025-11-06 22:10:53

---

🕒 2. Automatically Update Timestamp on Edit

SQLite does not support automatic updates to a column when a row changes, but you can achieve it using a trigger.

✅ Example with Trigger

CREATE TABLE articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    content TEXT,
    created_at TEXT DEFAULT (datetime('now', 'localtime')),
    updated_at TEXT
);

CREATE TRIGGER update_articles_timestamp
AFTER UPDATE ON articles
FOR EACH ROW
BEGIN
    UPDATE articles
    SET updated_at = datetime('now', 'localtime')
    WHERE id = OLD.id;
END;

Now, whenever you update a row:

UPDATE articles SET content = 'New text here' WHERE id = 1;

SQLite will automatically fill or refresh the updated_at field.

---

🧠 3. Using Timestamps in PHP with SQLite3 Interface

When using the PHP SQLite3 interface, you don’t need to manually generate timestamps if you’ve already set the default and trigger.

Example PHP Code:

<?php
$db = new SQLite3('example.db');

$db->exec("CREATE TABLE IF NOT EXISTS logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message TEXT,
    created_at TEXT DEFAULT (datetime('now', 'localtime'))
)");

$stmt = $db->prepare("INSERT INTO logs (message) VALUES (:message)");
$stmt->bindValue(':message', 'System started', SQLITE3_TEXT);
$stmt->execute();

$result = $db->query("SELECT * FROM logs");
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    echo $row['id'] . " | " . $row['message'] . " | " . $row['created_at'] . "\n";
}
?>

---

Enjoy! Follow us for more...