Skip to end of metadata
Go to start of metadata

A PL/SQL function to format time in a human friendly way

create or replace FUNCTION FUZZYTIME 
(
timevalue IN DATE
)
RETURN VARCHAR2
AS
type array_t is varray(100) of varchar2(100);
start_str array_t := array_t('It is','Time is','It_s','Current time is');__h_str array_t := array_t('one','two','three','four','five','six','seven','eight','nine','ten','eleven','twelve');_
mid_str array_t := array_t('roudabout','nearly','roughly','maybe');
ex_str array_t := array_t('exactly' , 'perpectly', 'exactly', 'exactly');
_m_str array_t := array_t('0','ten','twenty','thirty','fourty','fifty','60');_
r integer;
fuzzystr varchar2(1024);
head varchar2(1024);
mids varchar2(1024);
hours varchar2(1024);
minutes varchar2(1024);
hour_i integer;
h24_i integer;
minute_i integer;
ends varchar2(1024);
exs varchar2(1024);
BEGIN
r:= dbms_random.value(1,4);
head := start_str(r);
mids := mid_str(r);
minute_i := to_number(to_char(timevalue,'MI'));
hour_i := to_number(to_char(timevalue,'HH'));
h24_i := to_number(to_char(timevalue,'HH24'));
hours := h_str(hour_i);
minutes := m_str((round(minute_i/10+1)));
exs := ex_str(r);
ends := case
when h24_i between 0 and 6 then 'at night'
when h24_i between 6 and 13 then 'in the morning'
when h24_i between 13 and 19 then 'in the afternoon'
when h24_i between 19 and 23 then 'in the evening'
end;
if (minute_i in (0,60))
then
_fuzzystr := head||' '||hours||'_o clock '||ends;
elsif (minute_i in (10,20,30,40,50))
then
fuzzystr := head||' '||exs||' '||minutes||' '||hours||' '||ends;
elsif (minute_i between 0 and 9)
then
fuzzystr := head||' '||mids||' '||hours||' o clock '||ends;
elsif (minute_i between 51 and 59)
then
hours := h_str(hour_i+1);
_fuzzystr := head||' '||mids||' '||hours||'_o clock '||ends;
elsif (minute_i between 11 and 30)
then
fuzzystr := head||' '||mids||' '||minutes||' minutes after '||hours||' '||ends;
else
minutes := m_str((round((60-minute_i)/10+1)));
hours := h_str(hour_i+1);
fuzzystr := head||' '||mids||' '||minutes||' minutes to '||hours||' '||ends;
end if;
fuzzystr := fuzzystr||' ('||to_char(timevalue,'HH24:MI')||').';
RETURN fuzzystr;
END FUZZYTIME;