[SOLVED] How to select records from week days?

How to select records from week days?

I have hive table which contain daily records. I want to select record from week days. So i use bellow hive query to do it. I'm using QUBOLE API to do this.

SELECT      hour(pickup_time),
            COUNT(passengerid)
FROM        home_pickup
WHERE       CAST(date_format(pickup_time, 'u') as INT) NOT IN (6,7)
GROUP BY    hour(pickup_time)

However when i run this code, It came with Bellow error.

SemanticException [Error 10011]: Line 4:12 Invalid function 'date_format'

Isn't Qbole support to date_format function? Are there any other way to select week days?

Solution

Use unix_timestamp(string date, string pattern) to convert given date format to seconds passed from 1970-01-01. Then use from_unixtime() to convert to given format:

Demo:

hive> select cast(from_unixtime(unix_timestamp('2017-08-21 10:55:00'),'u') as int);
OK
1

You can specify date pattern for unix_timestamp for non-standard format. See docs here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions