You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@trafodion.apache.org by "Liu, Ming (Ming)" <mi...@esgyn.cn> on 2016/08/26 16:37:27 UTC
[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
RE: [help] is there a simpler way to translate an Oracle function?
Posted by "Liu, Ming (Ming)" <mi...@esgyn.cn>.
Thanks Kevin,
Yes, this is much better!
Thanks,
Ming
From: Xu, Kai-Hua (Kevin) [mailto:kaihua.xu@esgyn.cn]
Sent: Saturday, August 27, 2016 10:35 AM
To: user@trafodion.incubator.apache.org
Subject: RE: [help] is there a simpler way to translate an Oracle function?
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:ming.liu@esgyn.cn]
Sent: Saturday, August 27, 2016 12:37 AM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
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
RE: [help] is there a simpler way to translate an Oracle function?
Posted by "Xu, Kai-Hua (Kevin)" <ka...@esgyn.cn>.
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:ming.liu@esgyn.cn]
Sent: Saturday, August 27, 2016 12:37 AM
To: user@trafodion.incubator.apache.org
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