Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Database Timezone GMT (DBTIMZONE = +00:00)
Client Timezone BST (SESSIONTIMEZONE = +01:00)
When querying the TIMESTAMP column in DBA_AUDIT_TRAIL with a WHERE clause on TIMESTAMP, the returned result does not match the predicate. For example, if I query for an audit record at 18:05, it returns a record from 19:05.
select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') as audit_ts from dba_audit_trail where timestamp = to_date('2018-05-23 18:05:00','yyyy-mm-dd hh24:mi:ss');
AUDIT_TS
---------------------------------------------------------------------------
2018-05-23 19:05:00
The column is a DATE type column in the view DBA_AUDIT_TRAIL, and appears to to take account of the local timezone
cast ( /* TIMESTAMP */
(from_tz(ntimestamp#,'00:00') at local) as date),
but from a SQL point of view, I can't understand how, even with a view, the predicate "WHERE col=a" can return a result where "col<>a".