datetime comparison fails but datenum works

clear all
datetime.setDefaultFormats('default','yyyy-MM-dd HH:mm:ss.SSS')

load sample_t t

t

t = 4×5 table
     Name            StartDate                   EndDate            Duration     Type  
    ______    _______________________    _______________________    ________    _______

    "Opr1"    2023-01-01 18:38:00.000    2023-01-01 18:38:00.000    00:00:00    Process
    "Opr2"    2023-01-01 18:38:00.000    2023-01-01 18:38:20.433    00:00:20    Process
    "Opr3"    2023-01-01 18:38:20.433    2023-01-01 18:43:20.433    00:05:00    Process
    "Opr4"    2023-01-01 18:43:20.433    2023-01-01 19:03:20.433    00:20:00    Process

t.EndDate(2)

ans = datetime
   2023-01-01 18:38:20.433

t.StartDate(3)

ans = datetime
   2023-01-01 18:38:20.433

t.EndDate(2)<=t.StartDate(3) % false

ans = logical
   0

t.EndDate(3)<=t.StartDate(4) % true

ans = logical
   1

datenum(t.EndDate(2)) <= datenum(t.StartDate(3)) % true

ans = logical
   1

Answer 1

[Stephen23]

"datetime comparison fails but datenum works"

Actually DATENUM fails but DATETIME works.

The dates you uploaded have a higher precision than the micro-seconds that you show. Lets have a look:

S = load('sample_t.mat');
T = S.t

T = 4×5 table
     Name          StartDate                EndDate           Duration     Type  
    ______    ____________________    ____________________    ________    _______

    "Opr1"    01-Jan-2023 18:38:00    01-Jan-2023 18:38:00    00:00:00    Process
    "Opr2"    01-Jan-2023 18:38:00    01-Jan-2023 18:38:20    00:00:20    Process
    "Opr3"    01-Jan-2023 18:38:20    01-Jan-2023 18:43:20    00:05:00    Process
    "Opr4"    01-Jan-2023 18:43:20    01-Jan-2023 19:03:20    00:20:00    Process

fprintf('%64.42f\n',T.StartDate.Second)

                    0.000000000000000000000000000000000000000000
                    0.000000000000000000000000000000000000000000
                   20.433599999999977114839566638693213462829590
                   20.433600000000094354390967055223882198333740

fprintf('%64.42f\n',T.EndDate.Second)

                    0.000000000000000000000000000000000000000000
                   20.433599999999998431121639441698789596557617
                   20.433599999999977114839566638693213462829590
                   20.433600000000094354390967055223882198333740

Apparently you expect EndDate(2) <= StartDate(3), but it is clear that for the values you uploaded this is not true: from the 14th(?) fractional digit of the seconds unit the EndDate(2) is larger than the StartDate(3). This is around the binary floating point limit for the double class, which implies that this is just some accumulated floating point error.

DATENUM does not really work better, it just ignores the high precision of the DATETIME data by smudging it into one lower-precision scalar double. If the data are different (like yours), then the logical operation should reflect this, not hide it. So DATETIME actually works, just as it should.

You could include a tolerance in the comparison, e.g.:

tol = seconds(0.0005);
(T.StartDate(1:3)-T.EndDate(2:4)) < tol

ans = 3×1 logical array
   1
   1
   1

[Peter Perkins]

Right!

Not clear where these timestamps came from, but if they were originaly datenums, or excel serial date numbers. Same is true for some other numeric representations, so you need to be careful if you expect to work with timestamps with sub microsec precision (sub-muillisec for datenums, really).

Hard to give advice without knowing where your timestamps came from.