Timestamp Fields Not Sorting Correctly - Table Compares

I am trying to set up a table compare that compares two large tables to make sure they are identical. For the most part, this is working great but we do have some tables that have timestamps (date and time in format: 2021-04-28T03:00 and when I sort columns (to align the tables), these are not sorting correctly. I will get one table that has the timestamp column out of order even though ultimately, the two tables are truly identical. I have checked this by querying the data to verify. Is there something I should be doing to make sure these do indeed sort? They are being identified as Timestamp and convert to Local for both tables. Seems like somewhere in the process, it is forgetting to treat them as a timestamp. General flow for each table is (all DB) Connector > Table Selector > Sorter > Reader > Table Difference Finder.

Hi @JLamka I’m not sure how timestamps are treated by Knime, but this might be a precision issue in the backend.

As a test, try to break the timestamp into individual column, especially the time part using the Extract Date&Time Fields node on both tables, and then sort by date, hour, minute and seconds. This should sort properly.


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.