Return-Path: Delivered-To: apmail-incubator-cayenne-user-archive@locus.apache.org Received: (qmail 79310 invoked from network); 11 Dec 2006 11:54:27 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 11 Dec 2006 11:54:27 -0000 Received: (qmail 13647 invoked by uid 500); 11 Dec 2006 11:54:30 -0000 Delivered-To: apmail-incubator-cayenne-user-archive@incubator.apache.org Received: (qmail 13582 invoked by uid 500); 11 Dec 2006 11:54:30 -0000 Mailing-List: contact cayenne-user-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: cayenne-user@incubator.apache.org Delivered-To: mailing list cayenne-user@incubator.apache.org Received: (qmail 13484 invoked by uid 99); 11 Dec 2006 11:54:29 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 11 Dec 2006 03:54:29 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: local policy) Received: from [207.210.96.236] (HELO byaroza.objectstyle.org) (207.210.96.236) by apache.org (qpsmtpd/0.29) with SMTP; Mon, 11 Dec 2006 03:54:18 -0800 Received: (qmail 31231 invoked from network); 11 Dec 2006 11:53:57 -0000 Received: from unknown (HELO ?jR?????IPv6:::1?) (127.0.0.1) by localhost with SMTP; 11 Dec 2006 11:53:57 -0000 Mime-Version: 1.0 (Apple Message framework v752.3) In-Reply-To: <458785898@web.de> References: <458785898@web.de> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <64BDD2B7-C7A2-411E-82BA-8FA7102A6F9D@objectstyle.org> Content-Transfer-Encoding: 7bit From: Andrus Adamchik Subject: Re: how does cayenne handle java.util.date values ? Date: Mon, 11 Dec 2006 13:53:58 +0200 To: cayenne-user@incubator.apache.org X-Mailer: Apple Mail (2.752.3) X-Virus-Checked: Checked by ClamAV on apache.org 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 : > > 28.10.2006 22:14:28 > 0.2 , 0.5 , 0.7, 0.9 > > 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 > ---------------------------------------------------------------- > 29.10.2006 01:54:28 > > > 0.2,0.5,0.7,0.9, > 0.2,0.5,0.7,0.9 > > > -- 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 > ---------------------------------------------------------------- > 28.10.2006 22:14:28 > > > 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 > > > -- 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)