Date Timezone Conversion
Moderators: chulett, rschirm, roy
Date Timezone Conversion
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
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
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.