Dates Differences from Strings

[Ike]

If I have two String objects which are formatted date-times, say for
example:

'20060127 01:00:03'
and
'20060217 13:41:17'

Is there a means by which I could redisplay this as the difference between
the two, as a String, say, like, that may show the hh:mm:ss as well as the
number of days?

Thanks, Ike

[Joe Makowiec]

Re: Dates Differences from Strings

On 17 Feb 2006 in mailing.databas e.mysql, Ike wrote:
[color=blue]
> If I have two String objects which are formatted date-times, say for
> example:
>
> '20060127 01:00:03'
> and
> '20060217 13:41:17'
>
> Is there a means by which I could redisplay this as the difference
> between the two, as a String, say, like, that may show the hh:mm:ss
> as well as the number of days?[/color]

According to the MySQL 4.1 and 5.0 manuals:

DATEDIFF() returns the number of days between the start date expr
and the end date expr2. expr and expr2 are date or date-and-time
expressions. Only the date parts of the values are used in the
calculation.

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
-> -31

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Your best bet might be to convert them to Unix timestamps in PHP, and
do the arithmetic and formatting there.

http://www.php.net/manual/en/function.strtotime.php

http://www.php.net/manual/en/function.date.php

--
Joe Makowiec

http://makowiec.org/

Email: http://makowiec.org/contact/?Joe

[Ike]

Re: Dates Differences from Strings

Ah yes yes....actually TIMEDIFF() will do precisely what I need! THank you.

[Joe Makowiec]

Re: Dates Differences from Strings

On 17 Feb 2006 in mailing.databas e.mysql, Ike wrote:
[color=blue]
> Ah yes yes....actually TIMEDIFF() will do precisely what I need!
> THank you.[/color]

Just be careful of the date format you go in with:

mysql> select timediff('20060 127 01:00:03','2006 0217 13:41:17');
+---------------------------------------------------+
| timediff('20060 127 01:00:03','2006 0217 13:41:17') |
+---------------------------------------------------+
| -2172:41:14 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select timediff('2006-01-27 01:00:03','2006-02-17 13:41:17');
+-------------------------------------------------------+
| timediff('2006-01-27 01:00:03','2006-02-17 13:41:17') |
+-------------------------------------------------------+
| -516:41:14 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

--
Joe Makowiec

http://makowiec.org/

Email: http://makowiec.org/contact/?Joe

[Ike]

Re: Dates Differences from Strings

Right right...it needs to be specified, say, as '2006-02-17 12:13:14' for
the calculation to occur properly, yes?

[Joe Makowiec]

Re: Dates Differences from Strings

On 17 Feb 2006 in mailing.databas e.mysql, Ike wrote:
[color=blue]
> Right right...it needs to be specified, say, as '2006-02-17
> 12:13:14' for the calculation to occur properly, yes?[/color]

Looks that way.

--
Joe Makowiec

http://makowiec.org/

Email: http://makowiec.org/contact/?Joe