含日期检查的Query排除部分记录，请求排查成因

Hey there, let's dig into why those three entries (50 19/09/2019 15:00:00, 60 19/09/2019 17:00:00, 70 19/09/2019 18:00:00) aren't showing up, even though you've only set up a rule to exclude records older than 7 days. Here are the most likely culprits:

1. Date Format Parsing Mismatch #

This is super common. If your system expects dates in MM/DD/YYYY (US-style) but you're feeding it DD/MM/YYYY (EU-style), the date 19/09/2019 will be interpreted as "month 19, day 9"—which is invalid. Most date parsers will either throw an error or silently convert this to a default value (like NULL or a very old date), which then gets caught by your 7-day exclusion rule.

For example, if you're using a function like DateTime.Parse("19/09/2019") in C# without specifying the culture, it might default to en-US and fail. Or in SQL, STR_TO_DATE('19/09/2019', '%m/%d/%Y') would return NULL because 19 isn't a valid month.

2. Time Zone or Timestamp Boundary Logic Flaws #

Even if the date format is correct, time zone differences or sloppy boundary checks could be filtering these records by accident:

• Time zone mismatch: If your server uses UTC but your records are in a local time zone (e.g., UTC+8), converting the timestamps might push those 19/09 entries just over the 7-day threshold. For example, 19/09/2019 15:00 UTC+8 is 19/09/2019 07:00 UTC. If your current server time is 26/09/2019 06:00 UTC, that's 6 days and 23 hours later—not over 7 days. But if your code only compares dates (ignoring time), DATE('2019-09-19') vs DATE(NOW() - INTERVAL 7 DAY) might calculate as exactly 7 days apart, and your condition could be using < instead of <=, excluding the entire day.
• Incorrect interval calculation: Double-check if your exclusion logic uses CURRENT_DATE (which drops the time component) instead of CURRENT_TIMESTAMP. If you have WHERE created_at < CURRENT_DATE - INTERVAL 7 DAY, any record from 7 days ago regardless of time will be excluded, even if it's only minutes older than the 7-day mark.

3. Hidden Filter Logic You Forgot About #

You mentioned "only排除超过7天前的记录" (only excluding records older than 7 days), but it's easy to overlook a leftover filter in your code or query. For example:

• A hardcoded condition like WHERE value > 70 would exclude your 50, 60, 70 entries.
• A status filter (e.g., WHERE is_active = 1) that these records don't meet.
• A JOIN clause that accidentally excludes them because of missing related data.

4. Data Storage Anomalies #

Sometimes the issue is with how the data is stored, not the logic:

• If the timestamp is stored as a string instead of a datetime type, trailing spaces, incorrect separators (e.g., 19-09-2019 instead of 19/09/2019), or typos could break the comparison.
• Corrupted data: The timestamp field for these three entries might be NULL or set to an invalid value that gets treated as an extremely old date.

Quick Troubleshooting Steps #

To narrow it down fast:

1. Check the parsed timestamp values for these entries—print them out in your code or run a query to see how the system interprets 19/09/2019 15:00:00.
2. Test your exclusion logic with one of these timestamps manually: plug the exact value into your condition and see if it returns true (meaning it gets excluded).
3. Audit your full query/code for any extra filters you might have missed.
4. Verify the raw data in your storage to ensure the timestamps are saved correctly.

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