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]

On 17 Feb 2006 in mailing.database.mysql, Ike wrote:

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?

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/...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]

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

[Joe Makowiec]

On 17 Feb 2006 in mailing.database.mysql, Ike wrote:

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

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

mysql> select timediff('20060127 01:00:03','20060217 13:41:17');
+---------------------------------------------------+
| timediff('20060127 01:00:03','20060217 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]

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

[Joe Makowiec]

On 17 Feb 2006 in mailing.database.mysql, Ike wrote:

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

Looks that way.

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