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