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