MySQL Date/Time Problem

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.
I am using MySQL Version 4.1.0-alpha-max-nt because this was the only
version I could get the query to execute on. The query works correctly in
programs like access, but is wrong it crystal reports and MySQL control
Center.

More: It would appear the time is being subtracted as decimal. If it subs
time that rolls over a minute it says there is 100 seconds in a minute and
the difference is out by 40 seconds.


Query
--------
SELECT a.loc_id, a.alarm, b.alarm, b.dtg_timestamp As B, a.dtg_timestamp AS
start_date, ((b.dtg_timestamp-a.dtg_timestamp)) AS duration, a.state,b.state
FROM tbl_citectalarms AS a, tbl_citectalarms AS b
where a.alarm=b.alarm and b.dtg_timestamp = (select min(dtg_timestamp) from
tbl_citectalarms where alarm = a.alarm and dtg_timestamp >
a.dtg_timestamp) and a.state="Active"
ORDER BY a.dtg_timestamp,a.alarm;

Table Structure
----------------------
CREATE TABLE `tbl_citectalarms` (
`UniqueID` int(20) NOT NULL auto_increment,
`loc_id` varchar(10) character set latin1 NOT NULL default '',
`circ_name` varchar(50) character set latin1 NOT NULL default '',
`dtg_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
`alarm` varchar(50) character set latin1 NOT NULL default '',
`state` varchar(50) character set latin1 NOT NULL default '',
PRIMARY KEY (`UniqueID`),
KEY `CitectAlarmTimeStamp` (`dtg_timestamp`),
KEY `CitectAlarmAlarm` (`alarm`),
KEY `AlarmCircuitAndLocation` (`loc_id`,`circ_name`)
) TYPE=MyISAM;

Table Data
---------------
+----------+---------+---------------+---------------------+ ---------+------
----+
| UniqueID | loc_id | circ_name | dtg_timestamp | alarm | state
|
+----------+---------+---------------+---------------------+ ---------+------
----+
| 1 | brm-302 | brm-Broome Rd | 2099-01-05 10:12:45 | VReg |
Active |
| 2 | brm-302 | brm-Broome Rd | 2099-01-05 10:13:06 | VReg |
InActive |
| 3 | brm-304 | brm-Incomer 1 | 2099-01-04 12:23:32 | VReg |
Active |
| 4 | brm-304 | brm-Incomer 1 | 2099-01-04 12:24:02 | VReg |
InActive |
| 5 | brm-304 | brm-Incomer 1 | 2099-01-04 15:14:32 | VReg |
Active |
| 6 | brm-304 | brm-incomer 1 | 2099-01-04 15:15:03 | VReg |
InActive |
| 7 | brm-304 | brm-Incomer 1 | 2099-01-04 17:21:19 | VReg |
Active |
| 8 | brm-304 | brm-Incomer 1 | 2099-01-04 17:21:24 | VReg |
InActive |
| 10 | brm-302 | brm-Broome Rd | 2099-01-04 09:25:15 | FreqReg |
Active |
| 11 | brm-302 | brm-Broome Rd | 2099-01-04 09:25:50 | FreqReg |
InActive |
| 12 | brm-304 | brm-Incomer 1 | 2099-01-04 15:12:25 | THDReg |
Active |
| 13 | brm-304 | brm-Incomer 1 | 2099-01-04 15:13:40 | THDReg |
InActive |
| 14 | crn-10 | | 2099-01-04 10:12:00 | test |
Active |
| 15 | crn-10 | | 2099-01-04 10:15:23 | test |
InActive |
+----------+---------+---------------+---------------------+ ---------+------
----+

Query Results
---------------------
+---------+---------+---------+---------------------+------- --------------+-
---------+--------+----------+
| loc_id | alarm | alarm | B | start_date |
duration | state | state |
+---------+---------+---------+---------------------+------- --------------+-
---------+--------+----------+
| brm-302 | FreqReg | FreqReg | 2099-01-04 09:25:50 | 2099-01-04 09:25:15 |
35 | Active | InActive |
| crn-10 | test | test | 2099-01-04 10:15:23 | 2099-01-04 10:12:00 |
323 | Active | InActive |
| brm-304 | VReg | VReg | 2099-01-04 12:24:02 | 2099-01-04 12:23:32 |
70 | Active | InActive |
| brm-304 | THDReg | THDReg | 2099-01-04 15:13:40 | 2099-01-04 15:12:25 |
115 | Active | InActive |
| brm-304 | VReg | VReg | 2099-01-04 15:15:03 | 2099-01-04 15:14:32 |
71 | Active | InActive |
| brm-304 | VReg | VReg | 2099-01-04 17:21:24 | 2099-01-04 17:21:19 |
5 | Active | InActive |
| brm-302 | VReg | VReg | 2099-01-05 10:13:06 | 2099-01-05 10:12:45 |
61 | Active | InActive |
+---------+---------+---------+---------------------+------- --------------+-
---------+--------+----------+

Thanks for any help
Graeme

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: MySQL Date/Time Problem

Graeme Ferguson writes:
> 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.
> I am using MySQL Version 4.1.0-alpha-max-nt because this was the only
> version I could get the query to execute on. The query works correctly in
> programs like access, but is wrong it crystal reports and MySQL control
> Center.
>
>
> Thanks for any help
> Graeme
>

Was Access connected to MySQL when it worked ??

Can you try to make it work via mysql command line ??

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: MySQL Date/Time Problem

Access was connected to MySql via MyODBC at the time. I have just tried the
query in the mysql command line and it returned the same result set, 40
seconds out on each minute rollover.
Cheers
Graeme

-----Original Message-----
From: Sinisa Milivojevic [mailto:sinisa@mysql.com]
Sent: Saturday, 2 August 2003 8:50 PM
To: Graeme Ferguson
Cc: bugs@lists.mysql.com
Subject: Re: MySQL Date/Time Problem


Graeme Ferguson writes:
> 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.
> I am using MySQL Version 4.1.0-alpha-max-nt because this was the only
> version I could get the query to execute on. The query works correctly in
> programs like access, but is wrong it crystal reports and MySQL control
> Center.
>
>
> Thanks for any help
> Graeme
>

Was Access connected to MySQL when it worked ??

Can you try to make it work via mysql command line ??

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: MySQL Date/Time Problem

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

--
Are you MySQL certified? -> http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: MySQL Date/Time Problem

Thanks for that, the only reason I thought it was a bug was because it
worked throught the ODBC engine, which makes sense now. I looked through the
manual, but like all manuals you need to know where to look to find the
info.
Cheers
Graeme

-----Original Message-----
From: Alexander Keremidarski [mailto:salle@mysql.com]
Sent: Sunday, 3 August 2003 4:35 PM
To: Graeme Ferguson
Cc: 'bugs@lists.mysql.com'
Subject: Re: MySQL Date/Time Problem


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

--
Are you MySQL certified? -> http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski

/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org