find the closest datetime

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

Answer 1

[Stephen23]

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.:

https://www.mathworks.com/help/matlab/ref/datetime.html#d124e322582

2) Pay more attention to the differences between DATETIME and DURATION objects:

https://www.mathworks.com/help/matlab/date-and-time-operations.html

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.

[Luis Eduardo Cofré Lizama]

Many thanks Stephen23, I just realised all datetime info is there when changing the format. I also found that the ReferenceDatetime is "1899-12-31 09:39:16.0000" !!. So yes,when substracting my column with dates, which are from today, e.g.,2023-03-02 09:39:03.0083, it does not make any sense. UNfortunately, and maybe you can help me too, I've been unable to change the ""1899-12-31" to today's date. I tried using:

datetime(ReferenceDatetime, 'ConvertFrom','excel', 'Format','yyyy-MM-dd HH:mm:ss.SSSS');

However, I get a series of errors. Any suggestion?

In advance, many thanks

Cheers

Eduardo

[Stephen23]

"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]

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.

Answer 2

[DUY Nguyen]

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

Answer 3

[Steven Lord]

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.