Clear Filters
Clear Filters

Matlab dates and time not aligning after combining

3 views (last 30 days)
My end goal is to plot discharge over time with a large dataset. The sample interval for discharge is in minutes and changes from every 5 hours to every 5 minutes a couple years into data collection. I have created a table containing date and time combined and discharge. For some reason when I do this minutes will return to 0 randomly. I am not sure how to fix this so my plot will look normal. The file is too large I cannot import it so I attcahed a screenshot of the original data table and of T. Thanks in advance!!
dataTable = readtable(csvFilePath);
Date=datetime(dataTable{:,16})+minutes(dataTable{:,9});
Discharge=dataTable(:,10);
Q =[Discharge]
Hour=dataTable(:,9)
T_cell = table2array(Q);
Hour_cell=table2array(Hour);
times = datenum(Hour_cell);
t = table(Date,times);
dates = datetime(t.Date,'Format','MM/dd/yyyy HH:mm:SS');
times = datetime(t.times,'ConvertFrom','datenum','Format','MM/dd/yyyy HH:mm:SS');
fullt = dates+timeofday(times);
t.DatesNTimes = fullt;
T=table(fullt, T_cell);
  3 Comments
Callie
Callie on 14 Nov 2023
Sorry I am new to Matlab. Instead of using the times part I stuck with Hour_cell and still got the same result.
Les Beckham
Les Beckham on 14 Nov 2023
Can you post a subset of the "too large" dataset that illustrates the issue?

Sign in to comment.

Accepted Answer

Cris LaPierre
Cris LaPierre on 15 Nov 2023
Edited: Cris LaPierre on 15 Nov 2023
One issue is that your RecHour variable is not minutes. It is time of day in military time format (no colon. Should be 4 numbers, but that's likely lost due to converstion to numeric data type). The point is it is not appropriate to use minute(dataTable{:,9}). This is why your results in row 6 are now on a different day than the original data indicates.
Also, readtable has already converted date to a datetime. No need to do this a second time.
times is created ultimately from RecHours. There is no date information here, so it doesn't make sense to use datetime to create times. It addes a meaningless date to the times. Why do this? You end up ignoring the date anyway.
Finally, you come full circle and create fullt, which appears to just be a recreation of what your 2nd line of code creates, but with a lot of intrigue and mystery behind it.
I don't see any data in 5 minute increments, so can't observe what may be happening there.
Try this updated code and let us know if the issue persists.
% Load csv file (created using Excel's Import from Image feature)
dataTable = readtable('discharge.csv');
% combine date and time to new table variable
dataTable.RecHour = hours(dataTable.RecHour/100);
dataTable.Date=dataTable.date+dataTable.RecHour
dataTable = 24×17 table
Varl DataCod Recl RecYear RecMonth Recday Watershed DayofYear RecHour Discharge Shei Corr H eight Qual date Date ____ ___________ ____ _______ ________ ______ __________ _________ _______ _________ ____ _________ _ _______ _______ ___________ ____________________ 1 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 0 hr 0.003 0 {'5...' } 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 00:00:00 2 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 3 hr 0.003 0 {'4..' } 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 03:00:00 3 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 6 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 06:00:00 4 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 9 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 09:00:00 5 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 12 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 12:00:00 6 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 15 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 15:00:00 7 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 18 hr 0.003 0 {'4...' } 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 18:00:00 8 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 21 hr 0.003 0 {'4..' } 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 21:00:00 9 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 0 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 00:00:00 10 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 3 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 03:00:00 11 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 6 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 06:00:00 12 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 9 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 09:00:00 13 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 12 hr 0.003 0 {'4...' } 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 12:00:00 14 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 15 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 15:00:00 15 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 18 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 18:00:00 16 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 21 hr 0.003 0 {'4..' } 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 21:00:00
dataTable.Date.Format = 'MM/dd/yyyy HH:mm:ss';
% Extract the desired variables to a new table
T = dataTable(:,["Date","Discharge"])
T = 24×2 table
Date Discharge ___________________ _________ 01/01/1987 00:00:00 0.003 01/01/1987 03:00:00 0.003 01/01/1987 06:00:00 0.003 01/01/1987 09:00:00 0.003 01/01/1987 12:00:00 0.003 01/01/1987 15:00:00 0.003 01/01/1987 18:00:00 0.003 01/01/1987 21:00:00 0.003 01/02/1987 00:00:00 0.003 01/02/1987 03:00:00 0.003 01/02/1987 06:00:00 0.003 01/02/1987 09:00:00 0.003 01/02/1987 12:00:00 0.003 01/02/1987 15:00:00 0.003 01/02/1987 18:00:00 0.003 01/02/1987 21:00:00 0.003
  7 Comments
Callie
Callie on 15 Nov 2023
Yes that works! Before I did not look at the tail and it had the wrong times. This new code works. Thank you
Peter Perkins
Peter Perkins on 16 Nov 2023
Chris has certainly cracked that nut, but I'm gonna suggest that you use a timetable, not just a table.

Sign in to comment.

More Answers (0)

Categories

Find more on Timetables in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!