Add support for input/output of Timestamp in ISO format

[Suresh Subbiah]

Email conversation listed in reverse time order. ISO format is described here http://www.w3.org/TR/NOTE-datetime-970915.html

Thanks! We actually did option 2 on our application side as a workaround. Just want to know if Trafodion has better solution. For long term, I suggest trafodion support ISO 8601 directly, just like SQL server.

-----------------------------------------------------------------------------

Trafodion currently does not has something athat will allow it to directly handle timestamps in the ISO format (as far as I know, Anoop may know more).

I see two options
a) Write a small UDF that will allow conversion of ISO to the format accepted by Traf and use it inside the CAST expression shown below.
b) Use string functions to map the timestamp literal to a format where trafodion will accept it. For example
create table test1(a int not null, b timestamp) ;
insert into test1 values (2, CAST(REPLACE(REPLACE('2015-04-20 12:30:50', 'T', ' '), 'Z', '') AS TIMESTAMP)) ;
select * from test1 ;

A B
----------- --------------------------

          2 2015-04-20 12:30:50.000000

--- 2 row(s) selected.

Here we simply replaced T with blank space and Z with nothing. If serious time zone conversions are required maybe a UDF will be better. I suppose even a timezone could be handled with some kind of CASE statement.

Now do you see this kind of time zone literal occurring often in queries? We could provide some kind of builtin function support if such a query rewrite is not suitable. I think even a builtin function will not perform much better than this REPLACE type expression. UDFs will be slower.

------------------------------------------------------------------
We are doing the following

SELECT [FIRST 1] b.DATA FROM E.T_TIMELINE a, E.T_EVENT b WHERE a.EVENTCODE = 'newsEvent.publish' AND a.GUID = b.GUID AND a.TIMELINEDATETIME < CAST('2015-04-20T12:30:50Z' AS TIMESTAMP) ORDER BY a.TIMELINEDATETIME DESC;

But it doesn’t work.

----------------------------------------------------------------

What’s the suggested way to convery ISO timestamp '2015-04-20T12:30:50Z’ to Trafodion timestamp format in select? Thanks!