Mastering Date and Time Functions in PostgreSQL: A Practical Guide with 12 Examples

-- Create a table
CREATE TABLE example_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    created_at TIMESTAMP
);

-- Insert some data into the table
INSERT INTO example_table (name, age, created_at) 
VALUES 
    ('John', 30, NOW()),
    ('Alice', 25, NOW()),
    ('Bob', 35, NOW());

-- 1. Current date and time
SELECT NOW();

-- 2. Extract components from a timestamp
SELECT 
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DAY FROM created_at) AS day,
    EXTRACT(HOUR FROM created_at) AS hour,
    EXTRACT(MINUTE FROM created_at) AS minute,
    EXTRACT(SECOND FROM created_at) AS second
FROM example_table;

-- 3. Get the difference between two timestamps
SELECT AGE(NOW(), created_at) AS age_difference
FROM example_table;

-- 4. Add or subtract intervals from timestamps
SELECT 
    created_at + INTERVAL '1 day' AS tomorrow,
    created_at - INTERVAL '1 hour' AS an_hour_ago
FROM example_table;

-- 5. Identify the day of the week when each person was created
SELECT 
    name,
    created_at,
    TO_CHAR(created_at, 'Day') AS day_of_week
FROM example_table;

-- 6. Find the average age of all people in the table
SELECT AVG(age) AS average_age
FROM example_table;

-- 7. Identify the most recent and oldest creation dates in the table
SELECT 
    MAX(created_at) AS most_recent_date,
    MIN(created_at) AS oldest_date
FROM example_table;

-- 8. Identify people who were created on the same day
SELECT 
    e1.name AS person1,
    e2.name AS person2,
    e1.created_at AS creation_date
FROM 
    example_table e1
JOIN 
    example_table e2 ON e1.created_at = e2.created_at AND e1.id < e2.id;

-- 9. Determine the quarter of the year when each person was created
SELECT 
    name,
    created_at,
    EXTRACT(QUARTER FROM created_at) AS creation_quarter
FROM 
    example_table;

-- 10. Find the total number of people created in the last 7 days
SELECT 
    COUNT(*) AS people_created_last_week
FROM 
    example_table
WHERE 
    created_at >= NOW() - INTERVAL '7 days';

-- 11. Find the average age of people created in each month
SELECT 
    TO_CHAR(created_at, 'YYYY-MM') AS creation_month,
    AVG(age) AS average_age
FROM 
    example_table
GROUP BY 
    creation_month
ORDER BY 
    creation_month;

-- 12. Identify the age group distribution of people
SELECT 
    CASE 
        WHEN age < 30 THEN 'Under 30'
        WHEN age BETWEEN 30 AND 40 THEN '30-40'
        WHEN age > 40 THEN 'Over 40'
    END AS age_group,
    COUNT(*) AS people_count
FROM 
    example_table
GROUP BY 
    age_group
ORDER BY 
    age_group;

-- Bonus. Calculate the time since the last person was created for each record
SELECT 
    name,
    created_at,
    LAG(created_at, 1, created_at) OVER (ORDER BY created_at) AS previous_creation,
    AGE(created_at, LAG(created_at, 1, created_at) OVER (ORDER BY created_at)) AS time_since_last_creation
FROM 
    example_table
ORDER BY 
    created_at;