I feel that Dario already answered this partially but I still feel
obligated to answer this myself.
DATE and TIME types have nothing to do with timezones.
Well, currently they do. See also ResultSet.getDate and getTime(...,
Calendar cal).
I think you are mixing here the database management system (server) and
the client program
that accesses the DBMS. Let's say we have a c or php program accessing H2.
They do not have java's getDate() method or Calendar class.
So a type has to be defined more generally and should not be coupled
with the capabilities of one particular client.
When accessing the database with sql interface then we see that a type
must have a literal form (that is a textual form or a String form in
java terminology).
But we do not see there getDate method neither a Calendar class.
Further Date is constructed in java with milliseconds since Unix Epoch
int UTC.
And if you look at classes java.util.Date, java.sql.Date and
java.sql.Time you'll see that
most of their methods are deprecated meaning that they were badly designed.
Reason being of course that they did not take into account properly the
concept of timezones
and different calendar systems. For this java (read IBM) designed the
Calendar, Locale and Timezone
classes as part of internationalizing java.
TIMESTAMP refers to a point of time in the whole universe that can be shared
by all possible calendar systems.
For certain use cases, yes. For other use cases, no. Let's say you
want to store a log file in the database. Of course you *could*
convert the timestamps to UTC. In this case you would get different
results depending on the timezone where you open the database file.
But in many cases people prefer to *not* convert the timestamp. If you
send a log file (that includes a timestamp column) to another country
then the timestamp in the log file doesn't magically change. You don't
*want* it to change. If it changes when you send the database file
then that's unexpected and therefore confusing. Maybe not for you, but
for others.
That's probably why all databases I know don't convert timestamps when
you send the database file to another time zone.
I'm not saying a new data type "TIMESTAMP WITH TIMEZONE" is not
needed. It would be a nice feature. But it's currently not
implemented.
I think we are talking past each other because the concept of TIMESTAMP
I am talking about is an abstract thing.
I am talking about our concept of time and it's representability as a
contiguous set of real number values (or integers if we define a
precision eg. milliseconds or nanoseconds).
For you timestamp means different situations in the world where someone
writes something in the log file that for him represents a point of time.
Then a problem arises (in your system) when that value is given to
someone else who does not know how to interpret it correctly.
And you propose as a solution that we should add some extra information
to that value so everyone could interpret it correctly.
Ok, fine.
But my proposal is that we should define a time point (timestamp if you
will) in just one way.
And then tell everybody that that's the way it always is.
Then we would not need additional metadata to tell how to correctly
interpret the value.
Everybody would always know how to do it.
And it would always be done the same way.
That's what we are doing currently with varchar values.
The encoding to a list of bits and bytes is declared to be always in UTF-8.
This is another case where there has been constant confusion and an open
question whether there should only exist a single encoding for all
varchar values
or whether each and every varchar should be allowed to be encoded in any
given encoding
and then provide additional metadata to user about the encoding that
provides the key
to interpret a list of bits to a varchar correctly.
I am in favor of simplicity because the complexity does not bring anything.
(Note: Even if dbms always uses a single encoding it is still free to store
the varchar value in the backend in any way it chooses to. Eg.
compressed etc.)
Attaching timezone information to the timestamp can only achieve the
following (that could be useful in some situations)
- When reading/writing the timestamp in literal form one needs 2 things
a: timezone
b: locale
So the attached timezone could provide a default for the timezone value.
But then the question arises should we rather provide a type TIMESTAMP
WITH TIMEZONE AND LOCALE
That's when we start to see that types should not be created in such an
ad hoc matter.
Especially here when you can nicely have a literal form that does not
need those two pieces of metadata
and still be human readable. Here is an example of inputting a timestamp
in your chosen timezone in a rather human readable way.
And this representation leaves no room for wrong interpretation or loose
ends:
INSERT INTO TBL(CREATION_TIME) VALUES('2010-10-10T10:10:10+03');
Here I am using conveniently my own timezone and still this string
literal can be mapped unequivocally to milliseconds from Unix Epoch int UTC.
- Rami
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
