DSXchange

How can i do to have the timestamp for each row processed in my input stage ?
Functions Date(), time() and timedate() are based on the time where the job begins.
I need to have a unique time, even with milliseconds, for each row processed.
Thanks in advance.

Rob

as opposed to the system variable @Time, time() always takes the current time from the server. According to the online help (version 6):

The internal time is taken from the server, and is returned as the number of seconds since midnight to the nearest thousandth of a second.

After that it should be possible to derive a timestamp with milliseconds in two steps:

ts=time()
oconv(ts, "MTS"):'.':field(ts,'.',2)

I never came across an oconv conversion code, that would return milliseconds[?]

Hope this helps

Stephan

Rob

I tested what I suugested and it didn't work on our system, which runs under HP-UX [B)]. Time() did not deliver fractions of a second. But maybe it's different with other Operating Systems [?].



Stephan

Hi,
the question is why do you need this?

if it is i norder to recognize each row uniqueness you can use any GK (generated key) flavour you like.

if you need the actual fraction of second as a data input to analize it, then you need to explore the DB options to obtain it, I did a litle test with sql server and inserted 537289 rows of constant 1 and getdate() doing a cartezian join between 2 tables in order for it to take more then a few seconds.
the max() and min() values in te table were the same to the fraction:2003-09-04 14:26:00

this means that if your DB doesn't provide a way to do it you can't do it in a simple select.

try it on your DB and get your answer[:)]

IHTH (I Hope This Helps),



Roy R.

Rob1,

What system are you on? If it is a Windows system then (by default - I think) you will receive the milliseconds as part of the time. If you are on a Unix system (various flavors) you will only get the seconds and not milliseconds.

If you are on a Unix system then write a user subroutine and enter the following code -

$OPTIONS TIME.MILLISECOND

PRINT TIME()
PRINT SYSTEM(12)

If your flavor supports milliseconds then the first PRINT will return only seconds -

12345

and the second print should return something like -

12345.123

If you are on Windows then I believe you do not need the $OPTIONS statement and can use TIME() vs SYSTEM(12).

I have used both for keys that are date/time or time only. I can understand why you need this since some DB's recognize milliseconds, although Oracle 8 and below will allow you to insert or update a record with milliseconds, but does not recognize the milliseconds so be careful. (I did a test on Oracle where I inserted 10,000 rows using date and time via DS and only had 89 unique rows!) and this was using milliseconds.

Oracle 9i is the first version of Oracle to support or recognize the milliseconds portion of the time, but I have not yet tried it.

Hope this helps,

Michael Hester

Michael

this doesn't work either. I'm always getting the number of seconds followed by '000'. Seems I'm badly off for HP-UX [:(]!

Stephan

Since you are on Unix (and a flavor that does not support milliseconds) then you can always resort to manually incrementing the seconds to ensure uniqueness. If you can get the modelers to change the design of the table or do as others have suggested in this post and use a unique system generated key then you are left to manually create your own uniqueness in the key.

We had to do this on my current project and it works fine. We artificially incremented the seconds, minutes etc... to get what we wanted, but then we were dealing with a finite number of keys that were required to be unique. This would be tricky or very slow if you were doing this on a large input stream.

I ultimately recommend that you reexamine the requirement for a date/time key since it may not be the best solution for your problem.

Regards,

Michael Hester

Put the following in a routine. It works when we upgraded to 5.2.2 running on HP-UX 11.11 so it should work for you in DS 6.0.

CurrTime = SYSTEM(12000)

Ans=OCONV(DATE(), "D/YMD[4,2,2]") : " " : OCONV(CurrTime, "MTS:") : ".":field(CurrTime,".",2)


Output
2003/09/04 14:51:18.261

Rob,

What trobinson posts may work on a HP-UX system, but does not work on Sun etc...

Keep in mind that if you are trying to ensure uniqueness based on millisecond then you will have duplicates and many DB's don't recognize milliseconds in timestamps so you are back to whole seconds. Because of the speed at which data is processed, milliseconds do not ensure uniqueness.

Regards,

Michael Hester

"DateCurrentDateTime" can be used in the transformer to insert timestamp for each row processed.

Shiv

Shiv,

DateCurrentDateTime will work for a column derivation, but not for the key (if you want the key to be unique) since the fractional or millisecond portion of the time is "hard coded" to be "000". I ran a test against 20,000 rows of data and used this transform to generate the key and this yielded 3 unique rows in a Hash table.

Regards,

Michael Hester