Thanks Eamon,
I was trying with something like:
SELECT stamp,now()+0, if ((now() - stamp)<6000, (now() - stamp)
/60,0) as waitminutes from smstablademotaxi
//I'm interested to get it only for the last 60 minutes
result:
+----------------+----------------+-------------+
| stamp | now()+0 | waitminutes |
+----------------+----------------+-------------+
| 20041109180348 | 20041109180455 | 1.78 |
| 20041109180350 | 20041109180455 | 1.75 |
+----------------+----------------+-------------+
Thanks for the feedback :)
On Tue, 9 Nov 2004 10:20:31 -0600, Eamon Daly <[EMAIL PROTECTED]> wrote:
> 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]
