Hive技术求助：字符串日期转格式返回NULL及获取最早时间戳

Got it, let's figure out why your query is returning NULL and how to get the earliest date correctly.

First, the root cause of those NULL results is that the format string you used in unix_timestamp() doesn't match the actual structure of your acc_last_change_date values. Your date strings look like '20180209 00:00:00'—they have a space separating the date and time, plus colons between hours, minutes, and seconds. But your format string 'yyyyMMddHHmmss' expects a continuous string with no spaces or colons. That mismatch makes unix_timestamp() fail to parse the values, return NULL, and then from_unixtime() also outputs NULL as a result.

Here's how to fix this and retrieve the earliest date properly:

1. Correctly convert the string to a date/time type #

Use the matching format string 'yyyyMMdd HH:mm:ss' to parse your date strings correctly:

select from_unixtime(unix_timestamp(acc_last_change_date, 'yyyyMMdd HH:mm:ss')) as converted_date
from ACCOUNTS;

This should now return valid date/time values instead of NULL.

2. Get the earliest date #

To find the earliest date, wrap the converted value with the MIN() aggregate function. If you only need the date part (without the time component), you can use to_date() for a cleaner result:

Option 1: Include the full timestamp in the result

select MIN(from_unixtime(unix_timestamp(acc_last_change_date, 'yyyyMMdd HH:mm:ss'))) as earliest_change_date
from ACCOUNTS;

Option 2: Only retrieve the date part

select MIN(to_date(acc_last_change_date, 'yyyyMMdd HH:mm:ss')) as earliest_change_date
from ACCOUNTS;

Bonus tip: Since your original string follows a lex order-compatible format (year first, then month, day, etc.), you could technically just take the MIN() of the raw string directly:

select MIN(acc_last_change_date) as earliest_change_date
from ACCOUNTS;

But converting to a proper date type is better practice—it avoids edge cases and lets you perform date-specific operations later if needed.

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