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
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