Unix TimeStamp Convert

mkeevil · Post by **mkeevil** » Wed Apr 20, 2005 8:48 am

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

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

ArndW · Post by **ArndW** » Wed Apr 20, 2005 9:13 am

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

Code: Select all

(ICONV(In.DateColumn,"D4-DMY[2,3,4]')-ICONV('01-JAN-1970','D4-DMY[2,3,4]'))*60*60*24

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Apr 20, 2005 9:15 am

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

mkeevil · Post by **mkeevil** » Wed Apr 20, 2005 9:24 am

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

ArndW · Post by **ArndW** » Wed Apr 20, 2005 9:26 am

Sainath,

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

ArndW · Post by **ArndW** » Wed Apr 20, 2005 9:29 am

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.

mkeevil · Post by **mkeevil** » Wed Apr 20, 2005 9:32 am

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

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Apr 20, 2005 9:37 am

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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Apr 20, 2005 9:41 am

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.

mkeevil · Post by **mkeevil** » Wed Apr 20, 2005 9:42 am

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.

clshore · Post by **clshore** » Wed Apr 20, 2005 9:42 am

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")

mkeevil · Post by **mkeevil** » Wed Apr 20, 2005 9:44 am

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

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Apr 20, 2005 9:44 am

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

mkeevil · Post by **mkeevil** » Wed Apr 20, 2005 9:48 am

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")

clshore · Post by **clshore** » Wed Apr 20, 2005 10:11 am

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")