cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: how does cayenne handle java.util.date values ?
Date Mon, 11 Dec 2006 11:53:58 GMT
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)


Mime
View raw message