How to make a timespan column based on a time stamp column?

google sheets timestamp when cell is updated
static timestamp in google sheets
mysql timestamp default null
how to insert default value in timestamp column
insert date when cell is changed google sheets
mysql on update current_timestamp not working
mysql datetime default current_timestamp
mysql default current_timestamp not working

I have a datatable with timestamps in column 1 and events in column 2. The timestamps have the format Y-m-d H:M:OS3 (e.g. "2019-09-26 07:29:22,778").

I would like to add a new column with time-span values of the difference between timestamp 2 to timestamp 1, then 3 to 1 etc. E.g.:

timestamp                  event           diff in sec
2019-09-26 07:29:22,778    X                   
2019-09-26 07:29:23,918    Y               1.140
2019-09-26 07:29:25,118    Z               2.340
.
.

In base:

dt1$timediff <- cumsum(c(0, difftime(tail(dt1$timestamp,-1), head(dt1$timestamp,-1))))

or using data.table:

library(data.table)

dt1[ , timediff := cumsum(c(0, diff(as.numeric(timestamp))))][]
#>                  timestamp event timediff
#> 1: 2019-09-26 07:29:22.778     X     0.00
#> 2: 2019-09-26 07:29:23.917     Y     1.14
#> 3: 2019-09-26 07:29:25.118     Z     2.34

Another dplyr solution base off of akrun's answer:

library(dplyr)
dt1 %>%
  mutate(difftime = difftime(timestamp, timestamp[1], unit = 'sec'))

Data: N.B: I am using data.table to read the data.

fread(text="date time  event
 2019-09-26 07:29:22.778    X                   
 2019-09-26 07:29:23.918    Y               
 2019-09-26 07:29:25.118    Z") -> dt1

dt1$timestamp <- as.POSIXct(paste(dt1$date, dt1$time), format="%Y-%m-%d %H:%M:%OS")

dt1 <- dt1[,4:3]

3 Easy Ways to Automatically Insert Timestamps in Google Sheets , Insert TimeStamp Code in the Script in any cell in Column A, a timestamp  If two arguments are used (a start date column and a stop date column) the result will be a TimeSpan value displaying the total difference. If three arguments are used, the first argument should be the part to compare. The second argument is the start date column and the third argument is the stop date column.

We can use difftime

library(dplyr)
library(lubridate)
df1 %>%
   mutate(timestamp = ymd_hms(timestamp),
          diffinsec = cumsum(as.numeric(difftime(timestamp, 
       lag(timestamp, default = timestamp[1]), unit = 'sec'))))

TIMESTAMP, In the event that you have several tables and columns using temporal data types that you want to switch over to the new format, make sure the system variable is  Table1[Timestamp] <= DATE ( Year2; Month2; Day2 ) + TIME ( Hour2; Month2; Second2 ) ) where Table1 is your current table and Year1, Month1, etc and Year2, Month2, etc. are the initial and final date/time within which you want to keep dates (your Thursday and Sunday in your example),

Here a solution with dplyr. I assumed that you want the time difference from the first event. Otherwise @akrun's answer with lag() is the correct one.

library(dplyr)
df %>% 
  mutate(start = min(timestamp)) %>% 
  mutate(diff = timestamp - start)
#>             timestamp             event               start      diff
#> 1 2019-09-26 07:29:22                 X 2019-09-26 07:29:22 0.00 secs
#> 2 2019-09-26 07:29:23                 Y 2019-09-26 07:29:22 1.14 secs
#> 3 2019-09-26 07:29:25                 Z 2019-09-26 07:29:22 2.34 secs
data
df <- structure(list(timestamp = structure(c(1569479362.778, 1569479363.918, 
                                                 1569479365.118), class = c("POSIXct", "POSIXt"), tzone = ""), 
                         event = c("X", "Y", "Z")), row.names = c(NA, 
                                                                                  -3L), class = "data.frame")

11.2.5 Automatic Initialization and Updating for TIMESTAMP and , For any TIMESTAMP or DATETIME column in a table, you can assign the use DEFAULT 0 , a default that can produce warnings or errors depending on  Column E is "Total Number of Cases" (a simple Sum), and Column F is a "Calculated Case Load Value" which gives us a weighted value, since the cases in column C are 4 times as time-intensive, so the formula for column F is (let's say for the first nurse) =B5+ (C5*4)+D5.

Date and Time Functions, If a TimeSpan is to be added, two arguments are needed: a DateTime column and a which part of the date to get and Arg2 is the Date, Time or DateTime column. Returns the fiscal quarter for a date (Arg1), based on a shift specified in  GroupExpression: An expression over the columns, that provides a set of distinct values. Typically it's either a column name that already provides a restricted set of values, or bin() with a numeric or time column as argument.

Custom formatting of timespan columns, There are five different data values included in the TimeSpan format: days, hours, formatting of date and time columns · Custom formatting of timespan columns the custom format string allows you to create your own formats using a code Returns the number of hours with one or two digits, depending on the value. This article describes how to work with date and time data in Azure Monitor log queries. Date time basics. The Kusto query language has two main data types associated with dates and times: datetime and timespan. All dates are expressed in UTC. While multiple datetime formats are supported, the ISO8601 format is preferred.

public Timestamp - Timestamp, Negative second values with fractions must still have non-negative nanoseconds values that count forward in time. Must be from 0 to 999,999,999  TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp).. For any TIMESTAMP or DATETIME column in a table, you can assign the current timestamp as the default value, the auto-update value, or both:

Comments
  • Welcome to StackOverflow! Please read the info about how to ask a good question and how to give a reproducible example. This will make it much easier for others to help you.
  • How do I wrap it with as.numeric? In my hands, it results in integer values for the "diff in sec" column, however I would like to get the milliseconds as well (e.g. 2,340 sec).
  • No, they are character columns, only the new one "diffinsec" is numeric. How would you read in the data file while keeping the timestamp format intact and? Alternatively, how would you convert columns 1 and 2 to numeric columns?
  • @W.H.G. Not clear though. If you want to convert columns 1 to 2 to numeric df1 %>% mutate_at(1:2, as.numeric) %>%
  • Fine, thanks. But how do I get to values in "diff in sec" that include milliseconds (e.g.: 2,340 sec)?
  • Here, we used the unit as sec and if you check ?difftime, the units option includes only c("auto", "secs", "mins", "hours", "days", "weeks") so you may have to convert it to milliseconds by doing some arithmetic afterwards
  • Thanks. Since I still don't succeed to get to the right format, I would like to ask you how you would deal with an input text file that looks exactly like this: 2019-09-26 17:54:24,406 [218] INFO - [1] - Event X 2019-09-26 17:54:24,431 [207] INFO - [1] - Event Y 2019-09-26 17:54:24,438 [218] INFO - [1] - Event Z
  • I think you should ask a new question where you make your requirements a bit clearer. I don't know what you are getting at and your original problem seems to be solved.