gmt data

randy · Post by **randy** » Tue Jul 11, 2006 2:09 pm

A new data file is containg date/time formated as gmt.
I need to load this into an Oracle table, but am not sure how to handle it.

Can someone help me?

Thanks
Randy

kcbland · Post by **kcbland** » Tue Jul 11, 2006 2:13 pm

You mean Greenwich Mean Time? Are you asking how to add X hours to a date time to make it local to your standard? Write a DS function, search the forum, there's lots of examples.

ray.wurlod · Post by **ray.wurlod** » Tue Jul 11, 2006 2:13 pm

Look at the SDK routines - there's processing available there for GMT. You do have to know your timezone to convert. And be aware of summer time shifts.

randy · Post by **randy** » Tue Jul 11, 2006 2:16 pm

I did a search for gmt and found very little.

kcbland · Post by **kcbland** » Tue Jul 11, 2006 2:19 pm

Look at the final message on this post from DSGuru2B:

viewtopic.php?t=100080&highlight=add+date

randy · Post by **randy** » Tue Jul 11, 2006 2:26 pm

Thanks,

the GMT data looks like this: 0493558525

I am not sure how to sure the examples, but will spend the night working on it.

kcbland · Post by **kcbland** » Tue Jul 11, 2006 2:31 pm

No, your date is EPOCH, the number of seconds since Jan 1, 1970. I don't know if we've got one of those.

Krazykoolrohit · Post by **Krazykoolrohit** » Tue Jul 11, 2006 2:37 pm

use the iconv and oconv functions to convert. check datastage help for usage

kcbland · Post by **kcbland** » Tue Jul 11, 2006 2:38 pm

I suppose you could take your EPOCH value, divide it by 86400 (the number of seconds in a day) to get the whole number of days in the date. The DS BASIC internal date starts at 1 on January 1, 1968. So, you can work out the equation necessary to compute the date value you need. Then, take the number of seconds remaining when dividing your EPOCH by 86400 and turn that into hours, minutes, and seconds and construct your final timestamp value in the forum YYYY-MM-DD HH24:MI:SS.

kcbland · Post by **kcbland** » Tue Jul 11, 2006 2:41 pm

Krazykoolrohit wrote:use the iconv and oconv functions to convert. check datastage help for usage

No. ICONV and OCONV work on integer days, not seconds. The time conversions don't include date components. You must write a custom function.

ray.wurlod · Post by **ray.wurlod** » Tue Jul 11, 2006 3:54 pm

There are 1900 and 1970 epoch Transforms in the SDK.
DateGenericToDaysSince1900
DateGenericToDaysSince1970

You actually need the reverse of these, to calculate the date that is so many seconds since 1900-01-01 00:00:00

You can convert seconds to days by dividing by 86400. If all you want is the date, use

Code: Select all

Oconv(Int(InLink.MyEpoch/86400)-Iconv("1970-01-01","DYMD"),"D-YMD[4,2,2]")

or something similar, depending on your base date.