Help with Timezones

CloverDX Platform
1

Hi,

I’m having an issue when converting a string into a date with a timezone included. It looks like it wants to convert everything to the local time zone of the clover server instead of keeping the timezone of incoming data. The end goal is to convert the incoming date that is in text format into a date format.

if($in.0.foo == null ) $out.0.foo_Date = $in.0.foo_Date;
else
$out.0.foo_Date = str2date(
( substring($in.0.foo,0,4) + ‘-’ + substring($in.0.foo,5,2) + ‘-’ + substring($in.0.foo,8,2) + ’ ’ + substring($in.0.foo,11,2) + ‘:’ + substring($in.0.foo,14,2) + ‘:’ + ‘00’ ) + ’ ’ + substring($in.0.foo,17,3), “yyyy-MM-dd HH:mm:ss z”);

What’s the best way to resolve this? I have a separate output field that is just a string and the string is formatted correctly there.

2012-12-15 22:05:00 GST is an example of the incoming data. This gets converted to 2012-12-15 12:05:00 -0600

Any thoughts?
thanks!

2

I’m having an issue with a str2date conversion where I need to keep the timezone from the input data. Whenever I convert the date it picks up on the local timezone and doesn’t keep the source timezone. All I’m looking to do is convert the data from a string to a date type and keep the data the same (not convert the time into local time). What’s the easiest way do to this?

2012-12-15 07:45:00 AST is the input data. This gets changed to 2012-12-15 05:45:00 CST

Conversion code below…(the string logic is correct)

if($in.0.foo == null ) $out.0.foo_Date = $in.0.foo_Date;
else
$out.0.foo_Date = str2date(
( substring($in.0.foo,0,4) + ‘-’ + substring($in.0.foo,5,2) + ‘-’ + substring($in.0.foo,8,2) + ’ ’ + substring($in.0.foo,11,2) + ‘:’ + substring($in.0.foo,14,2) + ‘:’ + ‘00’ ) + ’ ’ + substring($in.0.foo,17,3), “yyyy-MM-dd HH:mm:ss z”);

3

Hi,

You can specify time zone in the str2date function using this format of the function:

date str2date(string input, string pattern, string locale, string timeZone);

Please see this example for your scenario:

$out.0.foo_date = str2date((substring($in.0.foo,0,4) + '-' + substring($in.0.foo,5,2) + '-' + substring($in.0.foo,8,2) + ' ' +  substring($in.0.foo,11,2) + ':' + substring($in.0.foo,14,2) + ':' + '00' ), "yyyy-MM-dd HH:mm:ss", "en.US", substring($in.0.foo,20,3));

The last attribute of the str2date function is the timezone parsed from your input string. That should parse your string date into date data type with the correct timezone.

Please note, you can also specify time zone of your metadata field by the attribute Time zone (please see the attached screenshot).
timezone.png
The Time zone attribute is used to specify the time offset used for parsing dates and writing dates as text. If the Time zone is not explicitly specified, CloverETL will use the system default time zone.

It means that if you try to show the current date on a debug edge or write the date into the flat file as a string, the date will be parsed by the Time zone property, which is set in your metadata. It happens in all situations where the date is automatically converted into the string. If you want to store your date as a string with a specified time zone, you should use function date2str with the time zone attribute.

4

Thanks - that got me pretty close. I had to take out the us.en string. I was getting an error that it expected 3 arguments and not four, which doesnt match the documentation (this is clover embedded into an Oracle product, so that could be the reason).

Is there a link anywhere which lists the value timezone strings that I can pass to the last arguement? I verified that the string is parsing correctly (AST, GMT, etc.), but my output is all showing up as GMT. There is a note in the documentation about

Time zone can either be specified using a time zone ID, e.g. “America/Los_Angeles”, which also takes daylight saving time into account, or using an absolute offset, e.g. “GMT+10”

Note that if an invalid string is specified as the Java time zone ID, no exception is thrown and Java uses the default “GMT” time zone (unlike Joda, which throws an exception).

So, I think when I pass AST for example it saying that it doesn’t like the abbreviation, so it defaults to AST.

So, it sounds like I need to recode the abbreviations to valid ID’s?

thanks!

5

Hi,

If you have available only function str2date with three parameters, I suppose you use this variant of that function:

date str2date(string input, string pattern, string locale);

As this issue relates to Oracle product please do contact Oracle support in this matter directly.

Regarding the time zone ID, I think the “AST” is correct time zone specification, all available IDs you can list by the java.util.TimeZone.getAvailableIDs(). Example of the list you can find here: http://garygregory.wordpress.com/2013/0 … ezone-ids/