How do you find results that occurred in the past week?

sql

performance

best-practices

database-indexing

byAnton Shumikhin·Sep 26, 2024

To fetch entries from the last 7 days, substitute your_table and created_at with your table and date fields:

-- For PostgreSQL:
SELECT * FROM your_table WHERE created_at > CURRENT_DATE - INTERVAL '7 days';
-- Just like grandma's recipe, but for data.

-- For MySQL:
SELECT * FROM your_table WHERE created_at > CURDATE() - INTERVAL 7 DAY;
-- MySQL style baby, spicy!

Quick and simple, each snippet accrues records per your SQL flavor.

Checking on time zones

Altering the time zone will change our definition of "current" day. Here's a tasty snippet that handles this:

-- Checking the user's time zone in PostgreSQL:
SELECT * FROM your_table WHERE created_at > (CURRENT_DATE AT TIME ZONE 'UTC' - INTERVAL '7 days');
-- It's 5 o'clock UTC somewhere.

Excluding today from the past week

Maybe you want to exclude today from your weekend planning. Here's how you'd do it:

-- PostgreSQL, excluding the current day:
SELECT * FROM your_table 
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days' 
AND created_at < CURRENT_DATE;
-- "Sorry, today it's time to clean my computer cables."

Sunday to Saturday: Specific week boundaries

Perhaps you require records from an entire calendar week instead:

-- PostgreSQL, last full week ending Sunday:
SELECT * FROM your_table 
WHERE created_at BETWEEN date_trunc('week', CURRENT_DATE) - INTERVAL '7 days' 
AND date_trunc('week', CURRENT_DATE) - INTERVAL '1 day';
-- "Entries from Sunday to Saturday, just the way momma used to fetch."

This gives you the previous Sunday to Saturday records, the regular work week.

Mitigating performance & leveraging indexes

For huge datasets, indexes are like Google Maps for your database. Here's how you would create an index on PostgreSQL for better performance:

-- PostgreSQL, creating an index on an expression for improved performance:
CREATE INDEX idx_date_only ON your_table ((created_at::date));
-- "Hello, it's Performance. I was wondering if after all these days you'd like to meet."

Adapt your query to lean on the computed column for filtering.

Translating to different SQL dialects

SQL Server and SQLite handle dates slightly differently than PostgreSQL and MySQL. Here's the translation:

-- For SQL Server:
SELECT * FROM your_table WHERE created_at > GETDATE() - 7;
-- "Hot SQL Server queries, coming right up!"

-- For SQLite:
SELECT * FROM your_table WHERE created_at > datetime('now', '-7 days');
-- Now serving SQLite solutions.

Improving readability: CTEs

Common Table Expressions (CTEs) are like a temporary post-it note. They help to enhance the readability of your SQL queries:

-- Using CTEs for clarifying the timeframe:
WITH LastWeek AS (
    SELECT CURRENT_DATE - INTERVAL '7 days' AS StartDate,
           CURRENT_DATE AS EndDate
)
SELECT * FROM your_table
JOIN LastWeek ON created_at BETWEEN LastWeek.StartDate AND LastWeek.EndDate;
-- PostgreSQL's model runway, check out these CTEs!