create or replace package body date_util
as
g_since_used boolean := true;
g_seconds_ago varchar2(255) := '#time# seconds ago';
g_minutes_ago varchar2(255) := '#time# minutes ago';
g_hours_ago varchar2(255) := '#time# hours ago';
g_days_ago varchar2(255) := '#time# days ago';
g_weeks_ago varchar2(255) := '#time# weeks ago';
g_months_ago varchar2(255) := '#time# months ago';
g_years_ago varchar2(255) := '#time# years ago';
g_seconds_from_now varchar2(255) := '#time# seconds from now';
g_minutes_from_now varchar2(255) := '#time# minutes from now';
g_hours_from_now varchar2(255) := '#time# hours from now';
g_days_from_now varchar2(255) := '#time# days from now';
g_weeks_from_now varchar2(255) := '#time# weeks from now';
g_months_from_now varchar2(255) := '#time# months from now';
g_years_from_now varchar2(255) := '#time# years from now';
g_now varchar2(255) := 'now';
g_sysdate date;
g_systimestamp_gmt timestamp;
function get_since_date (
p_date in date,
p_sysdate in date default null
) return varchar2 is
l_date date;
l_sysdate date;
begin
l_date := p_date;
if p_sysdate is null then
if g_sysdate is null then
g_sysdate := sysdate;
end if;
l_sysdate := g_sysdate;
else
l_sysdate := p_sysdate;
end if;
if p_date is null then
return null;
elsif l_sysdate = l_date then
return g_now;
elsif l_sysdate-l_date between 0 and 1/720 then
return replace(g_seconds_ago, '#time#', round(24*60*60*(l_sysdate-l_date)));
elsif l_date - l_sysdate between 0 and 1/720 then
return replace(g_seconds_from_now, '#time#', round(24*60*60*(l_date - l_sysdate )));
elsif l_sysdate-l_date between 1/720 and 1/12 then
return replace(g_minutes_ago, '#time#', round(24*60*(l_sysdate-l_date )));
elsif l_date - l_sysdate between 1/720 and 1/12 then
return replace(g_minutes_from_now, '#time#', round(24*60*(l_date - l_sysdate)));
elsif l_sysdate-l_date between 1/12 and 2 then
return replace(g_hours_ago, '#time#', round(24*(l_sysdate-l_date )));
elsif l_date - l_sysdate between 1/12 and 2 then
return replace(g_hours_from_now, '#time#', round(24*(l_date - l_sysdate )));
elsif l_sysdate-l_date between 2 and 14 then
return replace(g_days_ago, '#time#', trunc(l_sysdate-l_date));
elsif l_date - l_sysdate between 2 and 14 then
return replace(g_days_from_now, '#time#', trunc(l_date - l_sysdate));
elsif l_sysdate-l_date between 14 and 60 then
return replace(g_weeks_ago, '#time#', trunc((l_sysdate-l_date )/7));
elsif l_date - l_sysdate between 14 and 60 then
return replace(g_weeks_from_now, '#time#', trunc((l_date - l_sysdate)/7));
elsif l_sysdate-l_date between 60 and 365 then
return replace(g_months_ago, '#time#', round(months_between(l_sysdate,l_date )));
elsif l_date - l_sysdate between 60 and 365 then
return replace(g_months_from_now, '#time#', round(months_between(l_date,l_sysdate)));
elsif l_date < l_sysdate then
return replace(g_years_ago, '#time#', round(months_between(l_sysdate,l_date )/12,1));
elsif l_date > l_sysdate then
return replace(g_years_from_now, '#time#', round(months_between(l_date,l_sysdate)/12,1));
end if;
return null;
end get_since_date;
-- ----------------------------------------------------------------------------------------
--
function get_since_tswltz (
p_ltimestamp timestamp with local time zone
) return varchar2 is
l_timestamp timestamp;
l_date date;
l_sysdate date;
begin
l_timestamp := p_ltimestamp at time zone 'GMT';
l_date := l_timestamp;
if g_systimestamp_gmt is null then
g_systimestamp_gmt := systimestamp at time zone 'GMT';
end if;
l_sysdate := g_systimestamp_gmt;
if p_ltimestamp is null then
return null;
elsif g_systimestamp_gmt = l_timestamp then
return g_now;
else
return get_since_date ( p_date => l_date, p_sysdate => l_sysdate );
end if;
end get_since_tswltz;
function get_since_tswtz(
p_timestamp timestamp with time zone
) return varchar2 is
l_timestamp timestamp;
l_date date;
l_sysdate date;
begin
l_timestamp := p_timestamp at time zone 'GMT';
l_date := l_timestamp;
if g_systimestamp_gmt is null then
g_systimestamp_gmt := systimestamp at time zone 'GMT';
end if;
l_sysdate := g_systimestamp_gmt;
if p_timestamp is null then
return null;
elsif g_systimestamp_gmt = l_timestamp then
return g_now;
else
return get_since_date ( p_date => l_date, p_sysdate => l_sysdate );
end if;
end get_since_tswtz;
--
-- overloaded functions
--
function get_since(
p_date date
) return varchar2 is
begin
return get_since_date (p_date => p_date);
end;
function get_since(
p_value in timestamp ) return varchar2 is
begin
return get_since_date (p_date => p_value);
end;
function get_since(
p_value in timestamp with time zone ) return varchar2 is
begin
return get_since_tswtz (p_value);
end;
function get_since(
p_value in timestamp with local time zone ) return varchar2 is
begin
return get_since_tswltz (p_value);
end;
end ;