Time Zone Issue (Day Light Savings)


Problem :

We are  integrating Sales force with ERPLN data via Informatica Cloud ETL. The client is on ERPLN 6.1, Fp3, and Oracle 10 database and AIX OS. Both the LN Application and Oracle database are on the same Timezone GMT +1.00.

We observed that for Sales/Service Order dates, on Application front and on actual oracle database the date shown are different. There is a difference of either 1 hour or 2 hours, based on the day light savings in Europe.
Because of this difference, there is invariable changes in dates as well. For eg. Service Order date shown on ERPLN Application is 01-Jan-2013 00.12.25, but for the same service order the date stored in oracle database is 31-Dec-2012 23.12.25. Difference of 1 hour results in change of date as well. The difference d doesn’t remain the same over period, since there is day light savings as well.

So when we pull this data via ODBC connectivity in to Staging Database through Informatica cloud, the date shown is that of Oracle Database. When we push this date in to Salesforce, we want the dates shown same as that can be seen in ERPLN Application Front.

How do we achieve this?

Solution: 

It should be known that Date and time are stored in UTC format in the Database. ERPLN displays date and time according to user’s timezone settings. In User Data Template i.e. ttams1110m000, we define a template and maintain the Time Zone. This Template code is then attached to the User in User Data (ttaad1100m000).

For US  Time Zones: 

An Sales Order created by a user who is assigned Eastern Time at 5pm would have the same time stamp in the database as an order created by a user assigned Central Time at 4pm. Since the date is stored in UTC i.e. GMT +0:00. The person viewing the order created at 5pm eastern time would see the time listed as 4pm if they were on central time, and 2pm if they were on Pacific.

Definition of GMT and UTC:

GMT = Greenwich Mean Time

UTC = Universal Time Coordinated

In general GMT and UTC is considered to be the same.

In  Baan this “standard” is implemented according Unix standards using the UTC time format.

UTC format is the number of seconds from  0:00 hours 01-01-1970.

 This implementation of the UTC time is not affected by the DST (Day Light Savings)!!
Implementation in Baan Program

We can use the following function to convert the date timestamp from UTC to Local Time Zone.

utc.to.local()

Syntax

long utc.to.local(long utc, ref long local_days, ref long local_time )

Description

This converts a UTC long format value to local date and time.

Arguments

utc                          The UTC long format value.

local_days               The local date as the number of days since 01-01-0001.

local_time                The local time as the number of seconds since 00:00 hour.

Return values

0              success

-1              error

These functions are used in program scripts to make the conversion from UTC to local or vica versa.

To determine:

* the offset compared to GMT

* the beginning and ending of DST

the timezone has to be known.

This timezone information is stored in the userfile: $BSE/lib/user/u<baan

user name>

(for Windows NT: %BSE%\lib\user\u<baan user name>).

How does BaanERP determine the offset compared to GMT?

Directory /d1/baanerp/bse/lib/zoneinfo contains several data files for the

timezones, known in BaanERP.

From these file BaanERP retreives the data about the offset compared to GMT.

How does BaanERP determine the beginning and ending of DST?

Directory /d1/baanerp/bse/lib/zonerules contains several data files for the

zonerules.

From these file BaanERP retreives the data about the beginning and ending of

DST.

Check Solution No Solution: 78941

Implementation in SQL

We can use the following sql query to convert time from UTC to Local Time Zone

SELECT TO_CHAR(FROM_TZ(CAST(T$<fieldname> AS TIMESTAMP), ‘UTC’) AT TIME ZONE ‘EUROPE/BERLIN’,’DD-MM-YYYY HH24:MI:SS’) 

FROM BAAN.<Table_Code>

*Note –  This takes care of the Day Light Savings issue as well.

Advertisements