DSXchange

Anyone know how to convert to a Unix timestamp... example:

'19-APR-2005 00:00:00' to 1113868800

mkeevil,

there are more date conversion options in DataStage than you'd want to shake a stick at.

But please explain the way you get 1113868800 from '19-APR-2005'. The internal DS format is days-since-31/12/1967; Your number looks like seconds-since-1970/01/01. If that is the case, the formula is

Fmt(IConv(YourDateTime[1, 11], "D-DMY[2,A3,4]"), "5'0'R") : Fmt(IConv(YourDateTime[13, 8], "MTS"), "5'0'R")

I was using this site: http://www.onlineconversion.com/unix_time.htm

Sainath,

I think you forgot to convert from DS internal "0" date (31/12/1967) and the Julian from the poster (01/01/1970)...

mkeevil wrote:I was using this site: http://www.onlineconversion.com/unix_time.htm

mkeevil,

you are doing seconds since 1/1/1970 so the formula that I gave earlier will work for you.

I want to put them in a batch.... and pass them into an extract.. how would it look like? The above examples look like they go into a transform

ArndW,

I typed it online and did not check the return value. You are correct in highlighting the change required.

I assume that we need manipulate the time bit also.

While I was typing the post, I had a glimpse of an error mail raised by mkeevil. Surprisingly when I submitted mine that post disappeared. Is it due to some problem in the site or had the user withdrawn the post?

By-the-way, Mkeevil, I am using the variable YourDateTime to be passed to the transform. Hence you need to supply this value. It is not an 'additional' variable to be supplied.

Time will always be 00:00:00 because I want to extract everything from yesterdays date until today... so it would be '19-APR-2005 00:00:00' to '20-APR-2005 00:00:00' I would like to pass both dates in there unix format into an extract to use in the where clause.

This works. Remember that UNIX time starts at 1-JAN-1970 00:00:00

Function UnixTime(Arg1)

timepiece = Arg1[13,8]
datepiece = Arg1[1,11]

* '19-APR-2005 00:00:00' to 1113868800

secsinday = 60 * 60 * 24

Ans = (iconv(datepiece,"D-") - iconv("1 jan 1970","D")) * secsinday + iconv(timepiece, "MTS")

I removed that post because I saw that it was a transform statement.. I needed something that went into a batch. That was my error

That is nice. To speed up the process, you can compute the iconv(1/1/1970) and use the integer value in its place.

REVISED: Ohhh I think I have it... got to test but I guess I have to use the DEFUN command and pass in the parms.

I am still kind of new at Data Stage so I need to create a function with this code, and then call the function with Arg1 and return Ans. Do I have to make sure that Arg1, is in a specific format, example: 01-Jan-2005 00:00:00 or 1-Jan-2005 00:00:00? Also how would I execute this function from the batch so I can pass the Ans into my extract program?

Function UnixTime(Arg1)

timepiece = Arg1[13,8]
datepiece = Arg1[1,11]

* '19-APR-2005 00:00:00' to 1113868800

secsinday = 60 * 60 * 24

Ans = (iconv(datepiece,"D-") - iconv("1 jan 1970","D")) * secsinday + iconv(timepiece, "MTS")

The Arg1 format has to be the same, unless you want to pass in a format string as another arg, or put some parsing logic inside the function.

You can do it as a function, or inline as suggested above by pre-computing the secsindays and iconv('1 jan 1970','D'):

So if timestamp = '19-APR-2005 00:00:00' then

(iconv(timestamp[1,11],"D-")-732)*86400+iconv(timestamp[13,8],"MTS")

yields 1113868800


Carter

mkeevil wrote:REVISED: Ohhh I think I have it... got to test but I guess I have to use the DEFUN command and pass in the parms.

I am still kind of new at Data Stage so I need to create a function with this code, and then call the function with Arg1 and return Ans. Do I have to make sure that Arg1, is in a specific format, example: 01-Jan-2005 00:00:00 or 1-Jan-2005 00:00:00? Also how would I execute this function from the batch so I can pass the Ans into my extract program?

Function UnixTime(Arg1)

timepiece = Arg1[13,8]
datepiece = Arg1[1,11]

* '19-APR-2005 00:00:00' to 1113868800

secsinday = 60 * 60 * 24

Ans = (iconv(datepiece,"D-") - iconv("1 jan 1970","D")) * secsinday + iconv(timepiece, "MTS")