Hi, all, I am trying to rewrite an Oracle function into Trafodion, There are some tricky timestamp Oracle functions that I tried my best, but still feel not correct, so ask for help here.
Question 1: Original one: select to_char(sysdate,'yyyy-mm-dd hh24')||':'||floor(to_char(sysdate,'mi')/5)*5 from t; My translation: select substring(cast(CURRENT_TIMESTAMP as CHAR(22)), 1, 13 )||':'||cast(cast(floor(MINUTE (CURRENT_TIMESTAMP )/5)*5 as integer ) as varchar(2) ) from t; It is rather ugly, especially the floor, for unknown reason it cast into string as 1.000+E01 style, not simply 10, so I have to cast it into INTEGER first, is there any simper function here I can use? And for number smaller than 10, it will print as 5, for example, but I cannot find a way to print '5' as '05' ... ... Question 2: Original Oracle syntax: upt_time>=to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi'),'yyyy-mm-dd hh24:mi')-5/(24*60) and upt_time<to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi'),'yyyy-mm-dd hh24:mi') I feel it is a range that within the last 5 minutes, but I cannot understand the original "/(24*60)" part... I take it as a range of past 5 mins, so my rewrite is like this: upt_time>= CURRENT_TIMESTAMP - INTERVAL '5' MINUTE and upt_time< CURRENT_TIMESTAMP Not sure if it is correct or not... Thanks, Ming
