1

I am novice to Hive. Trying to get the previous day date using the below query:

SELECT MAX(id) FROM store_rcd_table
WHERE recon_dt = unix_timestamp(date_sub(from_unixtime(unix_timestamp(),
'yyyy-MM-dd'),1),'yyyy-MM-dd')

but getting the NULL as output. The output should have been date(2017-09-23) and MAX(id).

Also tried,

Select MAX(id) FROM store_rcd_table
WHERE recon_dt ='2017-09-24';

No output for this query also, just OK is coming as an output.

Not getting what the issue is? Any suggestion/ help is appreciated.

The structure of the table is:

id              string                                      
locationid      string                                      
mngrid          string                                      
empid           string                                                                          
deleted         boolean                                     
recon_dt        string
newbee
  • 49
  • 1
  • 1
  • 6

1 Answers1

9

Try select date_sub(current_date, 1);

0: jdbc:hive2://hiveserver2:1> select date_sub(current_date, 1);
+-------------+--+
|     _c0     |
+-------------+--+
| 2017-09-24  |
+-------------+--+
1 row selected (0.182 seconds)

Original attempt was date_sub(CAST(unix_timestamp()*1000 AS TIMESTAMP), 1) but apparently that's wrong. See comments

Feel free to read up on Hive Date Functions

If you get no results, you should check the source data to see what recon_dt actually is. (Also worth pointing out that you used two different tables in the question)

OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
  • I did that but its not showing. As currently I don't have sufficient reputation points. – newbee Sep 26 '17 at 00:24
  • 2
    **(1)** `current_date` was introduced on Hive 1.2.0, 18 May 2015, more than 2 years ago. **(2)** The use of `unix_timestamp` prevents partition elimination. – David דודו Markovitz Sep 26 '17 at 11:30
  • The use of unix_timestamp is not legitimate for this scenario – David דודו Markovitz Sep 26 '17 at 12:40
  • @Dudu if you can send a reference to partition elimination for Hive, I'll gladly read it. Everything I've found immediately is for SQL Server – OneCricketeer Sep 26 '17 at 12:44
  • @newbee Upvoting and accepting are different actions. https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – OneCricketeer Sep 26 '17 at 13:21
  • @cricket_007, from https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF: "Gets current Unix timestamp in seconds. This function is not deterministic and its value is not fixed for the scope of a query execution, therefore prevents proper optimization of queries - this has been deprecated since 2.0 in favor of CURRENT_TIMESTAMP constant." (I wrote this... :-) ...) – David דודו Markovitz Sep 26 '17 at 14:13
  • @cricket_007, also check https://stackoverflow.com/questions/42376268/why-partitions-elimination-does-not-happen-for-this-query/42377403#42377403. You can see that you don't need to execute anything in order to test partitions elimination, the execution plan is enough. – David דודו Markovitz Sep 26 '17 at 14:17
  • @Dudu Makes sense. Thanks. So, what was point (1) about? Should I not be using `current_date`? I haven't had the luxury of using Hive 2.x much. – OneCricketeer Sep 26 '17 at 16:45
  • Since current_date is here for a long time now, unix_timestamp should be obseleted – David דודו Markovitz Sep 26 '17 at 16:47