Thread: Using a timestamp in a WHERE clause

Using a timestamp in a WHERE clause

From
"Keith Worthington"
Date:
Hi All,

I am given the following input data.

IPADB=# SELECT * FROM data_transfer.tbl_inventory_scanner;
 employee_id | item_id | quantity | scan_date | scan_time
-------------+---------+----------+-----------+-----------
 116         | SAC38   | 55       | 20041220  | 160933
 116         | SEB12   | 555      | 20041220  | 160947
 116         | SEBM106 | 888      | 20041220  | 160953
 116         | B346.0  | 555      | 20041220  | 161003
 116         | B346.5  | 888      | 20041220  | 161011
 616         | 55-52   | 55       | 20041221  | 082221
 616         | CHHHH   | 0        | 20041221  | 082513
 116         | SNAP50  | 2255     | 20040102  | 090529
 116         | RSN2222 | 525      | 20040102  | 090539
 116         | SAC38   | 658      | 20040102  | 090549
 116         | SEBM106 | 12455    | 20040102  | 090602
(11 rows)

I store it in a table that converts the scan_date and scan_time into a timestamp.

CAST( CAST( scan_date || ' ' || scan_time AS text) AS timestamp)

My question is when searching the target table to see if the record already
exists can I reliably match using the timestamp?

WHERE inventory.tbl_scanner.scan_timestamp =
         CAST( CAST( rcrd_scanner.scan_date ||
                     ' ' ||
                     rcrd_scanner.scan_time
                     AS text
                   )
               AS timestamp
             ),
  AND inventory.tbl_scanner.item_id = rcrd_scanner.item_id

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com