Re: MySQL Date/Time Problem - Alexander Keremidarski - com.mysql.lists.bugs

5 messages in com.mysql.lists.bugsRe: MySQL Date/Time Problem

From	Sent On	Attachments
Graeme Ferguson	01 Aug 2003 18:35
Sinisa Milivojevic	02 Aug 2003 05:50
Graeme Ferguson	02 Aug 2003 18:44
Alexander Keremidarski	03 Aug 2003 01:34
Graeme Ferguson	03 Aug 2003 17:11

Subject:	Re: MySQL Date/Time Problem
From:	Alexander Keremidarski (sal...@mysql.com)
Date:	08/03/2003 01:34:55 AM
List:	com.mysql.lists.bugs

Hello,

Graeme Ferguson wrote:

Hi there, This has me stuffed, but I am a newbie to mysql. The following query performed on the following data returns the correct duration between times for some fields, whilst others are 40 seconds out.

You can hardly get any valid result with your query.

The wrong part is here:

... ((b.dtg_timestamp-a.dtg_timestamp)) AS duration ...

CREATE TABLE `tbl_citectalarms` ( ... `dtg_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

Accorrding to MySQL conversion rules when used in Numeric context DATETIME is converted to number by stripig all non-numeric characters.

Which means value '2099-01-05 10:12:45' becomes 20990105101245 I hope this is enough for you to see why above subtraction is invalid.

It is Ok to compare such values but subtraction will be meaningless except for some cases when difference is within seconds.

Correct way to perofrm above calculation is:

... (UNIX_TIMESTAMP(b.dtg_timestamp) - UNIX_TIMESTAMP(a.dtg_timestamp)) AS duration ...

Provided that you want duration in seconds.

This is well docummented in manual and definietly not bug.

Thanks for any help Graeme

Best regards