0

I have a set of timestamps in this format "13:09:56.176 ... up till say "13:46:09.145". I am interested in extracting the seconds only so that the output reads from 0 to say 2000 seconds. I am not particular about the time the data capturing started but just the duration.

I tried splitting the columns in Excel to extract the seconds eg 56.176 but was not able to sequence them.

Clax
  • 13
  • 1
  • 1
    Share the actual inputs and the codes that you've tried. – AdamGold Jun 06 '19 at 07:26
  • Possible duplicate of [How to calculate the time interval between two time strings](https://stackoverflow.com/questions/3096953/how-to-calculate-the-time-interval-between-two-time-strings) – Vishnu Dasu Jun 06 '19 at 07:29

3 Answers3

0

In matlab you can do this:

frmt='HH:MM:SS.FFF'; 
t0=datenum('13:00:00.000',frmt); %define t0 (first sample?)
seconds=24*3600*(datenum('13:09:56.176',frmt)-t0);

In excel you can just type in the result column the formula =(A2-$A$1)*24*3600 (assuming Col A is the input column and t0=A1)

Mendi Barel
  • 3,350
  • 1
  • 23
  • 24
  • Thanks for all your responses. To clarify, I have a timestamp in column A ranging from 13:09:56.176 to 13:12:18.258 down the row. I want the output in column B to be the seconds interval from the begining of the event. This means each succeeding row of column B would contain the result of subrtraction from the first point ie 56.176 to the next. – Clax Jun 06 '19 at 09:34
  • There is the need to account for the period when the seconds get to 59 to 00 and start all over again [ie from ...56, 57, 59, 00, 01, ....] in the next minutes. As I earlier mentioned, Column B in this case should then contain the duration starting from epoch 0.000 up to the end of the time stamp as shown in the sketch. Any help in python or excel is welcome. Thanks. – Clax Jun 06 '19 at 09:35
0

In excel (which you mention, but maybe you are not looking for an excel solution?):

just strip the integer part of the number that excel uses to represent the date, and multiply by (24*60*60) to give seconds. It will range from 1 (00:00:01 to 86399 23:59:59).

If date/time is in A1, excel code would look like

=(A1-ROUNDDOWN(A1,0))*86400
  • all this is assuming that excel is storing the date/time properly, and not as a string.
Burgertron
  • 76
  • 6
0

I assume you can successfully fetch Timestamp 1 and Timestamp 2, or T1, T2. Now you can use datetime library in python to find the difference. Here it is:

from datetime import datetime
#Setting format of Time. For Insight, Refer  
#https://docs.python.org/2/library/datetime.html#strftime-and-strptime-behavior 
fmt = '%H:%M:%S.%f'

t1 = datetime.strptime('13:01:00.001', fmt)
t2 = datetime.strptime('13:02:00.002', fmt)

td = t2 - t1

print(td.total_seconds())
#60.001

Hope it helps

  • In order for other developers to take reference when they face same problem as your's, you need to accept the answer if it has solved your issue. –  Jun 09 '19 at 08:03