Ingres timestamp

From PTAGISWiki

When using a Timestamp datatype in a SQL statement it may be necessary to declare the value as a timestamp. For instance, the following code fails to match even when you expect it should:

UPDATE wqb_query_details SET status = 'not completed'
WHERE start_time = '2008-08-21 09:25:50.701'

For some reason, the start_time value is not really seen as a timestamp and doesn't match an exact timestamp record that exists in the database.

If you declare start_time to be a timestamp with the JDBC escape syntax, it also fails:

UPDATE wqb_query_details SET status = 'not completed'
WHERE start_time = ts {'2008-08-21 09:25:50.701'}

It works to be sneaky and turn the timestamp into a char and then use an approximate match to get the exact value:

UPDATE wqb_query_details SET status = 'completed'
WHERE char(start_time) like '2008-08-21 09:25:50.701%'

But that is probably less that maximally efficient.

The syntax that works in this case is as follows:

UPDATE wqb_query_details SET status = 'not completed'
WHERE start_time = timestamp '2008-08-21 09:25:50.701'