1

I have a date field names "dts" in string format. I want to find out all the records based on their time differences (in hours). The run event time should be greater than or equal to eat event.

enter image description here

The output should be:

enter image description here

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Sarah
  • 151
  • 1
  • 3
  • 10

1 Answers1

1

Convert timestamps to seconds, then subtract, divide result by 3600 to get hours, use case+count to count by ranges, something like this:

select count(case when diff_hrs >24 then 1 end) as more_24,
       count(case when diff_hrs <=24 then 1 end) as less_than_24,
       ...
       count(case when diff_hrs >=2 and diff_hrs <=3 then 1 end) as hrs_2_to_3,
       ...
from
(
select
abs(unix_timestamp(dts) - unix_timestamp(dts-eat)))/60/60 as diff_hrs
from table
)s;
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • what if one of the date field is with T and Z ( 2016-05-05T13:37:30Z) and the other is without (2016-05-06 13:37:30.0). Would unix_timestamp still work? – Sarah May 18 '17 at 04:33
  • Convert dates like this: `from_unixtime(UNIX_TIMESTAMP("2017-01-01T05:01:10Z", "yyyy-MM-dd'T'HH:mm:ss'Z' "),"yyyy-MM-dd HH:mm:ss")` returns `2017-01-01 05:01:10` – leftjoin May 18 '17 at 05:20
  • from_unixtime(unix_timestamp(columnname, "yyyy-MM-dd'T'HH:mm:ss'Z' "),"yyyy-MM-dd HH:mm:ss") - would this return everything in a column in the same format? – Sarah May 18 '17 at 05:23
  • from_unixtime(unix_timestamp(dts-eat, "yyyy-MM-dd'T'HH:mm:ss'Z' "),"yyyy-MM-dd HH:mm:ss") - this is returning NULL – Sarah May 18 '17 at 05:46
  • from_unixtime(UNIX_TIMESTAMP("2017-01-01T05:01:10Z", "yyyy-MM-dd'T'HH:mm:ss'Z' "),"yyyy-MM-dd HH:mm:ss") - this is also returning NULL – Sarah May 18 '17 at 05:50
  • Tested: `select from_unixtime(UNIX_TIMESTAMP("2017-01-01T05:01:10Z", "yyyy-MM-dd'T'HH:mm:ss'Z'"),"yyyy-MM-dd HH:mm:ss");` OK `2017-01-01 05:01:10` Time taken: 0.062 seconds, Fetched: 1 row(s) – leftjoin May 18 '17 at 06:54
  • the last one worked..i think your first answer had an extra space after 'Z'. thanks :) – Sarah May 18 '17 at 07:05
  • if the timestamp is in PST can i use the following to convert it to UTC: to_utc_timestamp(tablename.fieldname, 'PST') – Sarah May 30 '17 at 00:32
  • Sure you can. I have checked, it works good in Hive 1.2.1. `select current_timestamp, to_utc_timestamp(current_timestamp, 'PST');` OK `2017-05-29 23:51:25.719 2017-05-30 06:51:25.719` – leftjoin May 30 '17 at 06:51