How you formulate a successful query against the SQLAudit table in part depends on the database you are using as your Hybrid Data Pipeline system database. The following examples show how to query each supported database system. The key difference between each is the function used to convert a timestamp supported by the database into the standard Java UTC epoch (also known as Unix time).

Note: Ordering by ID will not necessarily reflect a chronological order.

Oracle

Oracle does not provide a readily available function to convert human-readable timestamps into the Java UTC epoch. However, the following stored procedure can be used to achieve the conversion.

CREATE OR REPLACE FUNCTION TO_JAVA_UTC (TimestampEnd IN TIMESTAMP WITH TIME ZONE)
  RETURN NUMBER IS
days NUMBER;
hours NUMBER;
minutes NUMBER;
seconds NUMBER;
millis NUMBER;
utc TIMESTAMP WITH TIME ZONE;
diff INTERVAL DAY(9) TO SECOND;
BEGIN
  utc := TO_TIMESTAMP_TZ('01-01-1970 00:00:00+00:00', 'MM-DD-YYYY HH24:MI:SS TZH:TZM');
  diff := TimestampEnd - utc;
  days := EXTRACT (day FROM diff);
  hours := EXTRACT (hour FROM diff);
  minutes := EXTRACT (minute FROM diff);
  seconds := EXTRACT (second FROM diff);
  millis := (((days * 24 + hours) * 60) + minutes) * 60 + seconds;
  RETURN millis;
END;

The TO_JAVA_UTC function can now be used to filter queries, as shown in the following example.

SELECT * FROM SQLAudit WHERE TimestampEnd < TO_JAVA_UTC (TO_TIMESTAMP_TZ('08-26-2020 12:00:00 -04:00', 'MM-DD-YYYY HH24:MI:SS TZH:TZM'))

Microsoft SQL Server

For Microsoft SQL Server, the DATEDIFF function may be used to convert human-readable timestamps into the Java UTC epoch. The CAST function then casts the Java UTC epoch in terms of milliseconds.

SELECT * FROM SQLAudit WHERE TimestampEnd <= DATEDIFF(SECOND, '1970-01-01', '2020-08-26 12:00:00+04:00') * CAST(1000 as bigint)

MySQL

As the following example shows, MySQL provides the UNIX_TIMESTAMP function which converts human-readable timestamps into the Java UTC epoch. The epoch should then be multiplied by 1000 to convert the value from seconds to milliseconds.

SELECT * FROM SQLAudit WHERE TimestampEnd <= UNIX_TIMESTAMP('2020-08-26 12:00:00') * 1000

PostgreSQL

In this example, the PostgreSQL EXTRACT function is used to convert human-readable timestamps into the Java UTC epoch. The epoch should then be multiplied by 1000 to convert the value from seconds to milliseconds.

SELECT * FROM SQLAudit WHERE TimestampEnd <= EXTRACT(epoch from '2020-08-26 12:00:00' at time zone 'edt' at time zone 'utc') * 1000

Internal system database

For the internal system database, the UNIX_MILLIS function can be used to convert human-readable timestamps into the Java UTC epoch in milliseconds, as shown in the following example.

SELECT * FROM SQLAudit WHERE TimestampEnd <= UNIX_MILLIS('1970-01-01', '08-26-2020 12:00:00-04:00')

Filter using the Java UTC epoch

As opposed to including a function in your WHERE clause filter, you can filter using the Java UTC epoch. In this scenario, you would begin by converting a human-readable timestamp into the Java UTC epoch, using an epoch converter tool. You could then create a query like the following:

SELECT * FROM SQLAudit WHERE TimestampEnd <= 1598457600000

In this example, the value 1598457600000 is the Java epoch (in milliseconds) for the timestamp 2020-08-26 12:00:00 Americas/New_York. The result set would provide an audit trail of SQL statements executed on or before this time.

Note: For more information about supported system databases, refer to External system databases in the Deployment Guide.