Convert date data in datetime

Dear Matlab Community,

I have uplaoded the small subset of the data which I am using;

I am converting it in dateime using:

date = datetime(string(data.Date),'InputFormat','yyyyMMddHH' ) ;

I have also attached the output file.

I will also like to add that the original file is in csv format, I am using 'readtable' to read and process it in Matlab.

The problem is date corresponding to 24hrs is not coverted in datetime.

Any help will be highly appreciated.

Many thanks.

[the cyclist]

Rather than pasting the data (which is potentially ambiguous as to how it is stored), can you upload the data in a MAT file? You can use the paper clip icon in the INSERT section of the toolbar.

[the cyclist]

Here is a simpler, self-contained example that illustrates @PS's question. The last element does not convert (because the hour is listed as '24'):

date_data = ["2016010101","2016010113","2016010124"];

datetime(date_data,'InputFormat','yyyyMMddHH')

ans = 1×3 datetime array
   01-Jan-2016 01:00:00   01-Jan-2016 13:00:00   NaT                 

[PS]

I have edited the main question itself for better clarity,

thanks for the inputs

[Stephen23]

"I will also like to add that the original file is in csv format, I am using 'readtable' to read and process it in Matlab."

Please upload a sample CSV file, to see if the importing can be improved.

[PS]

@Stephen23, I will not be upload the file due to some reasons. But importing has been done correctly and the sample I have uploaded are the exact values given in the csv file.

Thanks

Answer 1

[Dyuman Joshi]

"The problem is date corresponding to 24hrs is not coverted in datetime."

Yes, because in 24 hour format, the hours are counted from 0 to 23.

A work around which works for the data in the given format -

load('data.mat')
format longg
date = datetime(string(data.Date - 1),'InputFormat','yyyyMMddHH') 

date = 49×1 datetime array
   01-Jan-2016 00:00:00
   01-Jan-2016 01:00:00
   01-Jan-2016 02:00:00
   01-Jan-2016 03:00:00
   01-Jan-2016 04:00:00
   01-Jan-2016 05:00:00
   01-Jan-2016 06:00:00
   01-Jan-2016 07:00:00
   01-Jan-2016 08:00:00
   01-Jan-2016 09:00:00
   01-Jan-2016 10:00:00
   01-Jan-2016 11:00:00
   01-Jan-2016 12:00:00
   01-Jan-2016 13:00:00
   01-Jan-2016 14:00:00
   01-Jan-2016 15:00:00
   01-Jan-2016 16:00:00
   01-Jan-2016 17:00:00
   01-Jan-2016 18:00:00
   01-Jan-2016 19:00:00
   01-Jan-2016 20:00:00
   01-Jan-2016 21:00:00
   01-Jan-2016 22:00:00
   01-Jan-2016 23:00:00
   02-Jan-2016 00:00:00
   02-Jan-2016 01:00:00
   02-Jan-2016 02:00:00
   02-Jan-2016 03:00:00
   02-Jan-2016 04:00:00
   02-Jan-2016 05:00:00

[PS]

Thanks for the neat solution, but using this method will change the order of corresponding Value associated with that Hour, for e.g. now Value 53 is corresponding to 00/24 hrs rather than 01hrs and so on.

[Dyuman Joshi]

But there are only 49 elements in the array above.

Also, (I guess) you want to display the hours in 1-24 range, which is not possible in MATLAB datetime arrays.

[Fangjun Jiang]

If the data.Date in data.mat is the best format that you prefer, you could apply the same "duration" idea.

date = datetime(string(data.Date - 1),'InputFormat','yyyyMMddHH')+hours(1)

[PS]

@Dyuman Joshi..this dataset is just a tiny sample from the large number of files I have. I have been given data in the format of 1-24hrs range, so I want to sick to that. But thanks for your solution

@Fangjun Jiang.. thanks for the solution, I think I can work with that.

Answer 2

[Fangjun Jiang]

You could treat the last two digits as duration. Code below is not optimized but gives the correct result.

load Data.mat;
date_data=char(string(data.Date));
a=char(date_data);
b=a(:,1:end-2);
c=a(:,end-1:end);
d=datetime(b,'InputFormat','yyyyMMdd')+hours(str2num(c))

   02-Jan-2016 20:00:00
   02-Jan-2016 21:00:00
   02-Jan-2016 22:00:00
   02-Jan-2016 23:00:00
   03-Jan-2016 00:00:00
   03-Jan-2016 01:00:00

[PS]

@Fangjun Jiang Thanks for this solution too, I have also found a workaround using insertAfter and replace. I will probably use first solution provided by you.

Answer 3

[Seth Furman]

Another solution is the numeric datetime constructor syntax (i.e. t = datetime(Y,M,D,H,MI,S)), which allows values outside the conventional range:

"If an element of the Y, M, D, H, MI, or S inputs falls outside the conventional range, then datetime adjusts both that element and the same element of the previous input."

Since your timestamps always have the same field widths, you can 1) extract each field, 2) convert to numeric, and 3) call the datetime constructor.

load data.mat

% Convert Date to string
data = convertvars(data, "Date", "string");

% Extract date and time fields
data = addvars(data, extractBetween(data.Date, 1, 4),  Before=1, NewVariableNames="Year");
data = addvars(data, extractBetween(data.Date, 5, 6),  After=1, NewVariableNames="Month");
data = addvars(data, extractBetween(data.Date, 7, 8),  After=2, NewVariableNames="Day");
data = addvars(data, extractBetween(data.Date, 9, 10),  After=3, NewVariableNames="Hour");

% Convert fields to numeric
data = convertvars(data, ["Year","Month","Day","Hour"], "double");

% Construct datetime
data = addvars(data, datetime(data.Year, data.Month, data.Day, data.Hour, 0, 0), Before=1, NewVariableNames="Datetime");

tail(data)

          Datetime          Year    Month    Day    Hour        Date        Value
    ____________________    ____    _____    ___    ____    ____________    _____

    02-Jan-2016 18:00:00    2016      1       2      18     "2016010218"     31  
    02-Jan-2016 19:00:00    2016      1       2      19     "2016010219"     24  
    02-Jan-2016 20:00:00    2016      1       2      20     "2016010220"     29  
    02-Jan-2016 21:00:00    2016      1       2      21     "2016010221"     29  
    02-Jan-2016 22:00:00    2016      1       2      22     "2016010222"     25  
    02-Jan-2016 23:00:00    2016      1       2      23     "2016010223"     30  
    03-Jan-2016 00:00:00    2016      1       2      24     "2016010224"     26  
    03-Jan-2016 01:00:00    2016      1       3       1     "2016010301"     37