A combination of UNIX_TIMESTAMP and SEC_TO_TIME should do it:
SELECT
UNIX_TIMESTAMP('1997-12-31 23:59:59.000001') as `date 1 in seconds`,
UNIX_TIMESTAMP('1997-12-30 01:01:01.000002') as `date 2 in seconds`,
UNIX_TIMESTAMP('1997-12-31 23:59:59.000001') -
UNIX_TIMESTAMP('1997-12-30 01:01:01.000002') as `diff in seconds`,
SEC_TO_TIME(
UNIX_TIMESTAMP('1997-12-31 23:59:59.000001') -
UNIX_TIMESTAMP('1997-12-30 01:01:01.000002')) as `diff in hh:mm:ss`
date 1 in seconds: 883634399 date 2 in seconds: 883465261 diff in seconds: 169138 diff in hh:mm:ss: 46:58:58
This throws away the msecs, though. Also, negative diffs work, surprisingly:
SELECT
UNIX_TIMESTAMP('1997-12-30 01:01:01.000002') as `date 1 in seconds`,
UNIX_TIMESTAMP('1997-12-31 23:59:59.000001') as `date 2 in seconds`,
UNIX_TIMESTAMP('1997-12-30 01:01:01.000002') -
UNIX_TIMESTAMP('1997-12-31 23:59:59.000001') as `diff in seconds`,
SEC_TO_TIME(
UNIX_TIMESTAMP('1997-12-30 01:01:01.000002') -
UNIX_TIMESTAMP('1997-12-31 23:59:59.000001')) as `diff in hh:mm:ss`date 1 in seconds: 883465261 date 2 in seconds: 883634399 diff in seconds: -169138 diff in hh:mm:ss: -46:58:58
So you've got that going for you.
____________________________________________________________ Eamon Daly
----- Original Message ----- From: "listsql listsql" <[EMAIL PROTECTED]>
To: "mysql" <[EMAIL PROTECTED]>
Sent: Tuesday, November 09, 2004 7:11 AM
Subject: Emulating timediff in 4.0 ?
TIMEDIFF(expr,expr2) TIMEDIFF() returns the time between the start time expr and the end time expr2. expr and expr2 are time or date-and-time expressions, but both must be of the same type.
mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001', -> '1997-12-30 01:01:01.000002'); -> '46:58:57.999999'
Any fast tip in how to emulate this on mysql 4.0 ? May save me hours of bitching :/
Thanks in advance.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
