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!
