Timestamp Tracking

Objective

Create tables with timestamp columns that automatically track when records are created and modified. Timestamp tracking is essential for auditing, debugging, and data synchronization.

Step 1: Basic Timestamp Columns

Create a table with creation and update timestamps.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    author VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Records with Auto-Timestamps

Insert records and let the database set timestamps automatically.

INSERT INTO articles (id, title, content, author) VALUES
    (1, 'Introduction to AIDB', 'Learn the basics of AIDB...', 'Tech Writer'),
    (2, 'Advanced SQL Patterns', 'Explore complex query techniques...', 'Database Expert'),
    (3, 'Best Practices Guide', 'Follow these recommendations...', 'Architecture Team');

Step 3: Verify Timestamps

Check that timestamps were automatically populated.

SELECT id, title, author, created_at, updated_at
FROM articles
ORDER BY created_at DESC;

Step 4: Update Records with New Timestamp

Update records and set new updated_at timestamp.

UPDATE articles
SET content = 'Updated content with new information...',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

SELECT id, title, created_at, updated_at
FROM articles
WHERE id = 1;

Step 5: Create Complete Audit Trail Table

Create a table with comprehensive timestamp tracking.

CREATE TABLE customer_records (
    id INTEGER PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP,
    deleted_at TIMESTAMP
);

INSERT INTO customer_records (id, customer_name, email) VALUES
    (1, 'Alice Corp', 'alice@example.com'),
    (2, 'Bob Industries', 'bob@example.com'),
    (3, 'Carol Services', 'carol@example.com');

Step 6: Track Login Activity

Update login timestamps for activity tracking.

-- Simulate login activity
UPDATE customer_records
SET last_login_at = CURRENT_TIMESTAMP
WHERE id = 1;

UPDATE customer_records
SET last_login_at = CURRENT_TIMESTAMP
WHERE id = 2;

SELECT customer_name, last_login_at, created_at
FROM customer_records
ORDER BY last_login_at DESC;

Step 7: Soft Delete with Timestamp

Implement soft delete using deleted_at timestamp.

-- Soft delete a record
UPDATE customer_records
SET deleted_at = CURRENT_TIMESTAMP,
    status = 'deleted'
WHERE id = 3;

-- Query only active records
SELECT customer_name, email, status
FROM customer_records
WHERE deleted_at IS NULL;

-- Query deleted records
SELECT customer_name, email, deleted_at
FROM customer_records
WHERE deleted_at IS NOT NULL;

Step 8: Create Order Tracking System

Build an order system with full timestamp tracking.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    submitted_at TIMESTAMP,
    processed_at TIMESTAMP,
    shipped_at TIMESTAMP,
    delivered_at TIMESTAMP,
    cancelled_at TIMESTAMP
);

INSERT INTO orders (id, customer_id, total_amount) VALUES
    (1, 101, 299.99),
    (2, 102, 149.50),
    (3, 101, 599.00);

-- Order workflow updates
UPDATE orders
SET status = 'submitted', submitted_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

UPDATE orders
SET status = 'processed', processed_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

UPDATE orders
SET status = 'shipped', shipped_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

Step 9: Query by Time Ranges

Find records based on timestamp ranges.

-- Orders created today
SELECT id, total_amount, status, created_at
FROM orders
WHERE created_at >= CURRENT_DATE;

-- Recently updated records
SELECT id, status, updated_at
FROM orders
ORDER BY updated_at DESC
LIMIT 10;

-- Calculate time between creation and shipping
SELECT
    id,
    total_amount,
    created_at,
    shipped_at
FROM orders
WHERE shipped_at IS NOT NULL;

Step 10: Date-Based Filtering

Create and query date-specific data.

CREATE TABLE daily_logs (
    id INTEGER PRIMARY KEY,
    log_date DATE DEFAULT CURRENT_DATE,
    log_time TIME DEFAULT CURRENT_TIME,
    log_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    message TEXT,
    severity VARCHAR(20) DEFAULT 'info'
);

INSERT INTO daily_logs (id, message, severity) VALUES
    (1, 'System started successfully', 'info'),
    (2, 'User authentication completed', 'info'),
    (3, 'Warning: High memory usage detected', 'warning');

SELECT log_date, log_time, severity, message
FROM daily_logs
ORDER BY log_timestamp DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS articles;
DROP TABLE IF EXISTS customer_records;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS daily_logs;

Expected Outcomes

• created_at auto-populates on INSERT
• updated_at tracks modifications
• Soft delete preserves data with timestamp
• Workflow timestamps track state changes
• Time-based queries filter effectively

Timestamp Patterns

Key Concepts Learned

• CURRENT_TIMESTAMP for automatic timestamps
• Tracking creation and update times
• Soft delete pattern with deleted_at
• Workflow state tracking
• Time-based querying and filtering