Hi,
because I didn't solved the problems of my previous posts I've tried to create another demo. Basicly I have the following situation. I get an xml file (via a servlet) which contains a comma-separated list of values (for example energy values) and a start date. So the file could looks like :
<startDate>28.10.2006 22:14:28</startDate>
<valueList>0.2 , 0.5 , 0.7, 0.9</valueList>
The first entry should be aligned to the last quarter before (!) the startDate. And then I have to iterate over the value list and align each value to the next quarter. The result should looks like :
28.10.2006 22:00 = 0.2
28.10.2006 22:15 = 0.5
28.10.2006 22:30 = 0.7
28.10.2006 22:45 = 0.9
Even if the startDate has always the format "dd.MM.yyyy hh:mm" it could represents a different timezone. Let's say "Asia/Seoul".
timezone = TimeZone.getTimeZone("Asia/Seoul");
dateFormat = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss");
dateFormat.setTimeZone(timezone);
parsedDate = dateFormat.parse(startDate);
My problem now is, when I try to create a cayenne Tbl* object and set the date value in it - the following value is stored in the database "Sat Oct 28 15:18:48 CEST 2006". But I would like to have "Sat Oct 28 22:18:48 CEST 2006". I think it's because java.util.Date just stores the time in UTC and "converts" it transparently to the default timezone.
Of course I can get a workaround for it. But the major problem for me is the break in time during the change from summer- to wintertime (the clock is turned back by 1h). In germay the clock changes from 3:00 a.m. to 2:00 a.m. That courses that when I proceed the value for 2:45 a.m the next value will be aligned to 2:00 a.m again. But in Korea there is no summer- or wintertime so the value should be aligned to 3:00 a.m..
Generally that's not a problem because the timezone has a method inDaylightTime() so that I know when the change was happen and can skipp the unneeded values.
But as I just said, in Korea there is no daylighttime and thus no need to skipp the values. But Java converts the (korean) date value into a date value for the default timezone (germany here) and so I have the values between 2:00 a.m and 3.00 a.m. twice. And that's not allowed by the database.
When I convert the date back into a string using
dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS, zzzz");
dateFormat.setTimeZone(timezone);
formattedDate = dateFormat.format(valueDate);
I get the correct date string back.
For me it looks like that cayenne (or maybe the jdbc driver) should consider the timezone for a date.
I've provided two samples to illustrate what I mean. The first small sample shows how cayenne stores date values. The second sample shows the exception during the change in daylight time.
It would be nice if someone could try it out and could give a comment on it.
Thanks, Lothar
------------------------------------ the database tables -------------------------------------------------------------------------------------------
/*
CREATE TABLE [dbo].[tblEfficiencyBlock] (
[efficiencyBlockId] [int] IDENTITY (1, 1) NOT NULL ,
[serialNumber] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblEffBlockData] (
[effBlockDataId] [int] IDENTITY (1, 1) NOT NULL ,
[efficiencyBlockId] [int] NOT NULL ,
[dataDate] [datetime] NOT NULL ,
[energyValue] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE
INDEX [IX_tblEffblockdata_1] ON [dbo].[tblEffBlockData] ([efficiencyBlockId], [dataDate])
WITH
DROP_EXISTING
ON [PRIMARY]
*/
/*
insert into tblEfficiencyBlock
(serialNumber)
values
('123456')
*/
------------------------------------------ the application code ----------------------------
void doTest{
energyValues = values.split(",");
valueDate = adjustDataDate(timezone, parsedDate);
for (String energyValue : energyValues) {
if (timezone.inDaylightTime(valueDate)) {
// skip some values
}
//TblImportEffBlockData importData = (TblImportEffBlockData)context.createAndRegisterNewObject(TblImportEffBlockData.class);
//importData.setEnergyValue(energyValue)
//importData.setDataDate(valueDate);
//importData.setToTblEfficiencyBlock(efficiencyBlock)
valueDate = getNextDataDate(timezone, valueDate);
}
//context.commitChanges();
}
private Date getNextDataDate(TimeZone timezone,Date date) {
// adds 15 min
Calendar cal = Calendar.getInstance(timezone);
cal.setTime(date);
cal.add(Calendar.MINUTE, 15);
return cal.getTime();
}
private Date adjustDataDate(TimeZone timezone,Date date) {
// round the passed date down to the previous quarter
Calendar cal = Calendar.getInstance(timezone) ;
cal.setTime(date);
int minutes = cal.get(Calendar.MINUTE) % 15;
int seconds = cal.get(Calendar.SECOND);
int mseconds = cal.get(Calendar.MILLISECOND);
cal.add(Calendar.MINUTE, -minutes);
cal.add(Calendar.SECOND, -seconds);
cal.add(Calendar.MILLISECOND, -mseconds);
return cal.getTime();
}
------------------------------------------------ 1st demo ----------------------------------------------------------------
<startDate>29.10.2006 01:54:28</startDate>
<valueList>
0.2,0.5,0.7,0.9,
0.2,0.5,0.7,0.9
</valueList>
-- cayenne output
INSERT INTO dbo.tblEffBlockData (dataDate, efficiencyBlockId, energyValue) VALUES (?, ?, ?)
[bind: '2006-10-28 20:15:00.0', 1091, 0.9114, 0.7]
[bind: '2006-10-28 20:30:00.0', 1091, 0.9121, 0.9]
[bind: '2006-10-28 20:00:00.0', 1091, 0.9107, 0.5]
[bind: '2006-10-28 19:45:00.0', 1091, 0.9098, 0.2]
[bind: '2006-10-28 21:15:00.0', 1091, 0.9114, 0.7]
[bind: '2006-10-28 21:30:00.0', 1091, 0.9121, 0.9]
[bind: '2006-10-28 21:00:00.0', 1091, 0.9107, 0.5]
[bind: '2006-10-28 20:45:00.0', 1091, 0.9098, 0.2]
-- database output
1091 2006-10-28 19:45:00.000 0.2
1091 2006-10-28 20:00:00.000 0.5
1091 2006-10-28 20:15:00.000 0.7
1091 2006-10-28 20:30:00.000 0.9
1091 2006-10-28 20:45:00.000 0.2
1091 2006-10-28 21:00:00.000 0.5
1091 2006-10-28 21:15:00.000 0.7
1091 2006-10-28 21:30:00.000 0.9
------------------------------------------------ 2nd demo ----------------------------------------------------------------
<startDate>28.10.2006 22:14:28</startDate>
<valueList>
13.65, 13.76, 13.81, 13.72,
13.65, 13.65, 13.65, 13.57,
13.49, 13.43, 13.43, 13.43,
13.42, 13.35, 13.20, 13.20,
13.28, 13.26, 13.28, 13.37,
13.57, 13.62, 13.42, 13.43,
13.54, 13.28, 12.94, 12.99,
12.96, 12.98, 13.01, 13.03,
13.16, 13.18, 13.33, 13.40,
13.42, 13.45, 13.62, 13.84,
13.96, 13.99, 14.20, 14.60,
15.06, 15.66, 16.35, 16.47,
16.78, 18.12, 17.76, 21.22,
21.97, 22.04, 25.65, 25.00,
19.54, 21.00, 18.59, 18.51,
17.62, 17.81, 17.66, 19.10,
20.59, 20.19, 18.76, 17.13,
16.86, 15.96, 14.83, 14.38,
13.55, 12.91, 12.30, 12.45,
12.60, 12.64, 12.54, 12.42,
11.72, 11.69, 11.82, 10.87,
10.50, 10.26, 10.14, 9.92,
10.09, 10.30, 10.06, 9.85,
9.70, 9.57, 9.45, 9.62
</valueList>
-- cayenne output
INSERT INTO dbo.tblEffBlockData ...
[bind: 2006-10-28 18:00:00.0', 0.0, 1091,
[bind: 2006-10-29 01:45:00.0', 0.0, 1091,
[bind: 2006-10-29 01:15:00.0', 0.0, 1091,
[bind: 2006-10-28 21:45:00.0', 0.0, 1091,
[bind: 2006-10-29 02:45:00.0', 0.0, 1091, xxx
[bind: 2006-10-28 15:00:00.0', 0.0, 1091,
[bind: 2006-10-29 13:15:00.0', 0.0, 1091,
[bind: 2006-10-29 07:00:00.0', 0.0, 1091,
[bind: 2006-10-29 06:00:00.0', 0.4 1091,
[bind: 2006-10-29 06:15:00.0', 0.3, 1091,
[bind: 2006-10-29 01:00:00.0', 0.0, 1091,
[bind: 2006-10-29 12:15:00.0', 0.0, 1091,
[bind: 2006-10-28 20:45:00.0', 0.0, 1091,
[bind: 2006-10-28 15:15:00.0', 0.0, 1091,
[bind: 2006-10-29 10:45:00.0', 0.0, 1091,
[bind: 2006-10-28 16:00:00.0', 0.0, 1091,
[bind: 2006-10-28 22:00:00.0', 0.0, 1091,
[bind: 2006-10-29 11:15:00.0', 0.0, 1091,
[bind: 2006-10-28 23:00:00.0', 0.0, 1091,
[bind: 2006-10-29 04:45:00.0', 0.1, 1091,
[bind: 2006-10-28 22:45:00.0', 0.0, 1091,
[bind: 2006-10-28 19:00:00.0', 0.0, 1091,
[bind: 2006-10-29 06:45:00.0', 0.1, 1091,
[bind: 2006-10-29 05:30:00.0', 0.1, 1091,
[bind: 2006-10-29 00:15:00.0', 0.0, 1091,
[bind: 2006-10-29 10:30:00.0', 0.0, 1091,
[bind: 2006-10-28 23:30:00.0', 0.0, 1091,
[bind: 2006-10-28 16:15:00.0', 0.0, 1091,
[bind: 2006-10-28 19:45:00.0', 0.0, 1091,
[bind: 2006-10-29 10:00:00.0', 0.0, 1091,
[bind: 2006-10-29 02:30:00.0', 0.1, 1091,
[bind: 2006-10-29 00:45:00.0', 0.0, 1091,
[bind: 2006-10-29 03:00:00.0', 0.3, 1091,
[bind: 2006-10-29 11:00:00.0', 0.0, 1091,
[bind: 2006-10-29 12:00:00.0', 0.0, 1091,
[bind: 2006-10-29 05:15:00.0', 0.1, 1091,
[bind: 2006-10-28 20:30:00.0', 0.0, 1091,
[bind: 2006-10-29 07:15:00.0', 0.0, 1091,
[bind: 2006-10-29 01:30:00.0', 0.0, 1091,
[bind: 2006-10-29 00:30:00.0', 0.0, 1091,
[bind: 2006-10-29 13:00:00.0', 0.0, 1091,
[bind: 2006-10-29 10:15:00.0', 0.0, 1091,
[bind: 2006-10-29 07:30:00.0', 0.0, 1091,
[bind: 2006-10-29 08:45:00.0', 0.0, 1091,
[bind: 2006-10-29 00:00:00.0', 0.0, 1091,
[bind: 2006-10-28 15:30:00.0', 0.0, 1091,
[bind: 2006-10-29 02:45:00.0', 0.4, 1091, xxx
*** error.
java.sql.SQLException: Cannot insert duplicate key row in object 'tblEffBlockData' with unique index 'IX_tblEffblockdata_1'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:421)
at org.objectstyle.cayenne.access.jdbc.BatchAction.runAsIndividualQueries(BatchAction.java:224)
at org.objectstyle.cayenne.access.jdbc.BatchAction.performAction(BatchAction.java:117)
at org.objectstyle.cayenne.dba.sqlserver.SQLServerBatchAction.performAction(SQLServerBatchAction.java:95)
at org.objectstyle.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:95)
at org.objectstyle.cayenne.access.DataNode.performQueries(DataNode.java:309)
at org.objectstyle.cayenne.access.DataDomainFlushAction.runQueries(DataDomainFlushAction.java:255)
at org.objectstyle.cayenne.access.DataDomainFlushAction.flush(DataDomainFlushAction.java:177)
at org.objectstyle.cayenne.access.DataDomain.onSyncFlush(DataDomain.java:830)
at org.objectstyle.cayenne.access.DataDomain$2.transform(DataDomain.java:801)
at org.objectstyle.cayenne.access.DataDomain.runInTransaction(DataDomain.java:856)
at org.objectstyle.cayenne.access.DataDomain.onSync(DataDomain.java:798)
at org.objectstyle.cayenne.access.DataContext.flushToParent(DataContext.java:1261)
at org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java:1165)
______________________________________________________________________________
"Ein Herz für Kinder" - Ihre Spende hilft! Aktion: www.deutschlandsegelt.de
Unser Dankeschön: Ihr Name auf dem Segel der 1. deutschen America's Cup-Yacht!
This archive was generated by hypermail 2.0.0 : Fri Dec 08 2006 - 10:59:00 EST