Clear Filters
Clear Filters

find the closest datetime

173 views (last 30 days)
Hi All, I'm struggling with this one that seems not difficult. I have a datetime column with the format HH:mm:ss.SSSS and for which I'm loooking for the closest datapoint to a time in a format HH:mm:ss. I tried a simple substraction of my reference time from all elements in the timedate column to look for the minimum, however, the results are pretty odd. For example, if a substract my referecnce time 09:39:16 from a datetime point, e.g., 09:39:11.3333, the results is 1079663:59:55?. Below is what Im trying to use to get the closest time to a reference one:
a = min(abs(Datetimepoints - ReferenceDatetime));
In concrete I need to find a way to get the index of the closest datetime.
Thanks

Accepted Answer

Stephen23
Stephen23 on 2 Mar 2023
Edited: Stephen23 on 2 Mar 2023
I have a datetime column with the format HH:mm:ss.SSSS and for which I'm loooking for the closest datapoint to a time in a format HH:mm:ss. I tried a simple substraction of my reference time from all elements in the timedate column to look for the minimum, however, the results are pretty odd. For example, if a substract my referecnce time 09:39:16 from a datetime point, e.g., 09:39:11.3333, the results is 1079663:59:55?"
Explanation
The reason is very simple (but so far no one picked up on this): you are subtracting DATETIMEs with very very different dates. Possibly because you thought that by changing the FORMAT it would only compare the time units. But in actual fact, changing the FORMAT makes absolutely no difference to the stored values and makes absolutely no difference to the values that get subtracted: those will always be the complete DATETIME, including all date units. Just because you can't see them does not not mean that they are not there.
This is very easy to demonstrate. With quick a bit of experimenting it seems that you used something a bit like an Excel date as the source, with an epoch around 1900:
dt1 = duration(09,39,16) + datetime(1900,0,0,1079663,59,55)
dt1 = datetime
30-Jan-2023 09:39:11
Now lets create a reference datetime (which also always includes date units, even if you hide them with the FORMAT):
dt0 = datetime(1900,0,0,09,39,16)
dt0 = datetime
30-Nov-1899 09:39:16
Now lets subtract them.
Subtracting two DATETIMEs returns a DURATION object showing the hours, minutes and seconds:
dt1-dt0
ans = duration
1079663:59:55
So far no surprises here: we just subtracted two points in time that are some 120 years apart, so of course there will be many many many hours between them. So far everything is behaving just as expected.
Solutions
1) Pay more attention to the epoch when importing/converting the data to DATETIME. For example, there are options the let you specify the epoch or date type e.g.:
2) Pay more attention to the differences between DATETIME and DURATION objects:
3) Use TIMEOFDAY to remove the date units and get pure DURATION objects:
d1 = timeofday(dt1)
d1 = duration
09:39:11
d0 = timeofday(dt0)
d0 = duration
09:39:16
d1-d0
ans = duration
-00:00:05
And there are your five seconds.
  3 Comments
Stephen23
Stephen23 on 2 Mar 2023
Edited: Stephen23 on 2 Mar 2023
"However, I get a series of errors. Any suggestion?"
If you only need to compare the time of day, why not use TIMEOFDAY as I showed in my answer? Then the date values are irrelevant.
Otherwise please save the variable ReferenceDatetime in a MAT file and upload it here by clicking the paperclip button (or even better, upload the original data file).
Peter Perkins
Peter Perkins on 13 Mar 2023
datetime(ReferenceDatetime, 'ConvertFrom','excel', ...) is expecting ReferenceDatetime to be an Excel day number. These are numeric values, e.g.
exceltime(datetime)
ans = 4.4999e+04
You are probably passing in something else. Stephen23's advice is on-target: it seems like you should be working with durations, not datetimes.
BTW, in case any thinks that
dt0 = datetime(1900,0,0,09,39,16)
dt0 = datetime
30-Nov-1899 09:39:16
is weird or wrong: what's the 0th month of 1900? Dec 1899. What's the 0th day of Dec 1899? 30 Nov 1899.

Sign in to comment.

More Answers (2)

DUY Nguyen
DUY Nguyen on 2 Mar 2023
Hi Luis,
You may check your datetime data again. You can have a look at the code below!
% Create some sample datetime points
Datetimepoints = datetime({'09:39:11.3333', '09:39:12.4444', '09:39:15.5555', '09:39:16.6666' }, 'Format', 'HH:mm:ss.SSSS');
% Define reference datetime
ReferenceDatetime = datetime('09:39:16', 'Format', 'HH:mm:ss');
% Compute the minimum difference between reference datetime and all datetime points
diffs = abs(Datetimepoints - ReferenceDatetime);
[min_diff, idx] = min(diffs);
% Print the closest datetime point and its index
fprintf('Closest datetime point: %s, Index: %d\n', Datetimepoints(idx), idx);
Closest datetime point: 09:39:15.5555, Index: 3

Steven Lord
Steven Lord on 2 Mar 2023
Let's make some sample dates and times within two-ish days of right now.
n = 10;
rightNow = datetime('now')
rightNow = datetime
02-Mar-2023 15:29:07
A = rightNow + hours(randi([-48 48], n, 1)) ...
+ minutes(randi([-60 60], n, 1)) ...
+ seconds(randi([-60 60], n, 1))
A = 10×1 datetime array
01-Mar-2023 07:24:16 01-Mar-2023 18:17:50 01-Mar-2023 23:01:06 03-Mar-2023 10:52:26 04-Mar-2023 08:05:13 03-Mar-2023 10:01:01 04-Mar-2023 14:18:01 03-Mar-2023 05:15:13 02-Mar-2023 09:28:40 01-Mar-2023 20:04:06
What's closest to right now?
interp1(A, A, rightNow, 'nearest')
ans = datetime
02-Mar-2023 09:28:40
Check:
results = table(A, abs(A-rightNow), VariableNames=["Date and Time", "Absolute Difference"])
results = 10×2 table
Date and Time Absolute Difference ____________________ ___________________ 01-Mar-2023 07:24:16 32:04:51 01-Mar-2023 18:17:50 21:11:17 01-Mar-2023 23:01:06 16:28:01 03-Mar-2023 10:52:26 19:23:19 04-Mar-2023 08:05:13 40:36:06 03-Mar-2023 10:01:01 18:31:54 04-Mar-2023 14:18:01 46:48:54 03-Mar-2023 05:15:13 13:46:06 02-Mar-2023 09:28:40 06:00:27 01-Mar-2023 20:04:06 19:25:01
Visual inspection of the Absolute Difference variable agrees with the result of interp1.

Categories

Find more on Dates and Time in Help Center and File Exchange

Products


Release

R2022a

Community Treasure Hunt

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

Start Hunting!