OraFAQ Forum: SQL & PL/SQL » Oracle equivalent of SQL Server timestamp data type

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle equivalent of SQL Server timestamp data type

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle equivalent of SQL Server timestamp data type [message #185818]

Thu, 03 August 2006 14:19

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

Report message to a moderator

Re: Oracle equivalent of SQL Server timestamp data type [message #185833 is a reply to message #185818]

Thu, 03 August 2006 16:15

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: Oracle equivalent of SQL Server timestamp data type [message #185840 is a reply to message #185833]

Thu, 03 August 2006 16:57

William Robertson
Messages: 1643
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

Report message to a moderator

Re: Oracle equivalent of SQL Server timestamp data type [message #185844 is a reply to message #185818]

Thu, 03 August 2006 17:10

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

Report message to a moderator

Re: Oracle equivalent of SQL Server timestamp data type [message #185845 is a reply to message #185844]

Thu, 03 August 2006 17:15

William Robertson
Messages: 1643
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.

Report message to a moderator

Re: Oracle equivalent of SQL Server timestamp data type [message #185846 is a reply to message #185845]

Thu, 03 August 2006 17:19

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

Report message to a moderator

Re: Oracle equivalent of SQL Server timestamp data type [message #185853 is a reply to message #185845]

Thu, 03 August 2006 17:42

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.

Report message to a moderator

Re: Oracle equivalent of SQL Server timestamp data type [message #185854 is a reply to message #185853]

Thu, 03 August 2006 17:52

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.

Report message to a moderator

Re: Oracle equivalent of SQL Server timestamp data type [message #185888 is a reply to message #185854]

Fri, 04 August 2006 01:10

William Robertson
Messages: 1643
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.

Report message to a moderator

Re: Oracle equivalent of SQL Server timestamp data type [message #185985 is a reply to message #185888]

Fri, 04 August 2006 11:57

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 08:03:19 CST 2024