Matlab dates and time not aligning after combining

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);

[Walter Roberson]

 times = datenum(Hour_cell);

Why are you doing that? Your Hour_cell is an array of numbers like 900 and 1200. datenum() with numeric inputs expects that the inputs are serial date numbers -- number of full days since 00-Jan-0000 . With single column numeric input, the output would be the same number. What is the point ??

[Callie]

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]

Can you post a subset of the "too large" dataset that illustrates the issue?

Answer 1

[Cris LaPierre]

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  

[Callie]

Thank you so much for helping. Unfortunately now the time is wrong in the table. I provided a screenshot. I was also able to cut the data.

[Cris LaPierre]

Then you have changed something without indicating what. When I run the code I shared on the file you attached, the results look like what I would expect.

% Load csv file (created using Excel's Import from Image feature)
dataTable = readtable('N01BLR copy cut.csv');

% combine date and time to new table variable
dataTable.RecHour = hours(dataTable.RecHour/100);
dataTable.Date=dataTable.date+dataTable.RecHour;
dataTable.Date.Format = 'MM/dd/yyyy HH:mm:ss';

% Extract the desired variables to a new table
T = dataTable(:,["Date","Discharge"])

T = 1259×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  

[Callie]

Sucess! Thank you so much.

[Cris LaPierre]

Actually, it's not working when times switch to 5 minute increments. It also seems to switch back and forth initially between 3 hr and 5 min increments, which complicates the issue. Is the CSV your raw data, or is there a precursor file? I suspect in the original data, RecHour is recorded with 4 digits. When you open that file in Excel, it formats it as a number and removes all leading zeros.

See if you can locate a raw data file that you have not yet opened in Excel. When opening it, you should see a popup like this

Click 'Don't Convert', and then inspect the RecHour column. Are there 4 digits?

[Cris LaPierre]

I think this code will work.

% Load csv file (created using Excel's Import from Image feature)
dataTable = readtable('N01BLR copy cut.csv');

% combine date and time to new table variable
dataTable.RecHour = duration(floor(dataTable.RecHour/100),rem(dataTable.RecHour,100),0,...
    'Format','hh:mm');
dataTable.Date=dataTable.date+dataTable.RecHour;
dataTable.Date.Format = 'MM/dd/yyyy HH:mm:ss'

dataTable = 1259×17 table
    Var1    DataCode     RecType    RecYear    RecMonth    Recday    Watershed    DayofYear    RecHour    Discharge    Sheight    CorrectedSheight    Height    LogFlag    QualFlag       date               Date        
    ____    _________    _______    _______    ________    ______    _________    _________    _______    _________    _______    ________________    ______    _______    ________    ___________    ___________________

      1     {'ASD05'}       1        1987         1          1       {'N01B'}         1         00:00       0.003         0             5.01            0        {'.'}      {'.'}      01-Jan-1987    01/01/1987 00:00:00
      2     {'ASD05'}       1        1987         1          1       {'N01B'}         1         03:00       0.003         0             4.99            0        {'.'}      {'.'}      01-Jan-1987    01/01/1987 03:00:00
      3     {'ASD05'}       1        1987         1          1       {'N01B'}         1         06:00       0.003         0             4.97            0        {'.'}      {'.'}      01-Jan-1987    01/01/1987 06:00:00
      4     {'ASD05'}       1        1987         1          1       {'N01B'}         1         09:00       0.003         0             4.97            0        {'.'}      {'.'}      01-Jan-1987    01/01/1987 09:00:00
      5     {'ASD05'}       1        1987         1          1       {'N01B'}         1         12:00       0.003         0             4.98            0        {'.'}      {'.'}      01-Jan-1987    01/01/1987 12:00:00
      6     {'ASD05'}       1        1987         1          1       {'N01B'}         1         15:00       0.003         0             4.97            0        {'.'}      {'.'}      01-Jan-1987    01/01/1987 15:00:00
      7     {'ASD05'}       1        1987         1          1       {'N01B'}         1         18:00       0.003         0             4.94            0        {'.'}      {'.'}      01-Jan-1987    01/01/1987 18:00:00
      8     {'ASD05'}       1        1987         1          1       {'N01B'}         1         21:00       0.003         0             4.92            0        {'.'}      {'.'}      01-Jan-1987    01/01/1987 21:00:00
      9     {'ASD05'}       1        1987         1          2       {'N01B'}         2         00:00       0.003         0              4.9            0        {'.'}      {'.'}      02-Jan-1987    01/02/1987 00:00:00
     10     {'ASD05'}       1        1987         1          2       {'N01B'}         2         03:00       0.003         0              4.9            0        {'.'}      {'.'}      02-Jan-1987    01/02/1987 03:00:00
     11     {'ASD05'}       1        1987         1          2       {'N01B'}         2         06:00       0.003         0             4.88            0        {'.'}      {'.'}      02-Jan-1987    01/02/1987 06:00:00
     12     {'ASD05'}       1        1987         1          2       {'N01B'}         2         09:00       0.003         0             4.88            0        {'.'}      {'.'}      02-Jan-1987    01/02/1987 09:00:00
     13     {'ASD05'}       1        1987         1          2       {'N01B'}         2         12:00       0.003         0             4.87            0        {'.'}      {'.'}      02-Jan-1987    01/02/1987 12:00:00
     14     {'ASD05'}       1        1987         1          2       {'N01B'}         2         15:00       0.003         0             4.88            0        {'.'}      {'.'}      02-Jan-1987    01/02/1987 15:00:00
     15     {'ASD05'}       1        1987         1          2       {'N01B'}         2         18:00       0.003         0             4.84            0        {'.'}      {'.'}      02-Jan-1987    01/02/1987 18:00:00
     16     {'ASD05'}       1        1987         1          2       {'N01B'}         2         21:00       0.003         0             4.81            0        {'.'}      {'.'}      02-Jan-1987    01/02/1987 21:00:00

% Extract the desired variables to a new table
T = dataTable(:,["Date","Discharge"])

T = 1259×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  

% View last 10 rows (data is in 5 min increments)
tail(T)

           Date            Discharge
    ___________________    _________

    03/23/1987 19:30:00      0.126  
    03/23/1987 19:35:00      0.127  
    03/23/1987 19:40:00      0.129  
    03/23/1987 19:45:00      0.129  
    03/23/1987 19:50:00       0.13  
    03/23/1987 19:55:00      0.129  
    03/23/1987 20:00:00      0.129  
    03/23/1987 20:05:00      0.127  

[Callie]

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]

Chris has certainly cracked that nut, but I'm gonna suggest that you use a timetable, not just a table.