Calculating Time Difference by Id

Calculating time difference by ID

With base R you could simply wrap it up in ave

ave(as.numeric(as.POSIXct(date)), Incident.ID.., FUN = padded.diff) 

Or using data.table (as per @akruns comment)

library(data.table) 
setDT(df)[, date.diff := padded.diff(as.POSIXct(date)), by = Incident.ID..]

Time differences for multiple entries for same ID in R

You may use lead to get next value of time.decimal with default value as 7.

library(dplyr)

user %>%
  group_by(id) %>%
  mutate(time.interval = lead(time.decimal, default = 7) - time.decimal) %>%
  ungroup() -> user

df

#     id hours    time.decimal time.interval
#  <int> <chr>           <dbl>         <dbl>
#1   123 03:32:12          1.2           3.3
#2   123 12:37:56          4.5           2.5
#3   140 09:46:33          6.3           0.7

Or in data.table :

library(data.table)

setDT(user)[, time.interval := shift(time.decimal, type = 'lead', fill = 7) - time.decimal, id]

data

user <- structure(list(id = c(123L, 123L, 140L), hours = c("03:32:12", 
"12:37:56", "09:46:33"), time.decimal = c(1.2, 4.5, 6.3)), 
class = "data.frame", row.names = c(NA, -3L))

How to calculate the ID time difference

Assuming your dataframe looks like

    TimeStamp Input  ID
0        1163     T   0
1        1163     T   1
2        1163     T   2
3        1163     T   3
4        1176     T   0
5        1176     T   1
6        1176     T   2
7        1176     T   3
8        1190     T   0
9        1190     T   1
10       1190     T   2

You can groupby the ID and use pd.diff() on each group and add that back into the results.

df["diff"] = df.groupby("ID")["TimeStamp"].diff()

    TimeStamp Input  ID  diff
0        1163     T   0   NaN
1        1163     T   1   NaN
2        1163     T   2   NaN
3        1163     T   3   NaN
4        1176     T   0  13.0
5        1176     T   1  13.0
6        1176     T   2  13.0
7        1176     T   3  13.0
8        1190     T   0  14.0
9        1190     T   1  14.0
10       1190     T   2  14.0

Note: the sort order of the dataframe is important. It must be sorted by TimeStamp in ascending order. If you need to do that, you can first use df.sort_values("TimeStamp", inplace=True).

calculate difference based on id and date

library(dplyr)

df <- structure(list(
    id = c("a", "a", "b", "b", "b", "c"),
    date = ("2018-04-13", "2011-11-12", "2019-05-30", "2014-09-13", "2019-06-21", "1998-01-08"),
    time = c("50", "40", "30", "20", "10", "30")),
  class = "data.frame", row.names = c(NA, -6L))

df %>%
  group_by(id) %>%
  arrange(id, date) %>%
  mutate(
    time = as.numeric(time),
    time_diff = time - lag(time)
  )

SQL - Time difference between 2 entries grouped by unique ID

I don't know which SQL dialect you use, so I tried to write a query very close to standard SQL (for example, SQL:2003), but using Postgres 8.4. For the character representation of date values, I used the format defined in ISO 8601.

create table T (
  ID char(2),
  Action char(1),
  "Timestamp" timestamp
);

insert into T values
  ('01', 'A', '2019-06-06T06:25'),
  ('01', 'B', '2019-06-06T06:30'),
  ('01', 'A', '2019-06-06T06:35'),
  ('01', 'B', '2019-06-06T06:40'),
  ('01', 'A', '2019-06-06T06:45'),
  ('03', 'B', '2019-07-01T08:25'),
  ('03', 'B', '2019-07-01T08:30'),
  ('10', 'B', '2019-07-02T09:40'),
  ('10', 'A', '2019-07-02T09:45'),
  ('10', 'A', '2019-07-02T09:50'),
  ('10', 'B', '2019-07-02T09:55');

select
  a.ID, extract(minute from (min(b."Timestamp") - a.min_ts)) as Difference
from (select
        t.ID, min(t."Timestamp") as min_ts
      from T as t
      where t.Action = 'A'
      group by t.ID, t.Action) as a
inner join T as b
  on a.ID = b.ID and b.Action = 'B' and a.min_ts < b."Timestamp"
group by a.ID, a.min_ts;

Output:

| id | difference |
+----+------------+
| 10 |         10 |
| 01 |          5 |

Test it online with SQL Fiddle.

Calculate time difference between observations for some rows only

Using match get the corresponding date where outcome = 1 for a id. Change the values which are before the outcome date to 0 along with those values which do not have any value with outcome = 1.

library(dplyr)

df %>%
  mutate(date = as.Date(date)) %>%
  group_by(id) %>%
  mutate(days_since_outcome_1 = as.integer(date - date[match(1, outcome)]), 
         days_since_outcome_1 = replace(days_since_outcome_1, 
               days_since_outcome_1 < 0 | is.na(days_since_outcome_1), 0)) %>%
  ungroup

#   id    date       outcome days_since_outcome_1
#  <chr> <date>       <int>                <dbl>
#1 ny21  2021-03-01       0                   28
#2 ny21  2021-02-01       1                    0
#3 ny21  2021-01-01       0                    0
#4 ch67  2021-08-09       0                    0

For larger datasets, you can try data.table -

setDT(df)
df
df[, days_since_outcome_1 := as.integer(date - date[match(1, outcome)]), id]
df[, days_since_outcome_1 := replace(days_since_outcome_1, 
            days_since_outcome_1 < 0 | is.na(days_since_outcome_1), 0)]
df

Calculate Time difference between two operation using kusto query

datatable(['timestamp [IST]']:datetime, name:string)
[
    "2022-06-30 04:10:00.460" ,"fbf759a0-d4be-4d07-adfb-7090a207e667  Success: Reached Three"
   ,"2022-06-30 04:12:00.091" ,"fbf759a0-d4be-4d07-adfb-7090a207e667  Success: Reached Two"
   ,"2022-06-30 04:10:00.460" ,"ewerewtetete-4d07-adfb-7090a207e667 Retry message"
   ,"2022-06-30 04:14:10.791" ,"fbf759a0-d4be-4d07-adfb-7090a207e667  Success: Reached One"
   ,"2022-06-30 04:15:10.460" ,"ewerewtetete-4d07-adfb-7090a207e667  Success: Reached Three"
   ,"2022-06-30 04:20:04.343" ,"fbf759a0-d4be-4d07-adfb-7090a207e667 Retry message"
]  
|   project-rename ts = ['timestamp [IST]']
|   parse name with OperationID " " *
|   summarize TimeDiffSec = round((max(ts) - min(ts)) / 1s) by OperationID

time1 = discord.utils.snowflake_time(int(id1))
time2 = discord.utils.snowflake_time(int(id2))
ts_diff = time2 - time1
secs = abs(ts_diff.total_seconds())

import datetime

def snowflake_time(id):
    return datetime.datetime.utcfromtimestamp(((id >> 22) + 1420070400000) / 1000)