codemonth.dk

One project every month - making stuff better ...

Difference in time - a plsql solution to human readable date/timestamp diff

While preparing to get the comments section done for my codemonth site, I needed something that could tell how old a comment was. Not just showing the date, but maybe showing in a bit more readable way, when the comment was from. So if you look at sites like Twitter and Facebook, comments are "dated" with strings like "1 hrs" or "1 day old" etc. So I wanted to make a small package that could do that for me.

So head on over to the github repository for time_ninja, and download the package and the package body, and install them in your database:


SQL> @time_ninja.package.sql

Package created.

SQL> @"time_ninja.package body.sql"

Package body created.

SQL>

Currently there are two main programs in this package. time_ninja.time_to and time_ninja.time_from. Both can accept either a date or a timestamp. So instead of trying to explain what it does, let me show you a demo:


SQL> select time_ninja.time_from(sysdate-231) from dual;

TIME_NINJA.TIME_FROM(SYSDATE-231)
----------------------------------------------------------------------
8 months ago

SQL> select time_ninja.time_from(sysdate-14) from dual;

TIME_NINJA.TIME_FROM(SYSDATE-14)
----------------------------------------------------------------------
14 days ago

SQL> select time_ninja.time_from(sysdate-0.034) from dual;

TIME_NINJA.TIME_FROM(SYSDATE-0.034)
----------------------------------------------------------------------
an hour ago

SQL> select time_ninja.time_from(sysdate-0.0002) from dual;

TIME_NINJA.TIME_FROM(SYSDATE-0.0002)
----------------------------------------------------------------------
a few seconds ago

SQL> 

and time_to:


SQL> select time_ninja.time_to(sysdate+0.0023) from dual;

TIME_NINJA.TIME_TO(SYSDATE+0.0023)
----------------------------------------------------------------------
in 3 minutes

SQL> select time_ninja.time_to(sysdate+0.123) from dual;

TIME_NINJA.TIME_TO(SYSDATE+0.123)
----------------------------------------------------------------------
in 3 hours

SQL> select time_ninja.time_to(sysdate+1354) from dual;

TIME_NINJA.TIME_TO(SYSDATE+1354)
----------------------------------------------------------------------
in 4 years

SQL> 

I will be adding a couple of extra functions over the few days, as well as some more control parameters to the existing functions.

Tagged in : months_between