Wrong date in Base on different PCs

[Epitaph]

Hi,

I have the following issue with Base:
I am using a database on two computers. The database contains several DATE fields. The problem is that the two computers display different date, although the records are the same. One of the computers is one day behind.

For example:

A have a record : "John", "15/09/2011".
On the first computer the record is displayed as "John", "15/09/2011" ; on the second computer I get ""John", "14/09/2011" . This affects date-related queries as well and is persistent on all databases i have. The computers are with Libre Office 3.3.2, one with XP, the other with WIN 7.

What might be the cause of this ?

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases your chances of a reply. (TheGurkha, Moderator)

[r4zoli]

Two computer in same locale settings?
With the same time zone?
HSQLdb 1.8 built-in OOo Base stores time with only one timezone - UTC 0 Your local time zone settings (your OS settings) are used to convert this value when it is used in a recordset.

[rudolfo]

How to you bring the database from one computer to the other?
Is it simply the .odb file that you copy? And the .odb file has the default imbedded HSQLDB database?
Are the computers both in the same timezone? Or to put it in another way all other date related functions (especially the last modified time of the file that makes up your database) are the same on both computers?
If it is really the embedded database that you are using, removing private data from the database and uploading the .odb file here for others to inspect the behaviour is a good idea.

[Villeroy]

Do you see the same (right or wrong) dates in the table, queries and forms? There may be a bug related to a certain type of form controls which would dislpay a wrong date for a correctly stored value.

[Epitaph]

Two computer in same locale settings?

- Yes (double checked)

With the same time zone?

- Yes

How to you bring the database from one computer to the other?

it is one odb file on one of the computers. The other computer has this odb file registered as database.
On the other hand, i am running another database on the same machines in server mode (using the instructions from http://www.oooforum.org/forum/viewtopic ... 523#396523 ). There is no problem with this database - the dates are OK.

Do you see the same (right or wrong) dates in the table, queries and forms?

Yes, the dates in the queries are wrong as well.

[rudolfo]

On the other hand, i am running another database on the same machines in server mode [...]. There is no problem with this database - the dates are OK.

I am not too deep into the Multimode database but what I got is that in contrary to the embedded HSQLDB the multimode uses JDBC to access the database. So we have basically 3 parts that are involved in database handling:
1) The HSQLDB database backend
2) the database driver: native sdbc for embedded and common JDBC driver for Multimode or server mode
3) The Base client frontend with table grid, query grids, form controls etc.

I am not sure what you really proof with your point that the server mode has not this date problem. Most database drivers respect the client's timezone settings from which they are started. But if the JDBC driver doesn't your example would just mean that both client computers show the same (unconverted server) time of the database backend table. Try to change the Locale/timezone settings for the JDBC driver and see if the two clients show the times according to the choosen timezone.
If you see no change the JDBC driver doesn't respect timezones and your example will only display raw data coming from the same origin on the database server. And it is no wonder that they display the same (raw) data in the same way.

Or your examples show that the JDBC driver is more stable then the builtin sdbc driver.

If all this sounds complicated it is because we are talking about debugging. And we have to do this with a lot of assumptions. So in general if you upload the file or the relevant parts of it (How to upload documents) it will be easier for all of us.

[APD]

I have exactly the same problem only on two Macs both running OO 3.3.0 on Mac OS X 10.7.2

I have been helping a friend create a Base database that has the default imbedded HSQLDB database. When I send him the .odb file and he opens it, the dates in the table, queries and forms are one day earlier on his computer. He is in the Central time zone, and I am in Eastern, one hour apart, but the dates are entered as just a mm/dd/yy format in a Field of Field Type DATE . If I export them to Calc and display the dates as numbers, his number is one less than mine.... hence one day earlier.

To be honest, I don't see why this would be a time zone issue, but then I don't know how Base is storing these Fields of type DATE.

Interestingly, I had him reload the dates into the table on his side and they showed up correctly. When he sent me the .odb back, the dates remained constant (that is the same correct date both here and there).

This issue makes be very nervous about what is going on in the database. Any ideas as to what could cause this?

[path32]

hi,

your using a macro basic program or SQL statement ( Tools> SQL... ) regarding in Timestamp?
if your using a macro basic code, your timestamp will follow the time of your computer client not in the server side so better to use SQL Statement

You can do use this

Code: Select all

ALTER TABLE "YourTable" ALTER COLUMN "DateField" DEFAULT CURRENT_DATE;
ALTER TABLE "YourTable" ALTER COLUMN "TimeField" DEFAULT CURRENT_TIME;
ALTER TABLE "YourTable" ALTER COLUMN "DateTimeField" DEFAULT CURRENT_TIMESTAMP;