Yeah, daylight saving time is tricky... Though the Date object still
stores information about the timezone. I am surprised the driver
doesn't account for it. Here is a few options that you have:
1. [the simplest] If you can redesign your database, I'd suggest
storing dates as long numbers.
2. "Normalize" dates as "pseudo-UTC" (i.e. subtracting an absolute
difference with UTC, that should take into account the daylight
saving time):
Calendar cal1 = new GregorianCalendar();
cal1.setTime(date1);
int offsetMinutes = -(cal1.get(Calendar.ZONE_OFFSET) + cal1.get
(Calendar.DST_OFFSET)) / (60 * 1000);
But again, I am surprised that driver doesn't do that for you - may
require more research with your DB...
Andrus
On Dec 8, 2006, at 5:58 PM, Lothar Krenzien wrote:
> 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.performActi
> on(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)
This archive was generated by hypermail 2.0.0 : Mon Dec 11 2006 - 06:54:32 EST