Skip to main content
  • EN

Hi,

I am currently calculating date time intervals via the DateTimeCalculator that result is ISO Duration format (PT28M20S for example)

I would like to format the result into a FME datetime but havent found a way to do it with the DateTimeConverter. I can split the value into its parts and then combine them but there should be an easier way...

I am surprised that FME doesn't recognize and can parse this date time notation, after all FME is producing it....

Using FME 2019.2.0.0 build 19801

Could be I am missing something, any assistance would be appreciated.

Cheers,

Itay

PT28M20S is a duration of 28 minutes and 20 seconds, it can't be converted to an FME datetime. Or do you mean you want to add it onto a known date time?

PT28M20S is a duration of 28 minutes and 20 seconds, it can't be converted to an FME datetime. Or do you mean you want to add it onto a known date time?

Hi @ebygomm,

No I want to format it into a FME datetime, so that I can eventually write it out as a datetime.

So I guess it's gonna be 'the hard way'....padding, etc

Hi @ebygomm,

No I want to format it into a FME datetime, so that I can eventually write it out as a datetime.

So I guess it's gonna be 'the hard way'....padding, etc

But it's not a date time it's a duration - what datetime would you expect PT28M20S to become?

PT28M20S is a duration of 28 minutes and 20 seconds, it can't be converted to an FME datetime. Or do you mean you want to add it onto a known date time?

True, I need to write it into an oracle date time data type.

@itay Oracle and other databases do have Interval date types. This isn't something we have tackled in FME yet so you'll have to parse the FME Duration for now. Also, I don't think the FME feature types support the 'interval' data types so you'll have to create the table before you write to it.

@itay Oracle and other databases do have Interval date types. This isn't something we have tackled in FME yet so you'll have to parse the FME Duration for now. Also, I don't think the FME feature types support the 'interval' data types so you'll have to create the table before you write to it.

Thx for the info @markatsafe, luckily the table already exists.

Thx for the info @markatsafe, luckily the table already exists.

If you're actually wanting to write to an interval date type column if you want to avoid parsing the date time interval you can create a dummy date which contains the interval added onto an arbitrary date

and write that into a temporary table. Then populate the interval column with a query by taking the arbitrary date away from the dummy date.

FME doesn't play nicely with columns of interval date types (unless this has changed in 2020) so that's something to be aware of. You cannot 'see' the interval columns with the reader, you can read with sqlcreator/sqlexecutor if you convert them (and know they're there and their names)

Hi @ebygomm,

No I want to format it into a FME datetime, so that I can eventually write it out as a datetime.

So I guess it's gonna be 'the hard way'....padding, etc

You could probably do some string manipulation, but there is also an easy to use python library isodate that will do it for you.

 

pip install isodate

 

import isodate 
duration = 'PT28M20S'
print(isodate.parse_duration(duration))

Result

0:28:20

You could probably do some string manipulation, but there is also an easy to use python library isodate that will do it for you.

 

pip install isodate

 

import isodate 
duration = 'PT28M20S'
print(isodate.parse_duration(duration))

Result

0:28:20

Hi @warrengis, that does look interesting...

thx for the tip.

If you're actually wanting to write to an interval date type column if you want to avoid parsing the date time interval you can create a dummy date which contains the interval added onto an arbitrary date

and write that into a temporary table. Then populate the interval column with a query by taking the arbitrary date away from the dummy date.

FME doesn't play nicely with columns of interval date types (unless this has changed in 2020) so that's something to be aware of. You cannot 'see' the interval columns with the reader, you can read with sqlcreator/sqlexecutor if you convert them (and know they're there and their names)

that is a very interesting approach, thx for the tip.

Reply


Community Stats

30,177
Posts
114,555
Replies
37,843
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.