How to splits the number into day, hour, minute and second format?

21 views (last 30 days)
vimal kumar chawda
vimal kumar chawda on 29 Jun 2022
Answered: Devang Tavkari on 2 Jul 2022
Ran in:
clc;
close all;
clear all;
num = xlsread("SensorsAP_example.csv");
Error using xlsread
Unable to read XLS file /users/mss.system.ANHaJr/SensorsAP_example.csv. File is not in recognized format.
T = days(1:4) + hour(8) + hours(1.2345)
day= num(:,3);
hour = num(:,3);
In 3rd column, I have timestamp. I want 1234567890 to split like first four digit 1234 means days, 56 hour, 78 minute and 90 is second. How can split the data in such format?
  4 Comments
Show 2 older comments
Stephen23
Stephen23 on 29 Jun 2022
Edited: Stephen23 on 29 Jun 2022
@Star Strider: well spotted. If interpreted as a Unix timestamp the first value 1219376117 corresponds to the entirely plausible Friday, 22nd August, 2008 at 3:35:17 AM. Another step/epoch is also possible.
Star Strider
Star Strider on 29 Jun 2022
@Stephen23 — Thank you!
I removed my original Comment and inserted it in my Answer.

Sign in to comment.

Sign in to answer this question.

Accepted Answer

madrix
madrix on 29 Jun 2022
Ran in:
I am understanding that you need to convert a 10digit number into days,hours,minutes and seconds.
Below example should work in this case.
num = 1234567890; % example
days = floor(num/(10^6))
days = 1234
hrs = floor(mod(num,10^6)/(10^4))
hrs = 56
mins = floor(mod(num,10^4)/(10^2))
mins = 78
secs = mod(num,10^2)
secs = 90

More Answers (2)

Star Strider
Star Strider on 29 Jun 2022
Ran in:
Maybe I’m mising something, however when I last checked, there are not 37 hours in a day or 61 minutes in an hour!
Try something like this —
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1049830/SensorsAP_example.csv', 'VariableNamingRule','preserve')
Warning: Table variable names were truncated to the length namelengthmax. The original names are saved in the VariableDescriptions property.
T1 = 9×75 table
timestamp sequenceNumber.value sensors.loopTimestampUs.value sensors.gnss[0].time.wnc.value sensors.gnss[0].time.tow.value sensors.gnss[0].time.status sensors.gnss[0].position.lon.value sensors.gnss[0].position.lat.value sensors.gnss[0].equirectangularPosition.northing.value sensors.gnss[0].equirectangularPosition.easting.value sensors.gnss[0].equirectangularPosition.status sensors.gnss[0].altitude.amsl.value sensors.gnss[0].altitude.wgs84.value sensors.gnss[0].altitude.status sensors.gnss[0].velocity.east.value sensors.gnss[0].velocity.north.value sensors.gnss[0].velocity.down.value sensors.gnss[0].covariances.position.lat.value sensors.gnss[0].covariances.position.lon.value sensors.gnss[0].covariances.position.altWgs84.value sensors.gnss[0].covariances.velocity.north.value sensors.gnss[0].covariances.velocity.east.value sensors.gnss[0].covariances.velocity.down.value sensors.gnss[0].solutionType.enum sensors.gnss[0].satelliteCount.value sensors.gnss[0].satelliteCount.status sensors.imu.acc.x.value sensors.imu.acc.y.value sensors.imu.acc.z.value sensors.imu.acc.status sensors.imu.gyr.x.value sensors.imu.gyr.y.value sensors.imu.gyr.z.value sensors.imu.gyr.status sensors.imu.gyroMaxNormDiff.value sensors.baro.pressure.value sensors.baro.temperature.value sensors.baro.status sensors.pitot.pressure.value sensors.pitot.temperature.value sensors.pitot.status sensors.lidar.value sensors.lidar.status sensors.battery[0].current.value sensors.battery[0].current.status sensors.battery[0].voltage.value sensors.battery[0].voltage.status sensors.battery[0].integratedCharge.value sensors.battery[0].integratedCharge.status sensors.battery[0].remainingCharge.value sensors.battery[0].remainingCharge.status sensors.battery[0].relativeStateOfCharge.value sensors.battery[0].relativeStateOfCharge.status sensors.temperatures.maxAutopilot.temperature.value sensors.temperatures.maxAutopilot.temperature.status sensors.temperatures.maxAutopilot.margin.value sensors.temperatures.maxAutopilot.source.device.enum sensors.temperatures.maxAutopilot.source.component.enum sensors.temperatures.maxStack.temperature.value sensors.temperatures.maxStack.temperature.status sensors.temperatures.maxStack.margin.value sensors.temperatures.maxStack.source.device.enum sensors.temperatures.maxStack.source.component.enum sensors.temperatures.maxDrone.temperature.value sensors.temperatures.maxDrone.temperature.status sensors.temperatures.maxDrone.margin.value sensors.temperatures.maxDrone.source.device.enum sensors.temperatures.maxDrone.source.component.enum sensors.temperatures.mostCriticalTemperature.temperature.value sensors.temperatures.mostCriticalTemperature.temperature.status sensors.temperatures.mostCriticalTemperature.margin.value sensors.temperatures.mostCriticalTemperature.source.device.enum sensors.temperatures.mostCriticalTemperature.source.component.e sensors.temperatures.outsideAir.value sensors.temperatures.outsideAir.status __________ ____________________ _____________________________ ______________________________ ______________________________ ___________________________ __________________________________ __________________________________ ______________________________________________________ _____________________________________________________ ______________________________________________ ___________________________________ ____________________________________ _______________________________ ___________________________________ ____________________________________ ___________________________________ ______________________________________________ ______________________________________________ ___________________________________________________ ________________________________________________ _______________________________________________ _______________________________________________ _________________________________ ____________________________________ _____________________________________ _______________________ _______________________ _______________________ ______________________ _______________________ _______________________ _______________________ ______________________ _________________________________ ___________________________ ______________________________ ___________________ ____________________________ _______________________________ ____________________ ___________________ ____________________ ________________________________ _________________________________ ________________________________ _________________________________ _________________________________________ __________________________________________ ________________________________________ _________________________________________ ______________________________________________ _______________________________________________ ___________________________________________________ ____________________________________________________ ______________________________________________ ____________________________________________________ _______________________________________________________ _______________________________________________ ________________________________________________ __________________________________________ ________________________________________________ ___________________________________________________ _______________________________________________ ________________________________________________ __________________________________________ ________________________________________________ ___________________________________________________ ______________________________________________________________ _______________________________________________________________ _________________________________________________________ _______________________________________________________________ _______________________________________________________________ _____________________________________ ______________________________________ 1.2194e+09 69 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.7084 -1.4547 -13.441 0 37.964 10.221 -5.5171 0 0.85306 99335 73.07 0 -0.042301 51.105 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.545 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.545 0 8 1 7 43.668 0 1.2194e+09 70 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.8218 -1.9142 -13.634 0 43.742 15.6 -9.3468 0 0.53474 99334 73.07 0 -0.038273 51.155 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.518 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.518 0 8 1 7 43.571 0 1.2194e+09 71 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.6447 -1.6411 -13.043 0 51.024 20.527 -13.336 0 0.34087 99337 73.08 0 -0.037205 51.105 0 0.06 8 0.56264 0 17.12 0 496.8 0 12758 0 97 0 76.576 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.576 0 8 1 7 43.571 0 1.2194e+09 72 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.3031 -1.0752 -12.402 0 55.354 24.522 -15.761 0 0.42052 99344 73.07 0 -0.041396 51.135 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.561 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.561 0 8 1 7 43.538 0 1.2194e+09 73 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.243 -0.84437 -11.953 0 55.976 27.983 -16.266 0 0.43076 99335 73.08 0 -0.052931 51.095 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.591 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.591 0 8 1 7 43.538 0 1.2194e+09 74 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.6089 -1.1942 -11.71 0 56.205 31.319 -15.932 0 0.50664 99333 73.07 0 -0.058207 51.145 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.6 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.6 0 8 1 7 43.635 0 1.2194e+09 75 1.2194e+09 2212 2.166e+08 0 11.406 51.856 -0.1502 13.929 0 178.36 225.34 0 0.038055 -0.054199 -0.14317 0.27874 0.14276 1.1365 0.00037665 0.00022326 0.0017627 9 20 0 -1.9675 -1.9026 -11.638 0 57.681 35.055 -16.399 0 0.35796 99338 73.07 0 -0.055192 51.135 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.573 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.573 0 8 1 7 43.635 0 1.2194e+09 76 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.9584 -2.4827 -11.274 0 59.671 38.962 -18.384 0 0.28719 99336 73.08 0 -0.057634 51.09 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.518 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.518 0 8 1 7 43.571 0 1.2195e+09 77 1.2195e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.7045 -2.6583 -10.202 0 61.628 41.506 -21.067 0 0.66216 99344 73.07 0 -0.065034 51.095 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.564 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.564 0 8 1 7 43.571 0
TimeStamp = table(datetime(T1{:,1}, 'ConvertFrom','posixtime'), 'VariableNames',{'TimeStamp'});
T1 = [TimeStamp T1]
T1 = 9×76 table
TimeStamp timestamp sequenceNumber.value sensors.loopTimestampUs.value sensors.gnss[0].time.wnc.value sensors.gnss[0].time.tow.value sensors.gnss[0].time.status sensors.gnss[0].position.lon.value sensors.gnss[0].position.lat.value sensors.gnss[0].equirectangularPosition.northing.value sensors.gnss[0].equirectangularPosition.easting.value sensors.gnss[0].equirectangularPosition.status sensors.gnss[0].altitude.amsl.value sensors.gnss[0].altitude.wgs84.value sensors.gnss[0].altitude.status sensors.gnss[0].velocity.east.value sensors.gnss[0].velocity.north.value sensors.gnss[0].velocity.down.value sensors.gnss[0].covariances.position.lat.value sensors.gnss[0].covariances.position.lon.value sensors.gnss[0].covariances.position.altWgs84.value sensors.gnss[0].covariances.velocity.north.value sensors.gnss[0].covariances.velocity.east.value sensors.gnss[0].covariances.velocity.down.value sensors.gnss[0].solutionType.enum sensors.gnss[0].satelliteCount.value sensors.gnss[0].satelliteCount.status sensors.imu.acc.x.value sensors.imu.acc.y.value sensors.imu.acc.z.value sensors.imu.acc.status sensors.imu.gyr.x.value sensors.imu.gyr.y.value sensors.imu.gyr.z.value sensors.imu.gyr.status sensors.imu.gyroMaxNormDiff.value sensors.baro.pressure.value sensors.baro.temperature.value sensors.baro.status sensors.pitot.pressure.value sensors.pitot.temperature.value sensors.pitot.status sensors.lidar.value sensors.lidar.status sensors.battery[0].current.value sensors.battery[0].current.status sensors.battery[0].voltage.value sensors.battery[0].voltage.status sensors.battery[0].integratedCharge.value sensors.battery[0].integratedCharge.status sensors.battery[0].remainingCharge.value sensors.battery[0].remainingCharge.status sensors.battery[0].relativeStateOfCharge.value sensors.battery[0].relativeStateOfCharge.status sensors.temperatures.maxAutopilot.temperature.value sensors.temperatures.maxAutopilot.temperature.status sensors.temperatures.maxAutopilot.margin.value sensors.temperatures.maxAutopilot.source.device.enum sensors.temperatures.maxAutopilot.source.component.enum sensors.temperatures.maxStack.temperature.value sensors.temperatures.maxStack.temperature.status sensors.temperatures.maxStack.margin.value sensors.temperatures.maxStack.source.device.enum sensors.temperatures.maxStack.source.component.enum sensors.temperatures.maxDrone.temperature.value sensors.temperatures.maxDrone.temperature.status sensors.temperatures.maxDrone.margin.value sensors.temperatures.maxDrone.source.device.enum sensors.temperatures.maxDrone.source.component.enum sensors.temperatures.mostCriticalTemperature.temperature.value sensors.temperatures.mostCriticalTemperature.temperature.status sensors.temperatures.mostCriticalTemperature.margin.value sensors.temperatures.mostCriticalTemperature.source.device.enum sensors.temperatures.mostCriticalTemperature.source.component.e sensors.temperatures.outsideAir.value sensors.temperatures.outsideAir.status ____________________ __________ ____________________ _____________________________ ______________________________ ______________________________ ___________________________ __________________________________ __________________________________ ______________________________________________________ _____________________________________________________ ______________________________________________ ___________________________________ ____________________________________ _______________________________ ___________________________________ ____________________________________ ___________________________________ ______________________________________________ ______________________________________________ ___________________________________________________ ________________________________________________ _______________________________________________ _______________________________________________ _________________________________ ____________________________________ _____________________________________ _______________________ _______________________ _______________________ ______________________ _______________________ _______________________ _______________________ ______________________ _________________________________ ___________________________ ______________________________ ___________________ ____________________________ _______________________________ ____________________ ___________________ ____________________ ________________________________ _________________________________ ________________________________ _________________________________ _________________________________________ __________________________________________ ________________________________________ _________________________________________ ______________________________________________ _______________________________________________ ___________________________________________________ ____________________________________________________ ______________________________________________ ____________________________________________________ _______________________________________________________ _______________________________________________ ________________________________________________ __________________________________________ ________________________________________________ ___________________________________________________ _______________________________________________ ________________________________________________ __________________________________________ ________________________________________________ ___________________________________________________ ______________________________________________________________ _______________________________________________________________ _________________________________________________________ _______________________________________________________________ _______________________________________________________________ _____________________________________ ______________________________________ 22-Aug-2008 03:35:17 1.2194e+09 69 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.7084 -1.4547 -13.441 0 37.964 10.221 -5.5171 0 0.85306 99335 73.07 0 -0.042301 51.105 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.545 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.545 0 8 1 7 43.668 0 22-Aug-2008 06:24:05 1.2194e+09 70 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.8218 -1.9142 -13.634 0 43.742 15.6 -9.3468 0 0.53474 99334 73.07 0 -0.038273 51.155 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.518 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.518 0 8 1 7 43.571 0 22-Aug-2008 09:17:48 1.2194e+09 71 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.6447 -1.6411 -13.043 0 51.024 20.527 -13.336 0 0.34087 99337 73.08 0 -0.037205 51.105 0 0.06 8 0.56264 0 17.12 0 496.8 0 12758 0 97 0 76.576 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.576 0 8 1 7 43.571 0 22-Aug-2008 12:49:17 1.2194e+09 72 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.3031 -1.0752 -12.402 0 55.354 24.522 -15.761 0 0.42052 99344 73.07 0 -0.041396 51.135 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.561 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.561 0 8 1 7 43.538 0 22-Aug-2008 15:00:57 1.2194e+09 73 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.243 -0.84437 -11.953 0 55.976 27.983 -16.266 0 0.43076 99335 73.08 0 -0.052931 51.095 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.591 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.591 0 8 1 7 43.538 0 22-Aug-2008 17:47:56 1.2194e+09 74 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.6089 -1.1942 -11.71 0 56.205 31.319 -15.932 0 0.50664 99333 73.07 0 -0.058207 51.145 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.6 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.6 0 8 1 7 43.635 0 22-Aug-2008 21:45:40 1.2194e+09 75 1.2194e+09 2212 2.166e+08 0 11.406 51.856 -0.1502 13.929 0 178.36 225.34 0 0.038055 -0.054199 -0.14317 0.27874 0.14276 1.1365 0.00037665 0.00022326 0.0017627 9 20 0 -1.9675 -1.9026 -11.638 0 57.681 35.055 -16.399 0 0.35796 99338 73.07 0 -0.055192 51.135 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.573 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.573 0 8 1 7 43.635 0 22-Aug-2008 23:06:35 1.2194e+09 76 1.2194e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.9584 -2.4827 -11.274 0 59.671 38.962 -18.384 0 0.28719 99336 73.08 0 -0.057634 51.09 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.518 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.518 0 8 1 7 43.571 0 23-Aug-2008 01:54:16 1.2195e+09 77 1.2195e+09 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN -1.7045 -2.6583 -10.202 0 61.628 41.506 -21.067 0 0.66216 99344 73.07 0 -0.065034 51.095 0 0.06 8 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 76.564 0 8 1 7 88.178 0 17 26 2 88.178 0 17 26 2 76.564 0 8 1 7 43.571 0
This at least makes sense. If the ‘timestamp’ variable is in a different format, we need to know the conversion method.
This can then be converted to a timetable if desired. Use the removevars function to remove the original ‘timestamp’ variable if you want to.
.
  2 Comments
Star Strider
Star Strider on 29 Jun 2022
What does ‘not working’ mean?
It works when I run it, producing what appears to me to be an appropriate result. As I mentioned, if the conversion routine is something different that POSIX (I experimented with Excel dates and that failed), we need to know what the conversion is.
For example, if you know the correct dates and times for at least two ‘timestamp’ values (preferably not immediately adjacent to each other), and assuming linearity, it would be possible to perform a linear regression to convert them into something meaningful, for example to MATLAB datenum values that could then be converted to datetime arrays. .
As I mentioned, 37 hour days and 61 minute hours make no sense, at least in terrestrial time. (Maybe on other planets, time is different.)

Sign in to comment.

Devang Tavkari
Devang Tavkari on 2 Jul 2022
Hello Vimal,
The following code should split the data and store the data in a .csv file.
clear
clc
close all
%Asking user to add path
path = 'Enter file path here';
d = dir(fullfile(path,'*.csv'));
%Reading file and extracting data
num = xlsread(fullfile(path,d.name));
col3 = num(:,3);
for i = 1:1:numel(col3)
day{i} = num2str(col3(i));
days{i} = str2num(day{i}(1:4));
hrs{i} = str2num(day{i}(5:6));
min{i} = str2num(day{i}(7:8));
sec{i} = str2num(day{i}(9:end));
end
%Storing data in .csv file
data = {days,hrs,min,sec};
data = cell2table(transpose(data));
writetable(data,'Data.csv');
Thanks.

Categories

Find more on Spreadsheets in Help Center and File Exchange

Tags

Products


Release

R2022a

Community Treasure Hunt

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

Start Hunting!