Home » SQL & PL/SQL » SQL & PL/SQL » Timestamp in sybase! In oracle?
icon10.gif  Timestamp in sybase! In oracle? [message #303831] Mon, 03 March 2008 00:02 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi all,

I am getting records from sybase and inserting the same through oracle. Now the problem is in sybase we are using timestamp, which will have the format like '0x467469786abfc324. How to convert it into the following format in oracle.'MM/DD/YYYY HH:MI:SS'

Thanks in advance.

Fortunethiyagu
Re: Timestamp in sybase! In oracle? [message #303834 is a reply to message #303831] Mon, 03 March 2008 00:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
which will have the format like

And what does this format mean?
What does it represent?

Regards
Michel
Re: Timestamp in sybase! In oracle? [message #303836 is a reply to message #303834] Mon, 03 March 2008 00:17 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Thanks for you speedy reply.

Sorry for not mentioning the data type of oracle table.

It will be in date format.

Sybase -> timestamp (datatype)
Oracle -> date (datatype).

fortunethiyagu
Re: Timestamp in sybase! In oracle? [message #303845 is a reply to message #303836] Mon, 03 March 2008 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant what is the Sybase timestamp format? Number of microseconds since a specific date? Or other thing?
Does it take care of time zone?

Regards
Michel

[Updated on: Mon, 03 March 2008 00:37]

Report message to a moderator

Re: Timestamp in sybase! In oracle? [message #303860 is a reply to message #303845] Mon, 03 March 2008 00:58 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

This is the given data.

0001000013F092F6

Sorry yaar, i dono whether it includes timezone... Sad
Re: Timestamp in sybase! In oracle? [message #303861 is a reply to message #303860] Mon, 03 March 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is the given data. 0001000013F092F6

And what does it represent?

Regards
Michel
Re: Timestamp in sybase! In oracle? [message #303863 is a reply to message #303861] Mon, 03 March 2008 01:04 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

It is in sybase, Timestamp datatype. I want that to be in oracle date format.

Sorry for keep on disturbing...
Re: Timestamp in sybase! In oracle? [message #303871 is a reply to message #303863] Mon, 03 March 2008 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes I understand you have a Sybase timestamp and want to convert it in an Oracle date.
Now if you don't know how this Sybase timespace is coded you can't convert.
So first search what "0001000013F092F6" stands for.
Maybe ask in a Sybase forum.

Regards
Michel
Re: Timestamp in sybase! In oracle? [message #303885 is a reply to message #303871] Mon, 03 March 2008 02:22 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In other words (just in case you didn't understand where the problem is), we don't know how to interpret "0001000013F092F6". It looks like hexadecimal interpretation of a date (at least, you said that it is a timestamp).

So, how to read it? We need to know how to convert this string into a date; something like
0001000013F092F6 -> F6 = day
                    92 = month
                    F0 = year
                    13 = hours
                    etc.
Do you know how to do that? I don't.
Re: Timestamp in sybase! In oracle? [message #409491 is a reply to message #303885] Mon, 22 June 2009 22:31 Go to previous messageGo to next message
llaszews
Messages: 6
Registered: June 2009
Location: New Hampshire
Junior Member

Sybase timestamp has nothing to do with date or time. It is a type of sequence. So mapping to Oracle date or timestamp makes no sense. In Oracle a unique identifier like Oracle's System Change Number (SCN) is a better fit.
Re: Timestamp in sybase! In oracle? [message #409932 is a reply to message #409491] Wed, 24 June 2009 09:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're using a SCN as a uniruq identifier in a table, then your design is wrong.

Use a sequence for that.
Re: Timestamp in sybase! In oracle? [message #409941 is a reply to message #409932] Wed, 24 June 2009 09:31 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
They are looking for the equivalent to as Sybase "Timestamp" here, though.

And for determining something like what-transactions-happened-in-which-order the Oracle SCN would be perfectly suitable.
Re: Timestamp in sybase! In oracle? [message #409951 is a reply to message #409941] Wed, 24 June 2009 09:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How exactly are you going to get the SCN for a specific transation?

Surely a sequence fulfils the 'the transactions happened in this order' requirement quite nicely?
Re: Timestamp in sybase! In oracle? [message #409953 is a reply to message #409951] Wed, 24 June 2009 10:01 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
JRowbottom wrote on Wed, 24 June 2009 07:53
How exactly are you going to get the SCN for a specific transation?



select id, total, ora_rowscn from t1;
ID  TOTAL ORA_ROWSCN
--- ----- ----------
1   101   7868198
2   201   7868198
3   301   7868198

One possible way to obtain SCN though it is not clear to me why original value can not be used.

[Updated on: Wed, 24 June 2009 10:10]

Report message to a moderator

Re: Timestamp in sybase! In oracle? [message #409955 is a reply to message #409953] Wed, 24 June 2009 10:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I thought of that - it neatly fulfills everything except the unique part of the requirement.
Re: Timestamp in sybase! In oracle? [message #409969 is a reply to message #409955] Wed, 24 June 2009 10:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
Surely a sequence fulfils the 'the transactions happened in this order' requirement quite nicely?


I'm not sure. Aren't we always cautioning people, that sequences in Oracle are only guaranteed to be UNIQUE, but not really sequential?

But there never was a "unique" part in the requirement. Wink

They do something on Sybase and use the Sybase "Timestamp" for that.

Which is, as it seems, not really something that defines a "real" time, but something that is something more like the Oracle SCN.

But I'm not a Sybase Guru.

[Updated on: Wed, 24 June 2009 11:04]

Report message to a moderator

Re: Timestamp in sybase! In oracle? [message #409972 is a reply to message #409969] Wed, 24 June 2009 11:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid I'm not convinced.
A sequence won't give you a gap free order, but it will give you a sequential order.
As far as I know, if you tag each insert into the database with a sequence value, then the sequence will record the order in which the inserts were executed.

A good definitive astatement, ripe for demolition.

You're right about the Unique bit - I took that from this post:
Quote:
Sybase timestamp has nothing to do with date or time. It is a type of sequence.
So mapping to Oracle date or timestamp makes no sense. In Oracle a unique identifier like Oracle's System Change Number (SCN) is a better fit.
Re: Timestamp in sybase! In oracle? [message #409983 is a reply to message #409972] Wed, 24 June 2009 12:04 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Two links I often find useful:
http://sybernet.sri.com/oracle/Translator.html
http://www.indiana.edu/~dss/Services/DataWarehouse/Oracle/Sybase/conversion.html

For this question though - plenty of google hits...
http://www.google.com/search?q=sybase+timestamp+oracle
Re: Timestamp in sybase! In oracle? [message #410299 is a reply to message #409953] Fri, 26 June 2009 01:05 Go to previous messageGo to next message
llaszews
Messages: 6
Registered: June 2009
Location: New Hampshire
Junior Member

I don't feel the SCN being unique across rows is a requirement for most use cases that use timestamp in Sybase.
Re: Timestamp in sybase! In oracle? [message #410431 is a reply to message #409969] Fri, 26 June 2009 23:03 Go to previous message
llaszews
Messages: 6
Registered: June 2009
Location: New Hampshire
Junior Member

In the end, you have to know why the application is using timestamp columns. I have been doing Sybase migrations for some time and the number one reason is because Sybase only had page level locking. So, what developers would do is put a timestamp column on the table. When a client application (Visual Basic, Powerbuilder etc) select the row for the table, it would store the timestamp column. When the client application went to write the updated row, it would do a read of the same row and compare the timestamp columns. If the timestamp value had changed, then the application knew that some other client connection had changed the row. Therefore, the client application would use the new row and apply the update (which by virtue of the Sybase timestamp column this column would be automatically updated). I have seen this 'design pattern' over and over in old Sybase applications.

Bottom line: In Oracle, you would not really need this column. However, since the data in the timestamp column in a Sybase database really has no impact on the business functionality of the application, you could simply not migrate the data, put an Oracle timestamp in place of Sybase timestamp on the column, and write a trigger to automatically update the timestamp column for update and insert.
Previous Topic: Date ,NVL Query
Next Topic: query pivot with flag
Goto Forum:
  


Current Time: Wed Dec 07 16:12:32 CST 2016

Total time taken to generate the page: 0.12709 seconds