Hive查询近7天数据返回空结果的问题排查与解决

Hey there, let's break down why your queries are returning empty results and fix them step by step:

What's going wrong? #

• Format mismatch: Your insertdate field stores full timestamps with hours/minutes/seconds (like 2018-05-14 03:57:00.0), but date_sub(current_date, 7) returns a plain date string (e.g., 2018-05-14). Comparing these two directly with = will never match, since one has time components and the other doesn't.
• Typo & redundant conversion: Your second query has a syntax error (whereinsert_date is missing a space between where and the field name). Also, FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') does the exact same thing as current_date—it just returns a plain date, so you still run into the format mismatch issue.
• Field name inconsistency: Your table uses insertdate, but your queries reference insert_date (missing a t). That's another easy-to-miss reason for empty results!

Fixes to get your expected results #

Here are two reliable ways to retrieve the correct records:

Option 1: Convert the timestamp to a date for comparison

Use Hive's date() function to extract just the date part from your insertdate field, then compare it to the 7-day-ago date:

select insertdate, customer_id 
from your_table_name 
where date(insertdate) = date_sub(current_date, 7);

This will turn timestamps like 2018-05-14 03:57:00.0 into 2018-05-14, which matches the output of date_sub(current_date,7).

Option 2: Use a time range query (more efficient for large tables)

If your table is large or uses partitioning, range queries are better because they can leverage partition pruning (functions like date() on the field might prevent that). This query selects all timestamps from the start of the 7-day-ago date up to (but not including) the start of the next day:

select insertdate, customer_id 
from your_table_name 
where insertdate >= date_sub(current_date, 7) 
  and insertdate < date_sub(current_date, 6);

This covers every timestamp from YYYY-MM-DD 00:00:00.0 to YYYY-MM-DD 23:59:59.999 for the target date.

Quick check #

Make sure you replace your_table_name with the actual name of your Hive table, and double-check that the field name is consistently insertdate (not insert_date) in your query.

Either of these methods will return the 5 records you're expecting from your test data.

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