Home » SQL & PL/SQL » SQL & PL/SQL » Oracle equivalent of SQL Server timestamp data type
Oracle equivalent of SQL Server timestamp data type [message #185818] Thu, 03 August 2006 14:19 Go to next message
zaslavets
Messages: 5
Registered: August 2006
Junior Member
Hi

I have a SQL server database where columns of timestamp datatype play very important role. Since timestamp is binary version of (global, unique within database) integer growing incrementally, new value is assigned to row every time it is updated/inserted.
Such columns allow to watch for inserts,updates of data incrementally on row level very precisely.

I am working on migration from SQL to Oracle.

Is there anything like timestamp column in oracle? What technique is used in oracle for similar purpose?

Thanks
Denis
Re: Oracle equivalent of SQL Server timestamp data type [message #185833 is a reply to message #185818] Thu, 03 August 2006 16:15 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There is a TIMESTAMP datatype in Oracle.

In order to mark insert or update time, either insert or update this value just like you'd do it with any other value, or write a database trigger which will do it for you (having a trigger, you wouldn't have to keep in mind and include this column in all your insert/update statements).
Re: Oracle equivalent of SQL Server timestamp data type [message #185840 is a reply to message #185833] Thu, 03 August 2006 16:57 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Or if it's just inserts, try the column DEFAULT clause.

An Oracle timestamp is like a DATE but with more precision though. That doesn't sound much like this:
Quote:

timestamp is binary version of (global, unique within database) integer growing incrementally
Re: Oracle equivalent of SQL Server timestamp data type [message #185844 is a reply to message #185818] Thu, 03 August 2006 17:10 Go to previous messageGo to next message
zaslavets
Messages: 5
Registered: August 2006
Junior Member
timestamp in SQL server is like sequence. it is very precise. It is qunique withing database, it is impossible to have 2 rows with the same value. Does timestamp in oracle provide the same feature?

For example if I update 2 rows in table in the same query,

update a
set ts = sysdate
where id in (1,2);

will they always have different timestamp?

Thanks
Denis
Re: Oracle equivalent of SQL Server timestamp data type [message #185845 is a reply to message #185844] Thu, 03 August 2006 17:15 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
SYSDATE returns a DATE value. SYSTIMESTAMP returns a TIMESTAMP value.

Obviously two things that occur at the same time will be assigned the same timestamp. It's less likely to happen with 9 digits after the decimal point, but I wouldn't count on it being impossible.
Re: Oracle equivalent of SQL Server timestamp data type [message #185846 is a reply to message #185845] Thu, 03 August 2006 17:19 Go to previous messageGo to next message
zaslavets
Messages: 5
Registered: August 2006
Junior Member
so it looks like it is easier for my task , just to have 1 global sequence and assign it's nextval to the column I want to watch for every insert/update in all tables I care about.


What do you think?


Thanks
Denis
Re: Oracle equivalent of SQL Server timestamp data type [message #185853 is a reply to message #185845] Thu, 03 August 2006 17:42 Go to previous messageGo to next message
helloanam
Messages: 20
Registered: August 2005
Junior Member
but there is difference in these 9 numbers after decimal point. in oracle every row is having unique timestamp. i think so.
Re: Oracle equivalent of SQL Server timestamp data type [message #185854 is a reply to message #185853] Thu, 03 August 2006 17:52 Go to previous messageGo to next message
zaslavets
Messages: 5
Registered: August 2006
Junior Member
I did testing:
create table t (id number, ts timestamp default systimestamp);

insert into t (id) values (1);
insert into t (id) values (2);
insert into t (id) values (3);
commit;
select * from t;

update t set id = id+10 , ts = systimestamp;
commit;
select * from t;

As you can see, if statements for every row are separate, timestamp is unique, If we do update in 1 query, it is not.
Re: Oracle equivalent of SQL Server timestamp data type [message #185888 is a reply to message #185854] Fri, 04 August 2006 01:10 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
My gut feelimg is that a timestamp is not a good choice for a primary key. Normally a sequence is used.

Another option is SYS_GUID() (note the empty brackets are required).

CREATE TABLE test_guid
( id     RAW(32) DEFAULT SYS_GUID() PRIMARY KEY
, value  VARCHAR2(30) );

INSERT INTO test_guid (value) SELECT table_name FROM user_tables;

SELECT * FROM test_guid;

For more discussion, see feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html and www.jlcomp.demon.co.uk/faq/autonumb.html, where I notice there is a suggestion to use HEXTORAW(SUBSTR(SYS_GUID(),1,12)) as the default value instead of plain SYS_GUID since the last 12 digits never appear to change. (Possibly that is a risky assumption.)

I notice Tom Kyte isn't that keen on this approach, though.
Re: Oracle equivalent of SQL Server timestamp data type [message #185985 is a reply to message #185888] Fri, 04 August 2006 11:57 Go to previous message
zaslavets
Messages: 5
Registered: August 2006
Junior Member
I do not need it as primary key, I need this column as unique incremental column within database as indicator, so I can watch for changes from some starting point.

Previous Topic: Unable to insert into CLOB column
Next Topic: Using Database Links
Goto Forum:
  


Current Time: Thu Dec 08 14:41:30 CST 2016

Total time taken to generate the page: 0.17610 seconds