CODEWITHSUNDEEP
sqloracle-database
needoriginalname
needoriginalname

Reputation: 721

Find entries in sql that were done milliseconds against each other

I need to debug an issue I found with someone else's code, where it reads both the id and the timestamp for a key. But, I noticed that it is not reading the millisecond information. While this is a potential cause, this not confirmed, and I need to find out if this is the cause.

The problem could occur if two entries in the table happened within the same second, 10:20:05.0500 pm and 10:20:05.5000 pm, but not 10:20:05.5000 and 10:20:06.0500.

How do I write such a query to look for it?

I am using Oracle pl/sql.

Upvotes: 1

Views: 364

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112622

To find records with the same date/time up to second precision but with different milliseconds, you can compare different records by joining the table to itself

SELECT A.ts, B.ts
FROM
    Test A
    INNER JOIN Test B
        ON TO_CHAR(A.ts, 'YYYY-MM-DD HH24:MI:SS') = TO_CHAR(B.ts, 'YYYY-MM-DD HH24:MI:SS')
WHERE
    A.ts < B.ts
ORDER BY
    A.ts, B.ts;

TO_CHAR truncates the milliseconds. This is important, because functions that round could yield different seconds. E.g., CAST(ts as timestamp(0)) rounds, which is not what wee need.

The example from the link below has a record with 999 milliseconds to test this.

See example on SQL Fiddle.

Upvotes: 1

alvalongo
alvalongo

Reputation: 571

In Oracle to use fractional seconds a column must be of data type "TIMESTAMP".

Maybe someone else's code is using a variable of type DATE, which is year-month-day hour-minute-second without fractional seconds.

Database SQL Language Reference: Data Types

Can you give the table description and someone else's code?

Upvotes: 1

Related Questions