find the closest datetime
159 views (last 30 days)
Show older comments
Luis Eduardo Cofré Lizama
on 2 Mar 2023
Commented: Peter Perkins
on 13 Mar 2023
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
0 Comments
Accepted Answer
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)
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)
Now lets subtract them.
Subtracting two DATETIMEs returns a DURATION object showing the hours, minutes and seconds:
dt1-dt0
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)
d0 = timeofday(dt0)
d1-d0
And there are your five seconds.
3 Comments
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
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)
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)
is weird or wrong: what's the 0th month of 1900? Dec 1899. What's the 0th day of Dec 1899? 30 Nov 1899.
More Answers (2)
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')
A = rightNow + hours(randi([-48 48], n, 1)) ...
+ minutes(randi([-60 60], n, 1)) ...
+ seconds(randi([-60 60], n, 1))
What's closest to right now?
interp1(A, A, rightNow, 'nearest')
Check:
results = table(A, abs(A-rightNow), VariableNames=["Date and Time", "Absolute Difference"])
Visual inspection of the Absolute Difference variable agrees with the result of interp1.
0 Comments
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);
0 Comments
See Also
Categories
Find more on Dates and Time 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!