Convert YYYYMMDD String to Date in Impala

Question

I'm using SQL in Impala to write this query. I'm trying to convert a date string, stored in YYYYMMDD format, into a date format for the purposes of running a query like this:

SELECT datadate, 
       session_info
FROM database
WHERE datadate >= NOW() - INTERVAL 5 DAY
ORDER BY datadate DESC;

Since the >= NOW() - INTERVAL 5 DAY code won't work with the YYYYMMDD string, I'd like to find a way to convert that into a date format that will work with this type of query. My thought is that it should look something like this (based on similar questions about other SQL query editors), but it's not working in Impala:

SELECT datadate,
       session_info,
       convert(datetime, '20141008', 102) AS session_date
FROM database
WHERE session_date >= NOW() - INTERVAL 5 DAY
ORDER BY session_date DESC;

Anyone know how to do this in Impala?

EDIT:

I finally found a working solution to the problem. None of the attempts using configurations of CAST or CONVERT would work in Impala, but the below query solves the problem and is fully operational, allowing date math to be performed on a column containing string values:

SELECT datadate,
       session_info
FROM database
WHERE datadate >= from_unixtime(unix_timestamp(now() - interval 5 days), 'yyyyMMdd')
GROUP BY datadate
ORDER BY datadate DESC;

Solingen

look at month(20141008) and year(20141008) – Driver

@vkp the MONTH() and YEAR() functions work in Impala data stored as a date or timestamp, but it doesn't appear to work for string data. – Solingen

look at the documentation cloudera.com/content/cloudera/en/documentation/cloudera-impala/… – Driver

@vkp these functions don't seem to work for me with dates stored as YYYYMMDD strings. – Solingen

USE a cast(datadate as char (8)) – Interception

Answer 1

Native way:

to_timestamp(cast(date_number AS STRING), 'yyyyMMdd')

Recognizor

I'm having a similar problem, but this gives me NULL values: from_timestamp(cast(dateas STRING),'dd/mm/yyyy') as date Date is already a string, so I tied with or without casting to STRING, but still NULL results – Interception

Answer 2

See Timestamp Literals on [Link Updated 2020-08-24]:

https://docs.cloudera.com/cdp-private-cloud-base/7.1.3/impala-sql-reference/topics/impala-literals.html

You need to add the dashes to your string so Impala will be able to convert it into a date/timestamp. You can do that with something like:

concat_ws('-', substr(datadate,1,4), substr(datadate,5,2), substr(datadate,7) )

which you can use instead of datadate in your expression.

Norite

Perfect! Thank you – Nominalism

Answer 3

To ignore hour/minute/second... , use from_timestamp, result 2020-01-01.
select from_timestamp(cast('2020-01-01 01:01:01.000000' as TIMESTAMP),'yyyy-MM-dd');

Ebenezer