OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Incorrect datetime value, Round 2

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
couldabin
General User
General User


Joined: 26 Jul 2012
Posts: 8

PostPosted: Wed Aug 01, 2012 7:46 am    Post subject: Incorrect datetime value, Round 2 Reply with quote

I posted a question last week about OO Base displaying Foxpro DateTime fields incorrectly -- it calculates the wrong time. Not a response. Is this a known problem? Has it been fixed?
Back to top
View user's profile Send private message
mgroenescheij
Super User
Super User


Joined: 20 Apr 2011
Posts: 870
Location: Australia

PostPosted: Wed Aug 01, 2012 8:17 pm    Post subject: Reply with quote

Hi,

Not much information you gave us.
One thing is how is the Date/Time stored in Foxpro?
Next question is how is it displayed in Foxpro? This depends on your Output Format.
To get the display the same as in Foxpro you need to set the column format the same.
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
couldabin
General User
General User


Joined: 26 Jul 2012
Posts: 8

PostPosted: Thu Aug 02, 2012 5:46 am    Post subject: Reply with quote

Foxpro stores DateTime values as Julian dates, consisting of two 32-bit integers -- the first is the number of days that have elapsed since Jan 1, 4713 BC, and the second is the time, which is the number of milliseconds since midnight. The database in question, which was created by a government regulatory agency, contains two DateTime fields -- the dates/times for the beginning and ending of a transaction. When the table is opened in OO, it displays the first record as having a start time of 6:59 am, and an ending time of 10:00 am. If the same table is opened using a Foxpro viewer (I used a utility called Altap Salamander) it shows the times as 3:59 am and 5 am. If you examine the raw datafile, the 32-bit time values are 0xDBB9FE and 0x112A880. When converted to milliseconds since midnight, those represent 3:59:59.98 and 5:00:00.00. So we have two issues here -- why does OO think the starting time is ~7:00 am, rather than ~4:00 am, and even more puzzling, why does it think the elapsed time is 3 hours instead of 1? I was able to test the values one additional way -- it turns out the government agency that supplied the Foxpro database also can provide the data in Excel spreadsheet format. In Excel, the times are 4:00 am and 5:00 am. Also puzzling -- OO displays the wrong elapsed time on all the records, but it isn;t always by 3 hours. It occurs to me there may be timezone issues at play here, which could explain the wrong hour, but I would think the interval would always be right. I can provide screenshots or other details if you like.
Back to top
View user's profile Send private message
couldabin
General User
General User


Joined: 26 Jul 2012
Posts: 8

PostPosted: Thu Aug 02, 2012 5:55 am    Post subject: Reply with quote

Just a quick follow -- I meant to say above that 0xDBB9FE would translate into 3:59:59.998, not 3:59:59.98. Thanks.
Back to top
View user's profile Send private message
Luxo
General User
General User


Joined: 06 Nov 2008
Posts: 8
Location: Luxemburg/Europe

PostPosted: Mon Aug 06, 2012 2:46 pm    Post subject: Reply with quote

I'm going to assume you're using openoffice with the embedded hsql db engine (when you have base open with a db, take a look at the bottom status bar).

the documentation for the embedded db engine is here: http://www.hsqldb.org/doc/1.8/guide/

the supported sql standards are: 'HSQLDB 1.8.0 supports the dialect of SQL defined by SQL standards 92, 99 and 2003.' source: http://www.hsqldb.org/doc/1.8/guide/ch02.html#N102B3

the supported data type for : time as Java type java.sql.Time. source: http://www.hsqldb.org/doc/1.8/guide/ch09.html#datatypes-section

of particular interest is what you can actually put into a DATE or TIME field:
A DATE literal starts and ends with ' (singlequote), the format is yyyy-mm-dd (see java.sql.Date.
A TIME liteal starts and ends with ' (singlequote), the format is hh:mm:ss (see java.sql.Time).
A TIMESTAMP or DATETIME literal starts and ends with ' (singlequote), the format is yyyy-mm-dd hh:mm:ss.SSSSSSSSS (see java.sql.Timestamp).
source: http://www.hsqldb.org/doc/1.8/guide/ch09.html#ftn.posthyper (bottom of chapter 9)

my uninformed guess (i'm just a basic user) is base is unable to make sense of the contens of those fields. ymmv.

as mgroenescheij has mentioned in his signature:'If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).'
Back to top
View user's profile Send private message
couldabin
General User
General User


Joined: 26 Jul 2012
Posts: 8

PostPosted: Tue Aug 07, 2012 7:29 am    Post subject: Reply with quote

No, the DateTime field in a Foxpro database is a binary value, not text. See here:

http://msdn.microsoft.com/en-US/library/ba6a6xks%28v=vs.80%29

The issue isn't that OO doesn't understand it; it's that OO gets the math wrong. Judging from the minimal response I've received to this, I'm inclined to think that doesn't bother OO users. For my part, I don't think DateTime values derived from a Foxpro database by OO can be trusted at all.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Aug 07, 2012 10:29 am    Post subject: Reply with quote

How could we bother without ever using Foxpro?
ODBC is a standard to avoid all those problems with different storage systems. Create an ODBC data source, connect any client (Base or whatever) to the ODBC data source and the underlying ODBC driver should expose your Foxpro dates just like the dates of any other ODBC database.
Since Foxpro is a Microsoft product, I would always recommend to use nothing else but MS software with this product.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
couldabin
General User
General User


Joined: 26 Jul 2012
Posts: 8

PostPosted: Tue Aug 07, 2012 10:49 am    Post subject: Reply with quote

I'm not sure I follow. I didn't post my question here in order to get advice on non-OO solutions. I posted my question here because I'm trying to use OO and running into problems.

My primary database is MS Access 2003. Neither Access 2003 nor Access 2007 comes with out-of-the-box ODBC drivers for Foxpro tables. A few years ago, MS's advice was to download the drivers. Now they appear to have been removed from MS's support site. So I am stuck no being able to use MS Access to connect to the table.

That's the only reason I turned to OO. The reason I posted here -- has it been well over a week ago? -- was to find out if this bug in OO has ever been fixed. So far, nobody has offered information on that. And again, it strikes me as remarkable that such a fundamental issue -- whether the information displayed reflects the underlying data -- seems to be of so little concern. I have to wonder if OO users are even aware of the problem.
Back to top
View user's profile Send private message
couldabin
General User
General User


Joined: 26 Jul 2012
Posts: 8

PostPosted: Tue Aug 07, 2012 10:55 am    Post subject: Reply with quote

If I may add one more observation: I suspect you don't really mean that only MS software should be used with MS products. Really? No .xls support, no .doc support? I'm quite sure a number of OO users expect to be able to read/write those file formats.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Aug 07, 2012 12:31 pm    Post subject: Reply with quote

couldabin wrote:
If I may add one more observation: I suspect you don't really mean that only MS software should be used with MS products. Really? No .xls support, no .doc support? I'm quite sure a number of OO users expect to be able to read/write those file formats.

Shure. If you work with doc/xls day by day you need MS Office. That's for sure and proven by millions of users of this office suite which is not a replacement for any other software just because it can import some proprietary file formats fairly well.

You are using a proprietary database format and MS decided to cancel the driver support. This is how proprietary software works. I would start Foxpro and export everything to dBase, csv, SQL scripts or whatever Foxpro has to offer.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Luxo
General User
General User


Joined: 06 Nov 2008
Posts: 8
Location: Luxemburg/Europe

PostPosted: Tue Aug 07, 2012 1:23 pm    Post subject: Reply with quote

couldabin wrote:

<snip>
it turns out the government agency that supplied the Foxpro database also can provide the data in Excel spreadsheet format.
<snip>


open the excel file in Calc, select all the cells you need (don't forget the headers), copy/paste (or drag'n'drop) in the table view of base, a wizard should open to help import the data.

As it is, Oo expects a properly formed 'string' (in 24 hour format) in a TIME field (as per the sql standard), no binary data
Back to top
View user's profile Send private message
couldabin
General User
General User


Joined: 26 Jul 2012
Posts: 8

PostPosted: Tue Aug 07, 2012 2:00 pm    Post subject: Reply with quote

All right, I'll leave it at that. I don't have Foxpro but wrote a utility to convert the data. I'll not make the mistake of thiking OO can be relied upon.



Villeroy wrote:
couldabin wrote:
If I may add one more observation: I suspect you don't really mean that only MS software should be used with MS products. Really? No .xls support, no .doc support? I'm quite sure a number of OO users expect to be able to read/write those file formats.

Shure. If you work with doc/xls day by day you need MS Office. That's for sure and proven by millions of users of this office suite which is not a replacement for any other software just because it can import some proprietary file formats fairly well.

You are using a proprietary database format and MS decided to cancel the driver support. This is how proprietary software works. I would start Foxpro and export everything to dBase, csv, SQL scripts or whatever Foxpro has to offer.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group