Date Timezone Conversion

thompsonp · Post by **thompsonp** » Tue Apr 04, 2006 8:00 am

I need to convert several fields (could be characters, timetamps or dates, doesn't really matter) in a row of data from one timezone to another before they are stored in the database. This would need to take account of daylight saving.

I thought that a parallel routine, accessible in the derivation of a transformer, would be a nice place to do this.

Obviously this would need writing in C++ and from a quick look on the web it is non standard but the ICU library and others support it.

Before I go off down this path and get the sys admin person to install the library etc, can anyone suggest a better approach, or has anyone already done something similar that they'd like to share?

Thanks

kumar_s · Post by **kumar_s** » Tue Apr 04, 2006 8:38 am

By saying Converting from one timestamp to another, do you mean updating set of records from old file to a new file with a updated timestamp?

thompsonp · Post by **thompsonp** » Tue Apr 04, 2006 8:46 am

It doesn't really matter whether the input data is a character, date or timestamp. It represents a date and time and another field gives the timezone for that data.

I need to convert the date time from the supplied timezone to another one.

e.g.

DateTime TZ
200604010200 BST

needs converting to GMT

kumar_s · Post by **kumar_s** » Tue Apr 04, 2006 8:51 am

If you search for the same in the forum, you may get some informations like using Basic routine or TZ environmental variable from unix.
The first option may not be usefule for your case, since you need to work out record wise. Perhaps you can try for the second approach.

thompsonp · Post by **thompsonp** » Tue Apr 04, 2006 9:17 am

The first option (Basic) is not really suitable as I want to use a parallel transformer rather than a Basic transformer.

As far as I am aware the TZ environment variable just shows what timezone the unix account is using (e.g. my dsadm account has it set as GMT0BST) which is irrelevant as each row of data says what timezone it is in and I will have the timezone I want to convert it to as a job parameter.

anujgarg · Post by **anujgarg** » Tue Apr 04, 2006 11:29 pm

Can anyone pls explain How to get the current timezone of system using that TZ unix env variables as i am not aware of that.

Pls explain in little detail how to get that in PX job.

Thanks,
Anuj

ray.wurlod · Post by **ray.wurlod** » Tue Apr 04, 2006 11:40 pm

Set up $TZ as a job parameter. Make its default value $ENV, so that it's picked up from the UNIX environment.

jking123 · Post by **jking123** » Fri Aug 11, 2006 12:41 pm

I didn't see the answer to this question through searches. Am I assuming correctly that this is currently not possible in Datastage and I will have to write an extternal routine using icu etc and call it thru datasatge.

In general the question is like this:
If my data contains in one row
date1,timezone1,date2,timezone2,date3,timezone3
and I need to convert all the above to GMT
then currently not possible using any built in routine or functionality.
timezones can be any timezone.

ray.wurlod · Post by **ray.wurlod** » Sat Aug 12, 2006 12:57 am

Create a Lookup File Set showing, for each time zone, the offset from GMT (or UTC if you're a purist!), the date daylight saving kicks in and the date daylight saving kicks out. Keep this maintained (up to date). Use a Lookup stage to get the offset, and adjust hours accordingly (convert hours to seconds and use TimeSecondsFromTime() or whatever that function is called).