Unequaled timestamps if time zone changed
Ray
My project need to support mutiple time zone.But I noticed one
strange thing about H2 that on timezone changing the queried timestamp
was not equaled to inserted one.
Environment:H2 1.2.142,JRE 1.6.0_17,Windows 7 64bit
Here is the scenario:
1.If OS time zone is GMT +8:00(China)
One car accident happened at 2000/1/1 08:00:00 GMT +8:00
3.Now change OS time zone to GMT +6:00(User fly to India)
Expected timestamp queried from H2 supposed to be 2000/1/1 06:00:00
GMT +6:00.
But rather it was 2000/1/1 08:00:00 GMT +6:00.
By searching forum said that H2 will convert and save in GMT format
which should be 2000/1/1 00:00:00 GMT(But H2 console always shows
2000/1/1 08:00:00).
H2 seems doesn't store any timezone infomation.
Such behavior seems break the data consistency and create dependency
on envionment.
Currently I figure out two solutions but would like to hear how
people solve such problem:
Solution A:Enforce application using GMT by setting
System.setProperty("user.timezone","GMT");
So OS timezone won't affect database.The queried timestamp is
always equaled to inserted one.Only need to convert to user timezone
on presentation.
Solution B:Totally abandon TimeStamp type by using Long type instead
and time is represent as 20000101000000 and add another column for
timezone.
I am a little skeptical about solution A . If H2 using different JRE
which has different DST correction.Will the queried timestamp still be
the same as inserted one if some DST changed before timestamp ?
Solution B doesn't have such problem(time is always the perceived
one(presentation state)) .But the query across different timezone have
to seperate to different sql(Solution A don't have such problem).
Eg:Query all car accident within past 24 hours of all country
select * from accident where start>'20000102235959' and
end<'20000101000000' for China,
select * from accident where start>'20000102215959' and
end<'19991231210000' for India.
But in contrary if query don't have to consider timezone,it's more
efficient(Solution A need multiple sql now).
Eg:Query all car accident happened in june of all country
select * from accident where start>'20000601000000' and
end<'20000630235959'
Can anyone enlighten some lights on this issue? It's appreciate!
Rami Ojares
I suggested that h2 should store timestamp as a long value signifying
milliseconds since Unix Epoch.
Most compact representation of time and leaves no room for
misinterpretation.
For the time being I would suggest route B.
And do all your timezone calculations in java because it seems to get
the timezone issues right.
That's what I do.
If you want timezone aware functions into H2 you can easily create your
own functions that can
utilize java's inherent power to deal with timezones.
- Rami
Thomas Mueller
I think in most cases it's better to *not* convert the date/time to
UTC (Coordinated Universal Time) before storing, because of following
problem: Let's say you store a birthday in the database. After that,
you close the database, and move the database file to a different time
zone. Then you open the database and read the birthday. If the
date/time is converted to UTC, then the birthday might change, which
is completely unexpected. This is an extreme example (you could say
"birthdays don't contain the time"). But also for other cases it's
more common to store the date/time in the local time zone, so that
moving a database file will not change the value. Please note the
problem is only moving the database file.
I agree in some cases it would be better to convert the date to UTC,
but this you could also do in the application.
I think most databases ignore the time zone by default when using the
"normal" data types DATE, DATETIME, or TIMESTAMP (that means, the
value doesn't change if you move a database file to a different time
zone). If you think this isn't true please tell me which database, and
provide a reproducible test case. I'm very interested to make H2
compatible with other databases (I know some databases support
TIMESTAMP WITH TIME ZONE; this is not what I talk about - H2 simply
doesn't support storing the time zone - I'm talking about the normal
DATE, DATETIME and TIMESTAMP data types).
Regards,
Thomas
Ray
My project is using O-R mapping tool Datanucleus(JDO) to manipulate
database so we can easily change database.For client-server version
our database is Postgresql and in single user version we choose
H2.Conversion to H2 is smooth and H2 works great only that
Datanuclues default maps java Date to timestamp with time zone for
Postgresql,but mapped to timestamp for H2.Since data was generated
from all the worlds.We are worried that the lost timezone in H2 will
got problem on data replication some other day.
So has H2 got any plan to support timestamp with time zone?
Currently what we do is to change all TIMESTAMP data type to long for
both Postgresql and H2.So we might get better portability.
Regards,
Ray
On 10月8日, 上午5時45分, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:
Rami Ojares
DATE and TIME types have nothing to do with timezones.
Proof:
For Example in EU the DST boundaries are set to be 3am of last sunday of march and october.
So if you have a date on those specific dates then that date is partly in winter time and partly in summer time.
Thus it's timezone can not even in theory be determined.
This means that a date value say 2010-05-16 is the same date value regardless of where you are
on the surface of planet earth (or in the universe for that matter).
It always means the sixteenth of may.
If the place observes a calendar that does not have a year, month day concepts then
of course that value seems meaningless. So it is safe to say that the types
DATE and TIME are very much bound to the gregorian calendar system.
TIMESTAMP is a completely different type of animal.
TIMESTAMP refers to a point of time in the whole universe that can be shared by all possible calendar systems.
And if we stick with the newtonian worldview (and do not consider the consequences of Einstein's relativity)
that point of time happened at the same instant throughout the whole of universe.
There are 2 things that need definition:
1) origin (the point from which the measurements are started)
2) the unit of measurement (in java's case milliseconds)
The standard is to set the origin to 1.1.1970 00:00:00.000 UTC.
And then express any other time as the distance (in milliseconds)
from the origin.
But there is one more extra requirement.
A type in database needs a literal representation.
It could be just the number expressing the distance from origin (the amount of milliseconds)
but this would not be very handy when creating sql queries manually or when representing those
values directly to users.
Better would be to use ISO 8601 standard already defined for us eg.
[YYYY]-[MM]-[DD]T[hh]:[mm]TZ
2000-01-01T01:00:00Z = 1st of january 1am in utc timezone
This is exactly the same value as
2000-01-01T03:00:00+02:00 = 1st of january 3am in finnish wintertime timezone
The default literal should be in utc format.
But it could be also in the timezone of the server.
One could also have a setting where one could change the timezone in which
queries produce timestamp literals.
But I feel that all of these are just optional nice to have features.
What is truly needed is that all timestamps are points of time where both
the origin and unit of measurement are explicitly and statically defined so that
all the fiddling related to timezones and literal representation is done on the client side.
Now if someone wants to write a function say MONTH_NAME(timestamp) then this method
obviously requires 2 more parameters: MONTH_NAME(timestamp, timezone, locale)
There could be settings to set the default timezone and locale of the server so that
these 2 arguments could be made optional.
I did not quite understand Thomas' talk about Date changing when moving the database over
timezone boundaries. Maybe you meant that if the origin is set to first of january 1970 in some other
timezone than utc (say in Helsinki winter time) and then the value is read with the false idea that it
was saved in Moscow time then clearly the value would be misinterpreted.
But if everyone knows that the timepoints are always saved in utc time then the possiblity of misinterpretation would vanish.
TIMESTAMP with TIMEZONE could be represented the same way.
The timevalue stored in utc but then using the timezone (stored in that same cell in some format)
to determine the timezone of the literal representation.
I am a little bit against that kind of type because it is needless to bundle 2 things into the same type.
However there is nothing theoretically wrong with it.
The associated timezone could be easily stored in an other column of type varchar with notation
(+|-)HH:mm eg. +03:00
The person would get the timestamp and then get the timezone in which he wants to show the times to the user
and in which he wants to interpret the string that the user inputs.
- Rami
Dario Fassi
This is a very good and accurate answer to questions about timestamp and timestamp tz data type uses that occur repeatedly, as doubts and as portability queries from other dbms.
I propose that this explanation, a summary or similar be added as a special note to the documentation on the timestamp data type in the website.
Maybe this reduce the never ending threads over this subject.
regards,
Dario
El 08/10/10 07:49, Rami Ojares escribió:
--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Thomas Mueller
> DATE and TIME types have nothing to do with timezones.
Well, currently they do. See also ResultSet.getDate and getTime(...,
Calendar cal).
> 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.
Regards,
Thomas
Thomas Mueller
There is a feature request to support timestamp with timezone. I will
increase the priority.
Regards,
Thomas
Dario Fassi
El 12/10/10 14:55, Thomas Mueller escribi�:
>> DATE and TIME types have nothing to do with timezones.
> Well, currently they do. See also ResultSet.getDate and getTime(...,
> Calendar cal).
Rami say and I agree, that date and time fields stored in a database must be "static" values that should never be adjusted in regards of client's TZ.
Only timestamps can be used for calendar conversions and arithmetic.
>> 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.
Thomas , again I understand that Rami say exactly the same thing.
He argues that timestamps by default default should also be static, and if you need TZ conversions (and you don't have timestamp with TZ) you can add an extra field to store origin TZ of this timestamps or convert always to GMT, making timestamp with time
zone data type a dispensable comfort feature.
regards,
Dario.
Rami
DATE and TIME types have nothing to do with timezones.Well, currently they do. See also ResultSet.getDate and getTime(..., Calendar cal).
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 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
Thomas Mueller
Please try to keep your responses short.
The documentation at
http://h2database.com/html/tutorial.html#date_time is incorrect, I
will change it to :
"If the time zone is not set, the value is parsed using the current
time zone setting of the system. Date and time information is stored
in H2 database files without time zone information. If the database is
opened using another system time zone, the date and time will be the
same.
That means if you store the value '2000-01-01 12:00:00' in one time
zone, then close the database and open the database again in a
different time zone, you will also get '2000-01-01 12:00:00'. Please
note that changing the time zone after the H2 driver is loaded is not
supported."
Example:
TimeZone.setDefault(TimeZone.getTimeZone("PST"));
// TimeZone.setDefault(TimeZone.getTimeZone("CET"));
Class.forName("org.h2.Driver");
String dir = "~/data/h2database/h2/data";
String url = "jdbc:h2:" + dir + "/test";
Connection conn = DriverManager.getConnection(url);
Statement stat = conn.createStatement();
stat.execute("create table if not exists test(x timestamp)");
stat.execute("insert into test values('2000-01-01 12:00:00')");
conn.close();
conn = DriverManager.getConnection(url);
stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select * from test");
while(rs.next()) {
System.out.println(rs.getString(1));
}
Regards,
Thomas
Rami Ojares
I promise to try, but I find clear and explicit communication in higher
value
than the the sparsity of expression.
Now the documentation seems to me clear and concise.
It expresses everything in a pragmatic way using a concrete example.
Well done.
Still, I would propose a different implementation for the future
but I quess you knew that already ;-)
- Rami