Search code examples
sqlimpala

SQL to calculate the number of minutes or hours (2 d.p) between two Impala Dates

Morning All,

I am working with Impala SQL, have a date field (LAST_RESOLVED_DATE) and would like to calculate the total number of minutes or hours (to 2 decimal places) from now and the LAST_RESOLVED_DATE

from_unixtime(Cast(hpd_help_desk.LAST_RESOLVED_DATE AS BIGINT),'yyyy-MM-dd HH:mm:ss')   AS "INC_LAST_RESOLVE_DATE",

Desired result:

inc_last_resolve_date   currwent_date_time           hours_since_resolution     minutes_since_resolution
2022-11-22 00:50:46 2022-11-22 12:41:45.508684000    11.85                      710.99 
2022-11-21 08:18:46 2022-11-22 12:41:45.508684000    28.38                      1,702.99 
2022-11-21 05:20:00 2022-11-22 12:41:45.508684000    31.36                      1,881.76 
2022-11-21 04:23:24 2022-11-22 12:41:45.508684000    32.31                      1,938.36 
2022-11-20 08:36:17 2022-11-22 12:41:45.508684000    52.09                      3,125.48 
2022-11-20 07:33:51 2022-11-22 12:41:45.508684000    53.13                      3,187.91 
2022-11-20 05:59:22 2022-11-22 12:41:45.508684000    54.71                      3,282.39 
2022-11-21 05:08:12 2022-11-22 12:41:45.508684000    31.56                      1,893.56

This is my attempt using DateDiff:

DATEDIFF(   NOW(),    
            TO_DATE(from_unixtime(Cast(hpd_help_desk.LAST_RESOLVED_DATE AS BIGINT),'yyyy-MM-dd HH:mm:ss')))*24 + hour(CURRENT_DATE()) - hour(TO_DATE(from_unixtime(Cast(hpd_help_desk.LAST_RESOLVED_DATE AS BIGINT),'yyyy-MM-dd HH:mm:ss'))
            ) AS "HOURS_SINCE_RESOLUTION",

This yield the following results , not exactly what I wanted as integer results are not granular enough.

inc_last_resolve_date   currwent_date_time              hours_since_resolution
2022-11-22 00:50:46     2022-11-22 12:41:45.508684000   0
2022-11-21 08:18:46     2022-11-22 12:41:45.508684000   24
2022-11-21 05:20:00     2022-11-22 12:41:45.508684000   24
2022-11-21 04:23:24     2022-11-22 12:41:45.508684000   24
2022-11-20 08:36:17     2022-11-22 12:41:45.508684000   48
2022-11-20 07:33:51     2022-11-22 12:41:45.508684000   48
2022-11-20 05:59:22     2022-11-22 12:41:45.508684000   48
2022-11-21 05:08:12     2022-11-22 12:41:45.508684000   24

I tried the following as an alternative for seconds

(UNIX_TIMESTAMP(Cast(NOW())) - UNIX_TIMESTAMP(Cast(hpd_help_desk.LAST_RESOLVED_DATE)))                AS "MINUTES_SINCE_RESOLUTION",

I get an error on this one:

ParseException: Syntax error in line 10:undefined: ...(UNIX_TIMESTAMP(Cast(NOW())) - UNIX_TIMESTAMP(Cast(hpd... ^ Encountered: ) Expected: AND, AS, BETWEEN, DIV, ILIKE, IN, IREGEXP, IS, LIKE, NOT, OR, REGEXP, RLIKE CAUSED BY: Exception: Syntax error

Peter

Solution

  • I think you're doing great. Only fix little syntax error.

    UNIX_TIMESTAMP(now())-
UNIX_TIMESTAMP(Cast(hpd_help_desk.LAST_RESOLVED_DATE as timestamp) timestamp_diff_in_secods,
(UNIX_TIMESTAMP(now())-
UNIX_TIMESTAMP(Cast(hpd_help_desk.LAST_RESOLVED_DATE as timestamp)) /60 timestamp_diff_in_minutes,
Round((UNIX_TIMESTAMP(now())-
UNIX_TIMESTAMP(Cast(hpd_help_desk.LAST_RESOLVED_DATE as timestamp)) /60,2) timestamp_diff_in_minutes_2decimal

    Explanation -

    now() will return a timestamp. Cast( str as timestamp) will aslo return timestamp. Unix_timestamp returns seconds from 1970-1-1 in bigint.