Oracle: Facebook Style Time of Post

Example:
Arrived 0 minute ago
Arrived 1 minute ago
Arrived 2 minutes ago
Arrived 59 minutes ago
Arrived 1 hour ago
Arrived 2 hours ago
Arrived 3 hours ago

Table MYBLOG
-------------------------------------------
MESSAGE                         VARCHAR2(1000)
ARRIVAL_DATE             DATE





SELECT
    MESSAGE,
    CASE
        WHEN FLOOR(((SYSDATE +  INTERVAL '330' minute) - ARRIVAL_DATE) * 24 * 60) < 60
            THEN 'Arrived ' || FLOOR(((SYSDATE +  INTERVAL '330' minute) - ARRIVAL_DATE) * 24 * 60) ||  'minute ago'
        WHEN FLOOR(((SYSDATE +  INTERVAL '330' minute) - ARRIVAL_DATE) * 24) < 4
            THEN 'Arrived ' || FLOOR(((SYSDATE +  INTERVAL '330' minute) - ARRIVAL_DATE) * 24) || ' hour ago'
        ELSE
            'Arrived on 17 February 2010 16:39:45 IST'
    END AS MYTIMESTRING
FROM
    MYBLOG
ORDER BY
    ARRIVAL_DATE DESC



*Note: (SYSDATE +  INTERVAL '330' minute) is used to conver IST to GMT (+5:30)


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

No comments:

Post a Comment