Turn number to datetime

27 views (last 30 days)
Dylan den Hartog
Dylan den Hartog on 16 May 2021
Edited: Cris LaPierre on 17 May 2021
Is there an easy way to convert a number, e.g. 101416517, to a time: 10:14:16.517?
Thanks in advance!

Accepted Answer

Cris LaPierre
Cris LaPierre on 17 May 2021
Edited: Cris LaPierre on 17 May 2021
Not the prettiest, but once we look at the actual data, the approach shared earlier need to be customized.
Your PacketCounter column appears to be the information you want to convert to time of day. Row 8 contains the date information. Since I assume the goal is to automate this, I suggest using textscan to capture the date, then readtable to read in the table of values.
% Capture the start date
fid = fopen('Enkel_links_20210403_235559_998.csv');
% Textscan does not support time zones, so read in as a string and convert.
hdr = textscan(fid,'%*s %q %*[^eof]','HeaderLines',7,'Delimiter',';');
startD = datetime(hdr{1}{1},'InputFormat','yyyy-MM-dd HH:mm:ss.SSS Z','TimeZone','Europe/London',"Format","dd-MMM-uuuu HH:mm:ss.SSS");
startD.TimeZone = ''
startD = datetime
03-Apr-2021 23:59:58.000
fclose(fid);
I dropped the time zone. You can keep it if you want.
With the date, you can now work out the process for converting PacketCounter to times. You can use pieces of what has been shown already.
T = readtable('Enkel_links_20210403_235559_998.csv','NumHeaderLines',10)
T = 5×11 table
PacketCounter SampleTimeFine Euler_X Euler_Y Euler_Z FreeAcc_X FreeAcc_Y FreeAcc_Z Gyr_X Gyr_Y Gyr_Z _____________ ______________ ___________________________ __________________________ __________________________ __________ __________ _________ ________ _________ _________ 2.3596e+08 3.9612e+09 {'-1.202.525.019.645.690' } {'33.681.910.037.994.300'} {'-995.186.538.696.289' } -0.010683 0.0056109 0.15428 -0.34185 -0.10859 -0.078413 2.3596e+08 3.9612e+09 {'-12.024.426.460.266.100'} {'33.681.600.093.841.500'} {'-9.951.932.525.634.760'} 0.0028689 -0.0023478 0.15526 -0.30783 -0.083088 -0.053146 0 3.9612e+09 {'-1.203.233.242.034.910' } {'3.368.135.452.270.500' } {'-9.951.834.869.384.760'} -0.0062543 0.005794 0.15089 -0.36434 -0.084138 0.017167 1 3.9613e+09 {'-1.202.520.489.692.680' } {'3.368.227.243.423.460' } {'-9.951.670.837.402.340'} 0.006976 0.0061645 0.16285 -0.2752 -0.07753 0.037337 2 3.9613e+09 {'-12.029.764.652.252.100'} {'3.369.213.342.666.620' } {'-9.951.692.199.707.030'} 0.0017199 -0.01065 0.15238 -0.33982 -0.022001 -0.05613
% Get start date without time
D0 = dateshift(startD,"start","day")
D0 = datetime
03-Apr-2021 00:00:00.000
% Convert PacketNumber to strings, padding with zeros so always 9 characters
% Then convert strings to durations by just keeping time of day
spf = @(x) sprintf('%09d',x);
% Add result to a new column to the end of the table
T.Datetime = timeofday(datetime(rowfun(spf,T(:,'PacketCounter'),"OutputFormat","cell"),'InputFormat',"HHmmssSSS"))
T = 5×12 table
PacketCounter SampleTimeFine Euler_X Euler_Y Euler_Z FreeAcc_X FreeAcc_Y FreeAcc_Z Gyr_X Gyr_Y Gyr_Z Datetime _____________ ______________ ___________________________ __________________________ __________________________ __________ __________ _________ ________ _________ _________ ________ 2.3596e+08 3.9612e+09 {'-1.202.525.019.645.690' } {'33.681.910.037.994.300'} {'-995.186.538.696.289' } -0.010683 0.0056109 0.15428 -0.34185 -0.10859 -0.078413 23:59:59 2.3596e+08 3.9612e+09 {'-12.024.426.460.266.100'} {'33.681.600.093.841.500'} {'-9.951.932.525.634.760'} 0.0028689 -0.0023478 0.15526 -0.30783 -0.083088 -0.053146 23:59:59 0 3.9612e+09 {'-1.203.233.242.034.910' } {'3.368.135.452.270.500' } {'-9.951.834.869.384.760'} -0.0062543 0.005794 0.15089 -0.36434 -0.084138 0.017167 00:00:00 1 3.9613e+09 {'-1.202.520.489.692.680' } {'3.368.227.243.423.460' } {'-9.951.670.837.402.340'} 0.006976 0.0061645 0.16285 -0.2752 -0.07753 0.037337 00:00:00 2 3.9613e+09 {'-12.029.764.652.252.100'} {'3.369.213.342.666.620' } {'-9.951.692.199.707.030'} 0.0017199 -0.01065 0.15238 -0.33982 -0.022001 -0.05613 00:00:00
% Add start date to duration
T.Datetime = T.Datetime + D0
T = 5×12 table
PacketCounter SampleTimeFine Euler_X Euler_Y Euler_Z FreeAcc_X FreeAcc_Y FreeAcc_Z Gyr_X Gyr_Y Gyr_Z Datetime _____________ ______________ ___________________________ __________________________ __________________________ __________ __________ _________ ________ _________ _________ ________________________ 2.3596e+08 3.9612e+09 {'-1.202.525.019.645.690' } {'33.681.910.037.994.300'} {'-995.186.538.696.289' } -0.010683 0.0056109 0.15428 -0.34185 -0.10859 -0.078413 03-Apr-2021 23:59:59.998 2.3596e+08 3.9612e+09 {'-12.024.426.460.266.100'} {'33.681.600.093.841.500'} {'-9.951.932.525.634.760'} 0.0028689 -0.0023478 0.15526 -0.30783 -0.083088 -0.053146 03-Apr-2021 23:59:59.999 0 3.9612e+09 {'-1.203.233.242.034.910' } {'3.368.135.452.270.500' } {'-9.951.834.869.384.760'} -0.0062543 0.005794 0.15089 -0.36434 -0.084138 0.017167 03-Apr-2021 00:00:00.000 1 3.9613e+09 {'-1.202.520.489.692.680' } {'3.368.227.243.423.460' } {'-9.951.670.837.402.340'} 0.006976 0.0061645 0.16285 -0.2752 -0.07753 0.037337 03-Apr-2021 00:00:00.001 2 3.9613e+09 {'-12.029.764.652.252.100'} {'3.369.213.342.666.620' } {'-9.951.692.199.707.030'} 0.0017199 -0.01065 0.15238 -0.33982 -0.022001 -0.05613 03-Apr-2021 00:00:00.002
% Determine new day as when there is a negative change in time
df = [0; diff(T.Datetime)<0]
df = 5×1
0 0 1 0 0
% Use cumsum on logical array to indicate how many days to add to date
T.Datetime = T.Datetime + cumsum(days(df))
T = 5×12 table
PacketCounter SampleTimeFine Euler_X Euler_Y Euler_Z FreeAcc_X FreeAcc_Y FreeAcc_Z Gyr_X Gyr_Y Gyr_Z Datetime _____________ ______________ ___________________________ __________________________ __________________________ __________ __________ _________ ________ _________ _________ ________________________ 2.3596e+08 3.9612e+09 {'-1.202.525.019.645.690' } {'33.681.910.037.994.300'} {'-995.186.538.696.289' } -0.010683 0.0056109 0.15428 -0.34185 -0.10859 -0.078413 03-Apr-2021 23:59:59.998 2.3596e+08 3.9612e+09 {'-12.024.426.460.266.100'} {'33.681.600.093.841.500'} {'-9.951.932.525.634.760'} 0.0028689 -0.0023478 0.15526 -0.30783 -0.083088 -0.053146 03-Apr-2021 23:59:59.999 0 3.9612e+09 {'-1.203.233.242.034.910' } {'3.368.135.452.270.500' } {'-9.951.834.869.384.760'} -0.0062543 0.005794 0.15089 -0.36434 -0.084138 0.017167 04-Apr-2021 00:00:00.000 1 3.9613e+09 {'-1.202.520.489.692.680' } {'3.368.227.243.423.460' } {'-9.951.670.837.402.340'} 0.006976 0.0061645 0.16285 -0.2752 -0.07753 0.037337 04-Apr-2021 00:00:00.001 2 3.9613e+09 {'-12.029.764.652.252.100'} {'3.369.213.342.666.620' } {'-9.951.692.199.707.030'} 0.0017199 -0.01065 0.15238 -0.33982 -0.022001 -0.05613 04-Apr-2021 00:00:00.002
% Convert to timetable
TT = table2timetable(T,'RowTimes','Datetime')
TT = 5×11 timetable
Datetime PacketCounter SampleTimeFine Euler_X Euler_Y Euler_Z FreeAcc_X FreeAcc_Y FreeAcc_Z Gyr_X Gyr_Y Gyr_Z ________________________ _____________ ______________ ___________________________ __________________________ __________________________ __________ __________ _________ ________ _________ _________ 03-Apr-2021 23:59:59.998 2.3596e+08 3.9612e+09 {'-1.202.525.019.645.690' } {'33.681.910.037.994.300'} {'-995.186.538.696.289' } -0.010683 0.0056109 0.15428 -0.34185 -0.10859 -0.078413 03-Apr-2021 23:59:59.999 2.3596e+08 3.9612e+09 {'-12.024.426.460.266.100'} {'33.681.600.093.841.500'} {'-9.951.932.525.634.760'} 0.0028689 -0.0023478 0.15526 -0.30783 -0.083088 -0.053146 04-Apr-2021 00:00:00.000 0 3.9612e+09 {'-1.203.233.242.034.910' } {'3.368.135.452.270.500' } {'-9.951.834.869.384.760'} -0.0062543 0.005794 0.15089 -0.36434 -0.084138 0.017167 04-Apr-2021 00:00:00.001 1 3.9613e+09 {'-1.202.520.489.692.680' } {'3.368.227.243.423.460' } {'-9.951.670.837.402.340'} 0.006976 0.0061645 0.16285 -0.2752 -0.07753 0.037337 04-Apr-2021 00:00:00.002 2 3.9613e+09 {'-12.029.764.652.252.100'} {'3.369.213.342.666.620' } {'-9.951.692.199.707.030'} 0.0017199 -0.01065 0.15238 -0.33982 -0.022001 -0.05613
As an aside, would it make more sense to use SampleTimeFine? You'd have to figure out what zero time is, but it appears 1 ms is equal to a change of 16,667, or 1E6/60. That may have something to do with the indicated OutputRate: 60Hz;

More Answers (4)

Cris LaPierre
Cris LaPierre on 16 May 2021
Edited: Cris LaPierre on 16 May 2021
d=101416517;
D=datetime(num2str(d),'InputFormat','HHmmssSSS',"Format","HH:mm:ss.SSS")
D = datetime
10:14:16.517
  2 Comments
Dylan den Hartog
Dylan den Hartog on 16 May 2021
This works perfectly. Thanks a lot!
Cris LaPierre
Cris LaPierre on 16 May 2021
dpb makes a good point that a duration may be more appropriate if you truly just want the time. Here is an approach to get that:
d=101416517;
D=datetime(num2str(d),'InputFormat','HHmmssSSS')
D = datetime
16-May-2021 10:14:16
D = D-dateshift(D,"start","day");
D.Format="hh:mm:ss.SSS"
D = duration
10:14:16.517

Sign in to comment.


dpb
dpb on 16 May 2021
>> dtn=datetime(string(tn),'InputFormat','HHmmssSSS','Format','HHmmss.SSS')
dtn =
datetime
101416.517
>>
NB: However, a MATLAB datetime must include a date; in the above without an input date field, the current date at the local time of the conversion will be used -- for example, carrying on from above
>> dtn.Format='default'
dtn =
datetime
16-May-2021 10:14:16
>>
To hold only the time, one would have to use a duration variable, but, unfortunately, duration() is a weak sibling --
>> duration(string(tn),'InputFormat','HHmmssSSS','Format','hh:mm:ss.SSS')
Error using duration (line 299)
Unsupported format 'HHmmssSSS'. See the documentation of 'InputFormat' for valid formats.
>>
The general input format strings aren't recognized; nor can you be fully general in the output format requested. Real slip-up in quality of implementation arena.
So, you can coerce in round about way by subtracting the y,m,d from the datetime value...
>> dun=duration(dtn-datetime(year(dtn),month(dtn),day(dtn)),'Format','hh:mm:ss.SSS')
dun =
duration
10:14:16.517
>>
Alternatively, you can format the string in a recognizable pattern...with some effort
>> stn=num2str(tn);
>> sprintf('%2s:%2s:%2s.%3s',stn(1:2),stn(3:4),stn(5:6),stn(7:9))
ans =
'10:14:16.517'
>>
Or, you can do all the arithmetic to modulo the pieces by the proper powers of 10 to numerically separate out the pieces...

Dylan den Hartog
Dylan den Hartog on 16 May 2021
Thanks for the answers.
Is it possble to create a datetime array by by specifying the start date, e.g. 03-04-2021?
The issue is that I have an Excel file with the times represented as numbers, e.g. 101416517 which stands for 10:14:16.517. I can extract the date which the measurement started in the Excel file name, so e.g. 03042021. Now I want to combine this start date with all the timestamps to create a datetime array in MATLAB.
So lets say I have this data:
start_date_number = 03042021
time_numbers = [101416488; 101416490; 101416517; 101416546; 101416548]
and I want to get this:
D =
5×1 datetime array
03-04-2021 10:14:16.488
03-04-2021 10:14:16.490
03-04-2021 10:14:16.517
03-04-2021 10:14:16.546
03-04-2021 10:14:16.548
  3 Comments
Dylan den Hartog
Dylan den Hartog on 16 May 2021
This is nice. Thank you.
The only problem is when the time changes from 03-04-2021 23:59:59.999 to 00:00:00.000 the date will stay the same with this method, right? So instead of 04-04-2021 00:00:00.000 it will be 03-04-2021 00:00:00.000.
Is there a way to fix this?
Cris LaPierre
Cris LaPierre on 16 May 2021
Yes, if your times reset to 0 each day, this will be a problem. What data do you have to work with? Could you share a data set? You can attach it using the paperclip icon.

Sign in to comment.


Dylan den Hartog
Dylan den Hartog on 16 May 2021
I shared the file.
As you can see the time starts on 03-04-2021 23:59:59.998 and end on 04-04-2021 00:00:00.002.
I can extract the start date 03-04-2021 from the filename. However, the rows in the file only contain the time for each measurement point, so I need to somehow use this extracted start date to create a datetime array which proceeds normally.
In MATLAB I want to get this:
D = 5×1 datetime array
03-04-2021 23:59:59.998
03-04-2021 23:59:59.999
04-04-2021 00:00:00.000
04-04-2021 00:00:00.001
04-04-2021 00:00:00.002
  1 Comment
Steven Lord
Steven Lord on 16 May 2021
Let's take a series of datetime values.
N = datetime('now')
N = datetime
16-May-2021 18:39:11
dur = hours(randi([0 5], 10, 1)) + ... % Between 0 and 5 hours from now
minutes(randi([0 59], 10, 1)) + ... % and 0 to 59 minutes
seconds(randi([0 59], 10, 1)); % and 0 to 59 seconds
dur.Format = 'hh:mm:ss';
dur = sort(dur)
dur = 10×1 duration array
00:25:12 00:56:25 01:06:11 01:35:30 02:10:38 02:47:41 03:27:35 04:08:11 05:42:08 05:51:29
DT = N+dur
DT = 10×1 datetime array
16-May-2021 19:04:23 16-May-2021 19:35:36 16-May-2021 19:45:22 16-May-2021 20:14:41 16-May-2021 20:49:49 16-May-2021 21:26:52 16-May-2021 22:06:46 16-May-2021 22:47:22 17-May-2021 00:21:19 17-May-2021 00:30:40
Given N and the datetime array DT we can recreate dur.
offset = DT - N
offset = 10×1 duration array
00:25:12 00:56:25 01:06:11 01:35:30 02:10:38 02:47:41 03:27:35 04:08:11 05:42:08 05:51:29
And if we want to figure out what would happen if we had started this count from say 4 AM today instead of from N:
newbase = datetime(2021, 5, 16, 4, 0, 0)
newbase = datetime
16-May-2021 04:00:00
newDT = newbase + offset
newDT = 10×1 datetime array
16-May-2021 04:25:12 16-May-2021 04:56:25 16-May-2021 05:06:11 16-May-2021 05:35:30 16-May-2021 06:10:38 16-May-2021 06:47:41 16-May-2021 07:27:35 16-May-2021 08:08:11 16-May-2021 09:42:08 16-May-2021 09:51:29
The times in newDT should be about 14.5 hours before the times in DT.
difference = DT - newDT
difference = 10×1 duration array
14:39:11 14:39:11 14:39:11 14:39:11 14:39:11 14:39:11 14:39:11 14:39:11 14:39:11 14:39:11
differenceInHours = hours(difference)
differenceInHours = 10×1
14.6532 14.6532 14.6532 14.6532 14.6532 14.6532 14.6532 14.6532 14.6532 14.6532
In general:
  • datetime + datetime gives an error (rather than a play between the pitcher and catcher.)
  • datetime - datetime gives a duration
  • datetime + duration gives a datetime
  • datetime - duration gives a datetime
  • duration + datetime gives a datetime
  • duration - datetime gives an error
  • duration + duration gives a duration
  • duration - duration gives a duration

Sign in to comment.

Categories

Find more on Dates and Time in Help Center and File Exchange

Products


Release

R2020b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!