I have a large integer number (milliseconds since 1970) which is 13 digits. So I tried to store it in a table as a bigint type. Storing works fine. When I try to retrieve it, I don't get the records I expect. If I try to order my records by the bigint column I get completely unexpected orderings.
My table definition is: CREATE TABLE `FREEWAYDATA` ( `CLIENTCLOCK` bigint default NULL, `CLOCKOFFSET` decimal(10,0) default NULL, `CORRECTEDCLOCK` bigint NOT NULL default '0', `DETECTORDATA` varchar(255) default NULL, `STATIONID` decimal(10,0) NOT NULL default '0', `THEDAY` date default NULL, PRIMARY KEY (`CORRECTEDCLOCK`,`STATIONID`) ) TYPE=MyISAM;
The problem field is correctedclock.
I insert records with a JDBC preparedStatement:
"insert into FREEWAYSERVER.FREEWAYDATA (STATIONID, CORRECTEDCLOCK, CLIENTCLOCK, DETECTORDATA, clockOffset) values (?, ?, ?, ?, ?)";
where the correctedclock is set using setLong();
Inserts appear to work fine.
I retrieve records using selects of the form:
SELECT CORRECTEDCLOCK FROM FREEWAYSERVER.FREEWAYDATA where correctedclock >= ? and correctedclock < ? order by correctedClock ASC
where the start and end times are large integer values.
What I get back varies by which values I use but is generally wrong. If I do "select correctedclock from freewaydata" I can see the values I want but they don't get pulled if I use the where clause.
If I do "select correctedclock from freewaydata order by correctedclock asc" I get some order which is not numeric nor string ordering nor the insert order.
If I change correctedclock to be an integer field, everything works as expected. While this is a possible workaround it messes up my data accesses.
Is there something special about bigint that prevents range based selects from working they way I expect them to?
This is using mysql 4.0.16 on Mac OS X 10.3 server.
Randall Cayford Institute of Transportation Studies UC Berkeley
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
