Geek asks and answers

Get sysdate -1 in Hive

Is there a way to get current date -1into Hive, then, yesterdays datealways? And in this format 20120805?

I can fulfill my request in such a way as to get data for yesterday date, since today it Aug 6this -

select * from table1 where dt = '20120805';

But when I tried to do this using date_sub functionto get yesterday's date, since the table below is split into a date column (dt).

select * from table1 where dt = date_sub(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(),
'yyyyMMdd')) , 1)     limit 10;

Is he looking for data in all sections? What for? Is there something wrong with what I am doing in my request?

How can I make an estimate in a subquery to avoid checking the entire table?

+5
source share
4 answers

Try something like:

select * from table1 
where dt >= from_unixtime(unix_timestamp()-1*60*60*24, 'yyyyMMdd');

, , . from_unixtime , Hive . (, ), , , , .

,

$ hive -hiveconf date_yesterday=20150331

script

select * from table1
where dt >= ${hiveconf:date_yesterday};

, , , unix. OP

$ hive -hiveconf date_yesterday=$(date --date yesterday "+%Y%m%d")
+10

mysql:

select DATE_FORMAT(curdate()-1,'%Y%m%d');

sqlserver:

SELECT convert(varchar,getDate()-1,112)

:

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()-1*24*60*60,'%Y%m%d');
+1

, DATE_SUB yyyy-MM-dd. , , . :

select * from table1 
where dt =  FROM_UNIXTIME(
                UNIX_TIMESTAMP(
                    DATE_SUB(
                        FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')
                    , 1)
                )
            , 'yyyyMMdd')     limit 10;
+1

Use this:

select * from table1 where dt = date_format(concat(year(date_sub(current_timestamp,1)),'-', month(date_sub(current_timestamp,1)), '-', day(date_sub(current_timestamp,1))), 'yyyyMMdd') limit 10;

This will give the deterministic result (row) of your section.

I know that he is very verbose.

0
source

More articles:


All Articles