Date Queries

Code

-- Current date time
SELECT CURRENT_DATE, CURRENT_TIMESTAMP, NOW();

-- Date arithmetic
SELECT order_date + INTERVAL '7 days' AS next_week,
       order_date - INTERVAL '1 month' AS last_month,
       CURRENT_DATE - order_date AS days_ago
FROM orders;

-- Date truncation
SELECT DATE_TRUNC('month', order_date) AS month_start,
       DATE_TRUNC('week', order_date) AS week_start
FROM orders;

-- Extract part
SELECT EXTRACT(YEAR FROM order_date) AS year,
       EXTRACT(MONTH FROM order_date) AS month,
       EXTRACT(DOW FROM order_date) AS day_of_week
FROM orders;

-- Group statistics
SELECT DATE_TRUNC('month', order_date) AS month,
       COUNT(*) AS order_count,
       SUM(total) AS revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY month
ORDER BY month;

-- Age calculation
SELECT name, AGE(birth_date) AS age FROM users;

Explanation

PostgreSQL provides rich date functions; INTERVAL performs date arithmetic; DATE_TRUNC truncates to a specified precision.

More SQL Snippets