SQL语句报错求助：查询前一天数据时‘1’位置出错

Hey there! Let's sort out that error in your SQL query and get you the data you need—all records from the previous day, right?

First, let's break down the problems in your original code:

• You wrapped created_at in single quotes ('created_at'), which tells the database to treat it as a string literal instead of referencing the actual column in your table. That's exactly why you're seeing an error at that position.
• Even if you fixed the quotes, using = with CURDATE() - INTERVAL 1 DAY would only match records where created_at is exactly midnight of the previous day (since created_at includes a full timestamp with time). You want all records from the entire previous day, not just that one specific moment.

Solution 1: Use a Date Range (Recommended for Performance) #

This approach checks if created_at falls between the start of the previous day and the start of today. It works efficiently because it can leverage any index on the created_at column, which is great for larger datasets:

SELECT object_key, audited_changes
FROM pg_audits
WHERE source_action = 'funding'
AND created_at >= CURDATE() - INTERVAL 1 DAY
AND created_at < CURDATE()
ORDER BY created_at DESC
LIMIT 1000

Solution 2: Truncate the Timestamp to Date #

If you prefer a more readable query, you can strip the time part from created_at and compare it directly to the previous day's date. Note that this might not use an index on created_at as efficiently as the range method, so it's better suited for smaller datasets:

SELECT object_key, audited_changes
FROM pg_audits
WHERE source_action = 'funding'
AND DATE(created_at) = CURDATE() - INTERVAL 1 DAY
ORDER BY created_at DESC
LIMIT 1000

Either of these should get you all the previous day's records without errors. Let me know if you run into any other hiccups!

内容的提问来源于stack exchange，提问作者delalma