CODEWITHSUNDEEP
sqloracle-databasetimestampsql-update
s.r
s.r

Reputation: 2577

Update Oracle timestamp to current date

I have a TIMESTAMP(6) field in Oracle db. Value of this field is in format

DD/MM/YYYY HH:MM:SS.000000000 PM

How to update this value to the current timestamp?

[a link to a similar question:] update date value in oracle

I followed this link, but following query is taking very long time to execute.

update table_name set start_time = to_char(to_date(start_time, 'yyyy/mm/dd-hh:mi:ss:ff3'), '2012/10/10-19:30:00:00') where column='Q'

Upvotes: 9

Views: 89252

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

Why you don't just

update table_name 
set start_date = systimestamp 
where column='Q'

If you suspect there are locks on the table, there are some tables to check: dba_locks, v$session, v$session_blockers etc. These are useful when a user blocked something with an accidental update without a commit or rollback, but you should be able to see if can exists blocking locks from the architecture of your application. You should just simulate on paper all the scenarios.

Upvotes: 3

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

A timestamp is a point in time, it has no format. To update such a field to the current timestamp, use SYSTIMESTAMP or CURRENT_TIMESTAMP (respectively the date/time of the server and the date/time of the session):

UPDATE your_table 
   SET your_column = systimestamp
 WHERE ...

If the query takes an abnormal amount of time (much longer than a comparable SELECT with the same WHERE clause), the mostly likely causes are:

  1. The rows that your are updating are locked by another session (doing a SELECT FOR UPDATE NOWAIT on these rows will make sure that you have the lock).
  2. You have triggers that perform additional work,
  3. You're updating a column referenced by a non-indexed foreign key.

Upvotes: 23

Related Questions