Ex: feedscoming frm UK and my processing area isin Hokng,etc

sekharg_dw · Post by **sekharg_dw** » Mon Dec 12, 2005 12:34 am

Hi all,

example :-
Im getting feeds from Uk,india..etc and my porcessing areas are in USA,FRANCE,SINGAPORE etc. and these data is comming from Oracle database .
I need to change the data according to the processing location time and date i.e USA,Singapore,France Etc.

how to do that ( this is can be done through writting Java function) i wnt to know wether any alternative is avalible in Oracle / Datastage.

tnks in Adv

djm · Post by **djm** » Mon Dec 12, 2005 1:45 am

I'm interpreting your request as "how can you extract data from a system in one time zone and display the data meaningfully for another time zone" e.g. if the data has a timestamp of 15:00 in one time zone, for a time zone that is two hours in front of it, you want to diplay the time as 17:00. If this interpretation of your question is incorrect, the following answer may not be useful to you.

Faced with the scenario that you have, one approach is to extract all timestamps from a common based. One choice for doing this is for timestamps to be expressed as UTC (Universal Time Coordinates?), which also happens to be identical to GMT. So for example, I am writing this message at roughly 20:45 NZDT but it would be sensible to record that with a date/timestamp of 2005-12-12 07:45:00 UTC. When displaying the data to a user, it is then possible to turn it into a timestamp relative to their location. Using a common base also helps avoid issues of the same hour occurring multiple times in the same day (e.g. for locations that utilize daylight saving).

If you want to understand the technique further, I would recommend you read one (or more!) of Ralph Kimball's books.

HTH.

David

sekharg_dw · Post by **sekharg_dw** » Mon Dec 12, 2005 3:30 am

hope this will gv u a clear picture !

incomming feed :-< date-time>
12/12/2005/14:10:55 this is feed coming from India..
i need to process this feed in France and USA..
inorder to process frist i need to convert india time zone to USA and France .

rkdatastage · Post by **rkdatastage** » Mon Dec 12, 2005 4:00 am

Hi

In my view you have to answer my question that is the feeds which are coming from source are through flat files with any naming convention or you are connecting to a database server of each of source locations with different connection strings...?

RK

sekharg_dw · Post by **sekharg_dw** » Mon Dec 12, 2005 6:44 am

the source is comming from Oracle ! suppose in feed 1 date and time field comes with the location from which it is comming

to gv more clearity !
SOURCE:-
FEED1---> DATEAND TIME IS INDIA TIME--> PROCESSING AREA USA
FEED2---> DATE AND TIME IS SINGAPORE --> UK
....
....
..
FEED100---> DATEAND TIME IS INDIA TIME-->PROCESSING ARE HONGKONG

rkdatastage wrote:Hi

In my view you have to answer my question that is the feeds which are coming from source are through flat files with any naming convention or you are connecting to a database server of each of source locations with different connection strings...?

RK

ameyvaidya · Post by **ameyvaidya** » Mon Dec 12, 2005 1:15 pm

If I've understood the issue correctly:
Your data sources contain date/time fields in a time zone that is different from the time zone this data is to be processed in.

1. A little elaboration on what processing needs to be carried out would help. If all that is required is simple date/time differences, the time zone may even be irrelevant in the needed logic.

2. If the current (Source) date, time is necessary for processing these rows, a new column can be added and the derivation set to "sysdate" in the source SQl query. This should get the current timestamp from the source Database server's time zone.

OR
Create a sequence that first calls the below job:
Add a small server job that executes

Select sysdate from dual

Assign this date to userstatus.

In the calling sequence, this userstatus field can be passed to any job as a job parameter