Displaying date/time

--00151748dd320d0a1b04810ed1ac
Content-Type: text/plain; charset=ISO-8859-1

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

--00151748dd320d0a1b04810ed1ac--

Re: Displaying date/time

--001636e0a65e92f35104810f6dd9
Content-Type: text/plain; charset=ISO-8859-1

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

--001636e0a65e92f35104810f6dd9--

Re: Displaying date/time

--000e0ce0f3c83c2241048111de5e
Content-Type: text/plain; charset=ISO-8859-1

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

--000e0ce0f3c83c2241048111de5e--

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 f=
unction.

-----Original Message-----
From: Tompkins Neil [mailto:neil.tompkins@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 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@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 th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Displaying date/time

Here is a MySQL stored procedure that I have used to format the difference =
between two dates:

CREATE DEFINER=3D`root`@`127.0.0.1` FUNCTION `sp_maint_PeriodLength`(dt1 DA=
TETIME, dt2 DATETIME) RETURNS char(128) CHARSET latin1
BEGIN

DECLARE yy, mm, d0, dd, hh, mi, ss, t1 BIGINT;
DECLARE t0 TIMESTAMP;

SET yy =3D TIMESTAMPDIFF(YEAR, dt1, dt2);
SET mm =3D TIMESTAMPDIFF(MONTH, dt1, dt2) MOD 12;
SET d0 =3D TIMESTAMPDIFF(DAY, dt1, dt2);

IF yy =3D 0 OR mm =3D 0 THEN
SET dd =3D 0;
ELSE
SET dd =3D d0 MOD (yy * mm);
END IF;

SET t0 =3D TIMESTAMPADD(DAY, d0, dt1);
SET t1 =3D TIME_TO_SEC(TIMEDIFF(dt2, t0));
SET hh =3D FLOOR(t1 / 3600);
SET mi =3D FLOOR(t1 / 60) - 60 * hh;
SET ss =3D t1 - 3600 * hh - 60 * mi;

RETURN CONCAT( yy, ' years ', mm, ' months ', dd, ' days ', hh, ' h=
ours ', mi, ' mins ', ss, ' secs' );

END

Maybe this will help.

Thanks,

Randall Price
Senior Programmer Analyst
Virginia Tech



-----Original Message-----
From: Gavin Towey [mailto:gtowey@ffn.com]=20
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 f=
unction.

-----Original Message-----
From: Tompkins Neil [mailto:neil.tompkins@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 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@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 th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3DRandall.Price@vt.ed=
u


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Displaying date/time

--00151747be1c8ef48b04814ddfad
Content-Type: text/plain; charset=ISO-8859-1

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 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@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.tompkins@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
> 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@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.Price@vt.edu
>
>

--00151747be1c8ef48b04814ddfad--

Re: Displaying date/time

--0015174764702ef9c60481626d73
Content-Type: text/plain; charset=ISO-8859-1

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
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 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@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.tompkins@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
>> 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@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.Price@vt.edu
>>
>>
>

--0015174764702ef9c60481626d73--

MySQL - licensing data recovery environments

Hi all,
does anybody know where can I find a recent guide/information regarding
the licensing of data recovery environments with /MySQL Enterprise Server/?




Regards
Dimitre

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org