I have a time series data recorded in different time zones (sample figure attached). Please note that the time information is in UTC. I need to convert them to local time (AM/PM) automatically for a large dataset. I have attached sample data and any help in this regard is highly appreciated.
https://www.dropbox.com/s/i2eeja8a7em87v9/Sample.csv?dl=0
You can use the TimeZone property for MATLAB's datetime data type to do the conversion for you. The only complication is then getting MATLAB to "forget" the timezone info so that you can just have a simple dataset with your desired datetimes. I've achieved this going via datenum below:
Note that if you want to retain the timezone information then you can simplify this by not doing the extra conversion steps.
Note also you would replace my dummy data at the top with readtable or similar to bring in the data, but I've assumed that the source data is entirely cells of chars, i.e. you may need to be careful of readtable "cleverly" detecting a datetime column, this may mean you have to handle it differently in the loop or provide additional arguments to readtable to prevent this automation.
data = cell2table( { ...
'4/02/2021 16:25', 'Pacific/Auckland'
'4/02/2021 16:05', 'Pacific/Auckland'
'5/02/2021 9:17', 'America/Chicago'
'4/02/2021 22:27', 'Asia/Singapore'
}, 'VariableNames', {'timeStrUtc','timezone'} );
data.timeUtc(:) = NaT;
data.timeLocal(:) = NaT;
for ii = 1:height(data)
% Convert input string to UTC-timezoned datetime
zonedDateUtc = datetime( data.timeStrUtc{ii}, ...
'InputFormat', 'dd/MM/yyyy HH:mm', ...
'TimeZone', 'UTC' );
% Convert to a local datetime
zonedDateLocal = datetime( zonedDateUtc, 'TimeZone', data.timezone{ii} );
% Get rid of MATLAB's associated timezones by converting to datenum
zonedDateUtc = datenum( zonedDateUtc );
zonedDateLocal = datenum( zonedDateLocal );
% Assign output to table, without timezones so all utc/local
data.timeUtc(ii) = datetime( zonedDateUtc, 'ConvertFrom', 'datenum' );
data.timeLocal(ii) = datetime( zonedDateLocal, 'ConvertFrom', 'datenum' );
end
% Format table columns
data.timeUtc.Format = 'd/M/yyyy h:mm a';
data.timeLocal.Format = 'd/M/yyyy h:mm a';
You can do this using a combination of readtable and the datetime constructor. First use readtable to read the data as text (because we want more control over the datetime construction):
t = readtable('Sample.csv', 'DateTimeType', 'text', 'TextType', 'string');
This results in a Nx2 table with string data. To convert each row to a datetime, we need a form of the datetime constructor like this:
datetime("4/02/2021 16:25", "InputFormat", "MM/dd/uuuu HH:mm", "TimeZone", "UTC")
Please check the "InputFormat" used there, because it isn't entirely obvious whether you want "MM/dd/uuuu" or "dd/MM/uuuu".
We can add another variable to the table t like this:
% Make a UTC datetime
t.endTimeUtc = datetime(t.endTimeUtcSecs, 'InputFormat', 'MM/dd/uuuu HH:mm', 'TimeZone', 'UTC');
Next, we need to convert each datetime from being in UTC to being in the specified TimeZone. Because a datetime array has a single TimeZone for the entire array, we must create a cell array of results. So, here's a function that takes a datetime and a time zone, and returns a new datetime wrapped in a cell array.
fun = @(ts, z) {datetime(ts, 'TimeZone', z, 'Format', 'MM/dd/uuuu HH:mm Z')};
(That function also sets up the display format of the datetime array to include time zone information)
We want to apply that function to the new variable in the table, and the time zone variable. Like this:
zonedEndTime = rowfun(fun, t(:, {'endTimeUtc', 'recordingTimezone'}));
(Note that I got a few warnings because some of your TimeZone specifications are apparently ambiguous)
This gives me the following result:
>> head(zonedEndTime)
ans =
8×1 table
Var1
__________________________
{[04/03/2021 05:25 +1300]}
{[04/03/2021 05:05 +1300]}
{[05/02/2021 04:17 -0500]}
{[04/03/2021 06:27 +0800]}
{[05/02/2021 01:51 -0500]}
{[05/02/2021 07:42 +0200]}
{[05/02/2021 08:17 +0800]}
{[05/02/2021 01:22 -0400]}
