CODEWITHSUNDEEP
sqldatedatetimehivehiveql
Sid
Sid

Reputation: 163

Converting date format number to date and taking difference in SQL

I have a data set as below,

enter image description here

Same is date in "YYYYMMDD" format, I wanted to convert the columns to date format and take the difference between the same. I used to below code

SELECT to_date(statement_date_key::text, 'yyyymmdd') AS statement_date,
       to_date(paid_date_key::text, 'yyyymmdd') AS paid_date,
       statement_date - paid_date AS Diff_in_days
FROM Table
WHERE Diff_in_days >= 90
;

Idea is to convert both the columns to dates, take the difference between them and filter cases where difference in days is more than 90.

Later I was informed that server is supported by HiveSQL and does not support of using ":", date time, and temp tables can not be created. I'm currently stuck on how to go about given the constraints.

Help would be much appreciated.

Sample date for reference is provided in the link dbfiddle

Upvotes: 1

Views: 454

Answers (3)

leftjoin
leftjoin

Reputation: 38290

Simple way: Function unix_timestamp(string, pattern) converts string in given format to seconds passed from unix epoch, calculate difference in seconds then divide by (60*60*24) to get difference in days.

select * from
(
   select t.*,
          (unix_timestamp(string(paid_date_key), 'yyyyMMdd') - 
           unix_timestamp(string(statement_date_key), 'yyyyMMdd'))/86400 as Diff_in_days
    from Table t
) t
where Diff_in_days>=90

You may want to add abs() if the difference can be negative.

One more method using regexp_replace:

select * from
(
   select t.*,
          datediff(date(regexp_replace(string(paid_date_key), '(\\d{4})(\\d{2})(\\d{2})','$1-$2-$3')), 
                   date(regexp_replace(string(statement_date_key), '(\\d{4})(\\d{2})(\\d{2})','$1-$2-$3')))  as Diff_in_days
    from Table t
) t
where Diff_in_days>=90

Upvotes: 1

Chintan Trivedi
Chintan Trivedi

Reputation: 11

Hello You Can Use Below Query It Work Well

    select * from (
      select convert(date, statement_date_key) AS statement_date, 
        convert(date, paid_date) AS paid_date,
        datediff(D, convert(date, statement_date_key), convert(date, paid_date)) as Diff_in_days
      from Table
    ) qry 
    where Diff_in_days >= 90

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Hive is a little convoluted in its use of dates. You can use unix_timestamp() and work from there:

SELECT datediff(to_date(unix_timestamp(cast(statement_date_key as varchar(10)), 'yyyyMMdd')),
                to_date(unix_timestamp(cast(paid_date_key as varchar(10)), 'yyyyMMdd'))
              ) as diff_in_days
FROM Table;

Note that you need to use a subquery if you want to use diff_in_days in a where clause.

Also, if you have date keys, then presumably you also have a calendar table, which should make this much simpler.

Upvotes: 1

Related Questions