Try This for Demo
SELECT
distinct
MYDATE,
CASE
WHEN FLOOR(((SYSDATE) - MYDATE) * 24 * 60) < 1
THEN 'Arrived ' || FLOOR(((SYSDATE ) - MYDATE) * 24 * 60 * 60) || ' seconds ago'
WHEN FLOOR(((SYSDATE) - MYDATE) * 24 * 60) < 60
THEN 'Arrived ' || FLOOR(((SYSDATE ) - MYDATE) * 24 * 60) || ' minute ago'
WHEN FLOOR(((SYSDATE) - MYDATE) * 24) < 4
THEN 'Arrived ' || FLOOR(((SYSDATE ) - MYDATE) * 24) || ' hour ago'
ELSE
'Arrived on ' || to_char(MYDATE,'DD-Mon-YYYY HH24:MI:SS')
END AS MYMSG
FROM
(select (sysdate - 1/level) MYDATE from dual connect by level <= 24*60*60)
ORDER BY MYDATE DESC
Oracle: Dates Difference in days, hours, minutes & seconds
http://nimishgarg.blogspot.com/2009/12/get-dates-difference-in-days-hours.html
Oracle SQL: Date Difference in Days, Months and Years
http://nimishgarg.blogspot.com/2010/08/oracle-sql-date-difference-in-days.html
Oracle: Facebook Style Time of Post
http://nimishgarg.blogspot.com/2010/02/oracle-sql-facebook-style-time-of-post.html
Oracle: Facebook Style Time of Post - 2
http://nimishgarg.blogspot.com/2010/02/oracle-sql-facebook-style-time-of-post_23.html
Oracle: Some Important Date Queries
http://nimishgarg.blogspot.com/2010/11/oracle-some-important-date-queries.html
SELECT
distinct
MYDATE,
CASE
WHEN FLOOR(((SYSDATE) - MYDATE) * 24 * 60) < 1
THEN 'Arrived ' || FLOOR(((SYSDATE ) - MYDATE) * 24 * 60 * 60) || ' seconds ago'
WHEN FLOOR(((SYSDATE) - MYDATE) * 24 * 60) < 60
THEN 'Arrived ' || FLOOR(((SYSDATE ) - MYDATE) * 24 * 60) || ' minute ago'
WHEN FLOOR(((SYSDATE) - MYDATE) * 24) < 4
THEN 'Arrived ' || FLOOR(((SYSDATE ) - MYDATE) * 24) || ' hour ago'
ELSE
'Arrived on ' || to_char(MYDATE,'DD-Mon-YYYY HH24:MI:SS')
END AS MYMSG
FROM
(select (sysdate - 1/level) MYDATE from dual connect by level <= 24*60*60)
ORDER BY MYDATE DESC
Oracle: Dates Difference in days, hours, minutes & seconds
http://nimishgarg.blogspot.com/2009/12/get-dates-difference-in-days-hours.html
Oracle SQL: Date Difference in Days, Months and Years
http://nimishgarg.blogspot.com/2010/08/oracle-sql-date-difference-in-days.html
Oracle: Facebook Style Time of Post
http://nimishgarg.blogspot.com/2010/02/oracle-sql-facebook-style-time-of-post.html
Oracle: Facebook Style Time of Post - 2
http://nimishgarg.blogspot.com/2010/02/oracle-sql-facebook-style-time-of-post_23.html
Oracle: Some Important Date Queries
http://nimishgarg.blogspot.com/2010/11/oracle-some-important-date-queries.html
very useful post.
ReplyDeleteyou have written an excellent blog. I learned something new from your Blog. Keep sharing valuable information.
ReplyDeleteOracle Training in Chennai
Oracle Course in Chennai
Great Content. Thanks for sharing this valuable information.
ReplyDeleteVMware Training in Chennai
VMware Training in Bangalore
VMware Online Training
VMware Course in Chennai
VMware Course in Bangalore
VMware Online Course
This comment has been removed by the author.
ReplyDeleteNice blog article , Thanks for sharing with us
ReplyDeleteJava training
Thank you for sharing this Blog, This is more informative and nice.
ReplyDeleteJava Job Oriented Training in Hyderabad with Placements
I want to thank you for your time and effort in writing this post. I hope you continue to do your best work in the future as well.
ReplyDeleteBest Devops Course In Hyderabad
Nice Blog Keep Posting.
ReplyDeletePython Full Stack Development Training institute in Hyderabad
Nice blog Good explanation bout the Develop in Oracle, keep posting more
ReplyDeleteWeb Designing training institute in JNTU