Support Questions

Find answers, ask questions, and share your expertise
Welcome to the upgraded Community! Read this blog to see What’s New!

Need the query to get the past 24 hours data from now.


I am looking for the query which brings me the pat 24 hours of data when ever I run a query. And I want store it in the local machine as CSV. Do any one of you have any idea about it. If so please can you please me.


Rising Star

Below query may help you with your use case?

select * from test where data>(select FROM_UNIXTIME(UNIX_TIMESTAMP()-86400))

Where data column holds data in timestamp format -> YYYY-mm-dd HH:MM:SS

To export using beeline below is command can be used.

beeline --outputformat=csv2 -u "JDBC_CONNECT_STRING" -n USERNAME -e "select * from test where data>(select FROM_UNIXTIME(UNIX_TIMESTAMP()-86400))" > /tmp/output.txt

OR if you want to exclude headers, you can run

beeline --showheader=false --outputformat=csv2 -u "JDBC_CONNECT_STRING" -n USERNAME -e "select * from test where data>(select FROM_UNIXTIME(UNIX_TIMESTAMP()-86400))" > /tmp/output.txt


@Chiran Ravani Is the query what you have here is correct ? I am getting some exceptions when I use it in Hive. In addition to that also I am trying to run select * from dnapm.pm_data where ts >(UNIX_TIMESTAMP() - 24*60*60) and it returns empty rows.

Here is the error that I am getting if I run your query. Might be you have got a typo in your query. I am just guessing/ Correct me if I am wrong.

<small>org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:33 cannot recognize input near 'select' 'FROM_UNIXTIME' '(' in expression specification

Rising Star

@Sai Krishna Makineni Somehow the query does not work on Hive 1.2.1, Your query looks good, can you check data format in ts column? I had in below format