Unix TimeStamp Convert
Moderators: chulett, rschirm, roy
Unix TimeStamp Convert
Anyone know how to convert to a Unix timestamp... example:
'19-APR-2005 00:00:00' to 1113868800
'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
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
Last edited by ArndW on Wed Apr 20, 2005 9:28 am, edited 1 time in total.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
I was using this site: http://www.onlineconversion.com/unix_time.htm
mkeevil,mkeevil wrote:I was using this site: http://www.onlineconversion.com/unix_time.htm
you are doing seconds since 1/1/1970 so the formula that I gave earlier will work for you.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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")
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
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")