Hi Ming,
I've a better one to answer your two questions. Have fun!
DATE_TRUNC('HOUR', current_timestamp) + (minute('2006-12-31 11:52:59')-
mod(minute('2006-12-31 11:52:59') ,5))*60
Best Regards,
Kevin Xu
From: Liu, Ming (Ming) [mailto:[email protected]]
Sent: Saturday, August 27, 2016 12:37 AM
To: [email protected]
Subject: [help] is there a simpler way to translate an Oracle function?
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
