List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:March 8 2010 6:02pm
Subject:Re: Displaying date/time
View as plain text  
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.Price@stripped> wrote:

> Here is a MySQL stored procedure that I have used to format the difference
> between two dates:
>
> CREATE DEFINER=`root`@`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:gtowey@stripped]
> 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.tompkins@stripped]
> 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.prabhat@stripped>
> 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.tompkins@stripped> 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=1
>
>

Thread
Displaying date/timeTompkins Neil5 Mar
  • Re: Displaying date/timeprabhat kumar5 Mar
    • Re: Displaying date/timeTompkins Neil5 Mar
      • RE: Displaying date/timeGavin Towey5 Mar
        • RE: Displaying date/timeRandall Price8 Mar
          • Re: Displaying date/timeTompkins Neil8 Mar
            • Re: Displaying date/timeTompkins Neil9 Mar
          • MySQL - licensing data recovery environmentsDimitre Radoulov6 Apr