Aparent incorrect times from other db

[johnb]

Hi - hope you can help.

We run Firebird (still on 1.5) and have been using MSQuery via ODBC to get information into excel.

We are trialling OO at present and have come up against an issue that I haven't been able to resolve.

There is a table that, amongst other fields, has two fields of dates - primarydatetime and secondarydatetime. secondarydatetime is either null or greater than primarydatetime.

Using IB Expert to view the table, all the datetime information is correct.

Using OO Base, the information is not correct. (Refer examples below)

From IB Expert:
ALARMHISTSYSID PRIMARYDATETIME SECONDARYDATETIME
46096 25/01/2008 23:14:32 25/01/2008 23:24:35
46097 25/01/2008 23:19:43 25/01/2008 23:21:30
46098 25/01/2008 23:25:20
46099 25/01/2008 23:32:41
46100 25/01/2008 23:50:29 25/01/2008 23:50:40
46101 25/01/2008 23:51:01 25/01/2008 23:51:21
46102 25/01/2008 23:51:28 26/01/2008 00:05:05
46103 25/01/2008 23:56:50 26/01/2008 00:06:22
46104 25/01/2008 23:59:23 26/01/2008 00:04:45
46105 26/01/2008 00:03:07
46106 26/01/2008 00:13:38
46107 26/01/2008 00:15:18 26/01/2008 00:45:55
46108 26/01/2008 00:15:28 26/01/2008 00:16:02
46109 26/01/2008 00:22:04 26/01/2008 00:35:06
46110 26/01/2008 00:22:22
46111 26/01/2008 00:24:22 26/01/2008 00:28:42
46112 26/01/2008 00:26:07 26/01/2008 00:31:00
46113 26/01/2008 00:28:55
46114 26/01/2008 00:29:50 26/01/2008 00:33:51
46115 26/01/2008 00:38:33 26/01/2008 00:53:55
46116 26/01/2008 00:39:46 26/01/2008 01:08:04
46117 26/01/2008 00:40:43 26/01/2008 00:46:26
46118 26/01/2008 00:43:43
46119 26/01/2008 00:43:48 26/01/2008 00:43:55


From OO Base (using ODBC from Firebird):
ALARMHISTSYSID PRIMARYDATETIME SECONDARYDATETIME
46096 25/01/08 23:23 25/01/08 23:24
46097 25/01/08 23:21 25/01/08 23:21
46098 25/01/08 23:34
46099 25/01/08 23:33
46100 26/01/08 00:00 25/01/08 23:50
46101 25/01/08 23:56 25/01/08 23:51
46102 25/01/08 23:51 26/01/08 00:05
46103 26/01/08 00:05 26/01/08 00:06
46104 26/01/08 00:09 26/01/08 00:04
46105 26/01/08 00:07
46106 26/01/08 00:16
46107 26/01/08 00:24 26/01/08 00:45
46108 26/01/08 00:18 26/01/08 00:16
46109 26/01/08 00:25 26/01/08 00:35
46110 26/01/08 00:24
46111 26/01/08 00:33 26/01/08 00:28
46112 26/01/08 00:27 26/01/08 00:31
46113 26/01/08 00:33
46114 26/01/08 00:30 26/01/08 00:33
46115 26/01/08 00:48 26/01/08 00:53
46116 26/01/08 00:49 26/01/08 01:08
46117 26/01/08 00:43 26/01/08 00:46
46118 26/01/08 00:45
46119 26/01/08 00:47 26/01/08 00:43

One other thing.
The date/time is coming into Base as DD/MM/YY HH:MM however I need it as DD/MM/YYYY HH:MM:SS - how do I change the default format of TIMESTAMP as I can't edit the field format in Base since it is connected using ODBC.


Some assistance would be greatly appreciated,
Thanks

[r4zoli]

It's may be based on your ODBC driver features see: The Complete Guide to ODBC Escape Sequences in InterBase : http://dn.codegear.com/article/37452
IB expert connect directly to FB 1.5 and OOo Base through ODBC driver, and OOo use ODBC datetime.

[sanmar]

Ok.

The time format is solved. We are still having problems with the time data being incorrect.

Using another database (DB_A), generated by the same system with similar data, the transfer works OK without a problem for every record. For some reason, the database file that is having problems(DB_B) seems to only have issues with being pulled via Firebird ODBC(Ver 1.5&2.0a) Driver into OpenOffice Base. This problem does not happen with the same data set and Excel via ODBC.

As John mentioned, there are two fields with the data type of TIMESTAMP..

primarydatetime is 2-10 minutes ahead of the same record in IBE (SQL Browser) and also the same data pulled via ODBC to Excel. I can't find any real reason as to what is creating the problem eg: incorrectly matching with another record for the primarydatetime field.

secondarydatetime seems to be providing the correct time.

In the order of fields, primarydatetime comes directly before secondarydatetime.

Any help would be appreciated!


{edit}

Just to add:

If a record for field primarydatetime is changed directly in IBExpert, repulling the data over to ooBase gives the correct information for that one updated record. However, the other records still do not match up in IBExpert and ooBase.