Just final results:
As Drew guessed different time zones appear to be the problem. We are
working in Brazil (with two timezones) plus UK.
However due to some windows setting I know nothing about when the other
person sets to the same time zone (with same setting for summer time
adjustments) the other laptop is still an hour ahead of mine (apparently
my co worker always has to manually adjust the hour)- so synchronising
is getting confusing.
Using DATETIME type doesn't help (hours still change).
The solution - use Calc where dates / times in the spreadsheets are not
changing. As we are working with only a few tables and a few thousand
lines the simplest way is to drag and drop Base data then pass Calc
files between us. As Base works so nicely with Calc this is not really a
problem. But is obviously not that productive.
There must be better way (which doesn't need hours of coding SQL / JAVA
/ Basic)?
Best wishes,
Darren
p.s. although maybe not - just seen this which also shows some
limitations in the handling of date / times:
http://www.mail-archive.com/[email protected]/msg02873.html
Darren Norris wrote:
Hi Drew,
Many thanks for the details from the HSQLdb source file ( I had a look
at http://hsqldb.org/web/hsqlDocsFrame.html before sending but wasn't
able to find the info you sent!! )
- and many thanks for your time and the suggestions - always helps to
put me in the right direction.
DJ > The data doesn't change - the representation does - see below.
he he
Would definitely be something good to have in Base (times / dates /
timestamps whose representation doesn't change that is......where's a
"Next Features" when you need one!).
I think DATETIME / TIMESTAMPS (need to double check my understanding
of what HSQL sees and how it uses) might be the simplest solution. I'm
not sure why but one of the other tables had this data type and the
representation stayed the same (at least on this occasion). I asked
and it looks like we are currently all in the same time zone - I'm
doing some tests to see ...but time zones we (and the laptops) are in
do / will change.
DJ > Is it enough to know that the times each of you are seeing are
relative to your locale?
Yes I could work with this. However we are doing a very small bit of
work - and it would probably be simplest ( at least for me ) to input
dates as text (then "kludge" this to a date / time) compared with
coding to add an hour here / subtract there.
We all have paper forms with data in, which all needs to go into Base
- we all really like Base forms and this is helping reduce errors and
make sure we all do the same. The idea is the .odb file gets passed
around and data appended / updated in turn. Yes there are many
(better) ways of doing this - however at this stage passing copies (
email / cd / usb ) of the .odb is quickest / easiest / simplest (at
least for us - only 3 of us and the work is only for a few months).
Abraços,
Darren
Drew Jensen wrote:
On 4/18/2009 10:37 AM, Darren Norris wrote:
I have a little database (attached) and the dates in one of the tables
(I haven't checked others) change when the file is copied between
different laptops.
The data doesn't change - the representation does - see below.
........
........
Darren
Hi Darren,
Most likely it is Time Zone differences with the user local.
HSQLdb does not have a timezone component (not that most DBMS' do) to
date or time types.
Actually to be more precise I change that to - HSQLdb 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. (such as displayed in a
form or on a report)
To see this in action you simply need to enter a date value - exit
OO.o - change your timezone in your OS - restart OO.o, open the file
and view that date/time again.
Here is the comment about this from the HSQLdb source file for
timestamps:
57 * HSQLDB uses the client and server's default timezone for all
DATETIME
58 * operations. It stores the DATETIME values in .log and .script
files using
59 * the default locale of the server. The same values are stored as
binary
60 * UTC timestamps in .data files. If the database
is trasported from one
61 * timezone to another, then the DATETIME values in cached tables
will be
62 * handled as UTC but those in other tables will be treated as
local. So
63 * a timestamp representing 12 noon stored in Tokyo timezone will
be treated
64 * as 9 pm in London when stored in a cached table but the same
value stored
65 * in a memory table will be treated as 12 noon.
(*NOTE* - for Base embedded files the tables are 'cached' tables, as
referred to above )
So the top question is, I suppose, "How do you need to handle that,
for your particular application?
What about these:
Is it enough to know that the times each of you are seeing are
relative to your locale?
You don't input date/time data for a different timezone the your OS
is configured for, do you?
Question is - is there any way to ensure for whatever reason
(different
OS, timezone, base version , openoffice settings etc) that a date or a
time remains exactly as entered (without inputting as text)? If not
where should I start looking to make sure dates remain the same (there
are 3 different people all with vista laptops and the .odb file
will be
emailed, copied via cd / usb between us).
Should be, shouldn't there? (thinking...)
Kind of OT - this is true for the version of HSQLdb shipped with
OO.o, version 1.8.x. The alpha release of HSQLdb 1.9 adds two new
data types to address this particular situation with "TIME WITH TIME
ZONE and TIMESTAMP WITH TIME ZONE". Whether Base will be supporting
the newer HSQLdb engine(s) [2.0 is also in the works] is something I
have not seen mentioned before.
Best wishes,
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
