PostgreSQL Date Functions

Common Date Functions

SELECT NOW();

SELECT EXTRACT(YEAR FROM NOW());

SELECT CURRENT_DATE + INTERVAL '7 days';

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');

🔹 NOW() and CURRENT_DATE

Get the current date and time from your database server. NOW() returns a timestamp with date and time, while CURRENT_DATE returns only the date. These are essential for timestamping records and date comparisons.

-- Get current timestamp
SELECT NOW();
-- Returns: 2024-01-15 14:30:25.123456

-- Get current date only
SELECT CURRENT_DATE;
-- Returns: 2024-01-15

-- Get current time only
SELECT CURRENT_TIME;
-- Returns: 14:30:25.123456

-- Practical example
SELECT 
    order_id,
    order_date,
    NOW() - order_date AS time_since_order
FROM orders;
                            

🔹 EXTRACT()

Extract specific parts from a date or timestamp like year, month, day, hour, or minute. This function is invaluable for grouping data by time periods or filtering based on specific date components.

-- Extract year
SELECT EXTRACT(YEAR FROM NOW());
-- Returns: 2024

-- Extract month
SELECT EXTRACT(MONTH FROM NOW());
-- Returns: 1

-- Extract day
SELECT EXTRACT(DAY FROM NOW());
-- Returns: 15

-- Extract multiple parts
SELECT 
    order_date,
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    EXTRACT(DAY FROM order_date) AS day
FROM orders;
                            

🔹 Date Arithmetic with INTERVAL

Add or subtract time periods from dates using INTERVAL. You can work with days, months, years, hours, minutes, and more. This makes calculating future dates, deadlines, or date ranges straightforward.

-- Add days
SELECT CURRENT_DATE + INTERVAL '7 days';
-- Returns: 2024-01-22

-- Subtract months
SELECT CURRENT_DATE - INTERVAL '3 months';
-- Returns: 2023-10-15

-- Add hours and minutes
SELECT NOW() + INTERVAL '2 hours 30 minutes';

-- Calculate due dates
SELECT 
    order_date,
    order_date + INTERVAL '30 days' AS due_date,
    order_date + INTERVAL '1 year' AS warranty_end
FROM orders;
                            

🔹 AGE()

Calculate the difference between two dates as an interval showing years, months, and days. AGE is perfect for calculating ages, time elapsed, or how long until a future date.

-- Calculate age from birthdate
SELECT AGE(CURRENT_DATE, '1990-05-15');
-- Returns: 33 years 8 mons 0 days

-- Age with single argument (from current date)
SELECT AGE('1990-05-15');
-- Returns: 33 years 8 mons 0 days

-- Calculate account age
SELECT 
    username,
    created_date,
    AGE(CURRENT_DATE, created_date) AS account_age
FROM users;
                            

🔹 DATE_PART()

Similar to EXTRACT but returns a double precision number. DATE_PART is useful when you need numeric values for calculations or when working with specific date components in mathematical operations.

-- Get year as number
SELECT DATE_PART('year', NOW());
-- Returns: 2024

-- Get day of week (0=Sunday, 6=Saturday)
SELECT DATE_PART('dow', NOW());
-- Returns: 1 (Monday)

-- Get day of year
SELECT DATE_PART('doy', NOW());
-- Returns: 15

-- Group by month
SELECT 
    DATE_PART('month', order_date) AS month,
    COUNT(*) AS order_count
FROM orders
GROUP BY month;
                            

🔹 TO_CHAR() - Format Dates

Convert dates to formatted strings using pattern templates. TO_CHAR gives you complete control over how dates appear, perfect for reports, displays, and exporting data in specific formats.

-- Format as YYYY-MM-DD
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');
-- Returns: 2024-01-15

-- Format as Month DD, YYYY
SELECT TO_CHAR(NOW(), 'Month DD, YYYY');
-- Returns: January 15, 2024

-- Custom formats
SELECT TO_CHAR(NOW(), 'Day, Mon DD YYYY HH24:MI:SS');
-- Returns: Monday, Jan 15 2024 14:30:25

-- Format order dates
SELECT 
    order_id,
    TO_CHAR(order_date, 'MM/DD/YYYY') AS formatted_date,
    TO_CHAR(order_date, 'Day') AS day_name
FROM orders;
                            

🔹 TO_DATE() and TO_TIMESTAMP()

Convert string representations to date or timestamp types. These functions parse text into proper date objects, essential when importing data or working with dates stored as strings.

-- Convert string to date
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD');
-- Returns: 2024-01-15

-- Convert string to timestamp
SELECT TO_TIMESTAMP('2024-01-15 14:30:25', 'YYYY-MM-DD HH24:MI:SS');
-- Returns: 2024-01-15 14:30:25

-- Parse different formats
SELECT TO_DATE('01/15/2024', 'MM/DD/YYYY');
SELECT TO_DATE('15-Jan-2024', 'DD-Mon-YYYY');

-- Convert imported data
SELECT 
    TO_DATE(date_string, 'MM/DD/YYYY') AS proper_date
FROM imported_data;
                            

🔹 DATE_TRUNC()

Truncate a timestamp to a specified precision like hour, day, month, or year. This function is excellent for grouping data into time buckets or finding the start of time periods.

-- Truncate to day (remove time)
SELECT DATE_TRUNC('day', NOW());
-- Returns: 2024-01-15 00:00:00

-- Truncate to month (first day of month)
SELECT DATE_TRUNC('month', NOW());
-- Returns: 2024-01-01 00:00:00

-- Truncate to year
SELECT DATE_TRUNC('year', NOW());
-- Returns: 2024-01-01 00:00:00

-- Group sales by month
SELECT 
    DATE_TRUNC('month', sale_date) AS month,
    SUM(amount) AS monthly_total
FROM sales
GROUP BY month;
                            

🔹 OVERLAPS

Check if two date ranges overlap. This operator is useful for scheduling systems, booking applications, or any scenario where you need to detect conflicting time periods.

-- Check if date ranges overlap
SELECT (DATE '2024-01-01', DATE '2024-01-15') 
       OVERLAPS 
       (DATE '2024-01-10', DATE '2024-01-20');
-- Returns: true

-- Check if periods don't overlap
SELECT (DATE '2024-01-01', DATE '2024-01-10') 
       OVERLAPS 
       (DATE '2024-01-15', DATE '2024-01-20');
-- Returns: false

-- Find conflicting bookings
SELECT * FROM bookings b1
WHERE EXISTS (
    SELECT 1 FROM bookings b2
    WHERE b1.id != b2.id
    AND (b1.start_date, b1.end_date) 
        OVERLAPS 
        (b2.start_date, b2.end_date)
);