I'm still trying with this. Can anyone else offer any support or examples ?
Thanks, Neil On Mon, Mar 8, 2010 at 6:02 PM, Tompkins Neil <neil.tompk...@googlemail.com>wrote: > Hi > > Thanks for the positive reply. Your stored procedure kind of works but > doesn't give me the desired results. However I will base my requirements on > this. > > Thanks again > Neil > > On Mon, Mar 8, 2010 at 2:42 PM, Price, Randall <randall.pr...@vt.edu>wrote: > >> Here is a MySQL stored procedure that I have used to format the difference >> between two dates: >> >> CREATE definer=`ro...@`127.0.0.1` FUNCTION `sp_maint_PeriodLength`(dt1 >> DATETIME, dt2 DATETIME) RETURNS char(128) CHARSET latin1 >> BEGIN >> >> DECLARE yy, mm, d0, dd, hh, mi, ss, t1 BIGINT; >> DECLARE t0 TIMESTAMP; >> >> SET yy = TIMESTAMPDIFF(YEAR, dt1, dt2); >> SET mm = TIMESTAMPDIFF(MONTH, dt1, dt2) MOD 12; >> SET d0 = TIMESTAMPDIFF(DAY, dt1, dt2); >> >> IF yy = 0 OR mm = 0 THEN >> SET dd = 0; >> ELSE >> SET dd = d0 MOD (yy * mm); >> END IF; >> >> SET t0 = TIMESTAMPADD(DAY, d0, dt1); >> SET t1 = TIME_TO_SEC(TIMEDIFF(dt2, t0)); >> SET hh = FLOOR(t1 / 3600); >> SET mi = FLOOR(t1 / 60) - 60 * hh; >> SET ss = t1 - 3600 * hh - 60 * mi; >> >> RETURN CONCAT( yy, ' years ', mm, ' months ', dd, ' days ', hh, ' >> hours ', mi, ' mins ', ss, ' secs' ); >> >> END >> >> Maybe this will help. >> >> Thanks, >> >> Randall Price >> Senior Programmer Analyst >> Virginia Tech >> >> >> >> -----Original Message----- >> From: Gavin Towey [mailto:gto...@ffn.com] >> Sent: Friday, March 05, 2010 4:15 PM >> To: Tompkins Neil; prabhat kumar >> Cc: [MySQL] >> Subject: RE: Displaying date/time >> >> That's probably something best done in your presentation (app) layer. >> >> If you must do this in mysql, then you'll probably want to write a stored >> function. >> >> -----Original Message----- >> From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] >> Sent: Friday, March 05, 2010 10:27 AM >> To: prabhat kumar >> Cc: [MySQL] >> Subject: Re: Displaying date/time >> >> Hi >> >> That is kind of what I'm looking for. However I'd like to be able to >> display the difference between date 1 and date 2 like >> >> 1d 2h 29min ago >> >> Thanks >> Neil >> >> On Fri, Mar 5, 2010 at 3:32 PM, prabhat kumar <aim.prab...@gmail.com> >> wrote: >> >> > Might be this will help you: >> > there is a table called message with 3 colums - id, pubdate and message; >> > You can get all messages from the last 5 minutes with the following >> > example; >> > >> > SELECT TIMESTAMPDIFF(MINUTE, pubdate, now()), id, message from message >> > where (TIMESTAMPDIFF(MINUTE, pubdate, now()) < 5); >> > >> > Thanks, >> > >> > >> > On Fri, Mar 5, 2010 at 8:19 PM, Tompkins Neil < >> > neil.tompk...@googlemail.com> wrote: >> > >> >> Hi >> >> >> >> I have a number of rows which have Date and Time data in. I want to >> >> display >> >> in the following formats based on the systems current time >> >> >> >> e.g under 1 hour 24min ago >> >> e.g under 1 day 16h 29min ago >> >> e.g over 1 day 1d 2h 29min ago >> >> e.g over 1 week 1w 4d 2h 29min ago >> >> >> >> How would this best be achieve using MySQL. >> >> >> >> Thanks, >> >> Neil >> >> >> > >> > >> > >> > -- >> > Best Regards, >> > >> > Prabhat Kumar >> > MySQL DBA >> > Datavail-India Mumbai >> > Mobile : 91-9987681929 >> > www.datavail.com >> > >> > My Blog: http://adminlinux.blogspot.com >> > My LinkedIn: http://www.linkedin.com/in/profileprabhat >> > >> >> This message contains confidential information and is intended only for >> the individual named. If you are not the named addressee, you are notified >> that reviewing, disseminating, disclosing, copying or distributing this >> e-mail is strictly prohibited. Please notify the sender immediately by >> e-mail if you have received this e-mail by mistake and delete this e-mail >> from your system. E-mail transmission cannot be guaranteed to be secure or >> error-free as information could be intercepted, corrupted, lost, destroyed, >> arrive late or incomplete, or contain viruses. The sender therefore does not >> accept liability for any loss or damage caused by viruses or errors or >> omissions in the contents of this message, which arise as a result of e-mail >> transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA >> 94089, USA, FriendFinder.com >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=randall.pr...@vt.edu >> >> >
