| *** Mud has joined #ingres | 01:00 | |
| *** grantc has quit IRC | 01:55 | |
| *** grantc has joined #ingres | 01:56 | |
| *** grantc has joined #ingres | 01:59 | |
| *** grantc has quit IRC | 02:08 | |
| *** grantc has joined #ingres | 02:08 | |
| *** atrofast has joined #ingres | 04:16 | |
| *** toumi01 has joined #ingres | 04:23 | |
| atrofast | Morning pboro, I'm back with another JDBC question... are there an peculiarities with how you handle DATE, TIME and TIMESTAMP datatypes when using prepared statements? | 04:27 |
|---|---|---|
| *** rossand has joined #ingres | 04:28 | |
| *** ChanServ sets mode: +o rossand | 04:28 | |
| *** Dejan has joined #ingres | 04:45 | |
| Dejan | hello everybody | 04:46 |
| pboro | atrofast, hmm, not really, why? | 04:49 |
| atrofast | Just that the DATE/TiME/TIMESTAMP tests that passed when not using the PreparedStatements stuff stopped passing when I switched to it | 04:49 |
| atrofast | Could be something else then, thanks | 04:50 |
| pboro | Hmm... | 04:50 |
| pboro | are you using INGRESDATE or ANSI types in the db? | 04:50 |
| atrofast | Whatever is default | 04:50 |
| pboro | for DATE? the default for DATE is INGRESDATE... it doesn't work too well with JDBC, cause INGRESDATE is not standard... | 04:51 |
| pboro | I would expect it to work if you use the ANSI types, like DATE (when aliased to ANSIDATE), TIME and TIMESTAMP | 04:51 |
| atrofast | Okay I'll change it and see | 04:52 |
| pboro | but with DATE aliased to INGRESDATE... dunno :) | 04:52 |
| pboro | anyway, I wouldn't recommend using INGRESDATE anyway, 'cos it is proprietary and not portable :) | 04:53 |
| atrofast | Yeah hmm where can I actually change the default? :) | 04:53 |
| pboro | cbf | 04:54 |
| pboro | ii.hostname.config.date_alias: ingresdate | 04:55 |
| pboro | change it to... ansidate :) then try again | 04:55 |
| pboro | (requires dbms restart) | 04:55 |
| atrofast | Ahh got it, thanks :) | 04:56 |
| pboro | I assume that the tests create tables on their own? | 04:57 |
| pboro | and if they use DATE and DATE is aliased to INGRESDATE, it might not work too well... there might be a connection time settings in JDBC driver too, if you don't want to play with cbf and system settings | 04:58 |
| atrofast | Hmm no difference... It's weird because if I didn't use prepared statements they worked | 04:58 |
| pboro | does it provide any debug information? like what's not working as expected? | 04:59 |
| atrofast | Sometimes heh | 05:01 |
| atrofast | It depends on where an exception is raised... Sometimes it's good at catching exceptions and uhh nto showing them | 05:01 |
| pboro | :D | 05:01 |
| pboro | is it so that the tests create the tables on their own when needed or do you create the tables manually? | 05:02 |
| atrofast | When using dates i na prepared statement, should you use setString(col, "2009-06-28 15:12:41.0") or something else? | 05:02 |
| pboro | setTimestamp | 05:02 |
| atrofast | The tables are created manually when the test is first run and those statements pass okay | 05:03 |
| atrofast | Okay, let's see if GeoTools is actually using setTimestamp | 05:03 |
| pboro | dunno if it's possible to use setString at all... maybe, I have to check the api | 05:03 |
| pboro | btw you don't get to see the exception at all, just that the test failed? | 05:04 |
| atrofast | Well I'm a n00b when it comes to JDBC :) | 05:04 |
| atrofast | Yeah it's got these assertTrue or assertEquals things... and it fails on a select count(*) from tab where timestampcol = ? | 05:05 |
| pboro | ah :) did you recreate the test tables after changing the alias for DATE? | 05:05 |
| pboro | apparently it inserts some certain timestamp in the table and then tries to retrieve the row using the same timestamp and it fails | 05:06 |
| pboro | or... the row is there already and it doesn't, for some reason, get matched with the timestamp given using JDBC | 05:07 |
| pboro | assertTrue and assertEquals sound like JUnit :) | 05:08 |
| atrofast | Yeah it is JUnit :) | 05:08 |
| atrofast | Also yeah it drops and reacreates tables automatically with every test | 05:09 |
| pboro | ok, and you're using prepared statements for both inserts and updates and selects? | 05:09 |
| pboro | maybe there's some difference in your code when doing inserts/updates and selects with timestamps/dates/times? | 05:10 |
| *** gerhard has joined #ingres | 05:10 | |
| *** javahorn has joined #ingres | 05:11 | |
| pboro | for DATETIME columns it should be setTimestamp, for DATE it should be setDate and for TIME it should be setTime | 05:11 |
| *** javahorn has left #ingres | 05:11 | |
| pboro | when using setString for any of those columns, it's up to the JDBC driver to figure out the necessary conversions... which is a bit unefficient too, and not "strongly typed" :) | 05:11 |
| atrofast | Ahh I see, yeah I'll have to step through teh debugger to see what it actually does :) | 05:14 |
| atrofast | Thanks | 05:14 |
| atrofast | Hey pboro, does this look like it'd work with Ingres? http://ingres.pastebin.com/khHHq2SK | 05:22 |
| pboro | it does, although I do not know what convert method actually does | 05:24 |
| atrofast | You can create custom converters for special cases (I did jdbcClob and jdbcBlob to String/byte[] respectively) but I never put any in for Date types so convert shouldn't do anything | 05:25 |
| pboro | okay, I would expect that to work... but for timestamps (DATETIME) it's not working correctly, i.e. SELECT for a timestamp fails to return the expected rows? | 05:26 |
| atrofast | Yeah Date works but Time and Timestamp does not... it filter on = on timestamp and >= on time and it returns the wrong number of rows | 05:28 |
| pboro | have you checked the values stored in the table, to make sure the values look ok there? | 05:29 |
| pboro | btw have you noticed that there's two Date classes in Java? java.util.Date and javax.sql.Date? | 05:30 |
| pboro | which are different, java.util.Date can be used for any dates and times (like a timestamp), but javax.sql.Date is simply a date | 05:30 |
| atrofast | Yeah if I run it in terminal monitor I get the right results: http://ingres.pastebin.com/XNZib8Cs | 05:31 |
| pboro | also, there's no java.util.Time or java.util.Timestamp, those are only in javax.sql package | 05:31 |
| pboro | so when the user provides you a java.util.Date, it may be proper for any of ANSIDATE, TIME or TIMESTAMP depending how java.util.Date is used... | 05:32 |
| atrofast | The datatypes that are used are java.sql.Time and java.sql.Timestamp | 05:32 |
| pboro | okay, so they should be ok | 05:32 |
| atrofast | yeah date works, it's just those two that are not playing nice :( | 05:33 |
| pboro | hm hm... | 05:34 |
| pboro | which junit test is the failing test? | 05:35 |
| pboro | testCount? | 05:36 |
| atrofast | http://ingres.pastebin.com/7TCYF32h | 05:38 |
| atrofast | And a similar one for Time | 05:38 |
| pboro | thanks | 05:38 |
| pboro | do you also have to code which inserts the data? | 05:39 |
| pboro | the one that is to be retrieved | 05:39 |
| atrofast | Oh hm that one doesn't use prepared statements: http://ingres.pastebin.com/ydXqzmMu | 05:42 |
| pboro | testFilterByTimeStamp uses java.util.Date for querying the rows, the Date is created using SimpleDateFormat, but no timezone is assigned, so it prolly uses system timezone... I wonder how the insert is doing | 05:42 |
| atrofast | Should it make a difference when retrieving? | 05:42 |
| pboro | jes :) | 05:42 |
| pboro | yes, I mean | 05:42 |
| atrofast | What if I create them all without Timezone? :) | 05:42 |
| pboro | I don't think you can create Date object which didn't have timezone | 05:43 |
| pboro | since timezone is a static part of any timestamp, you can leave it zero for sure | 05:43 |
| pboro | the problem prolly is this: | 05:43 |
| atrofast | Wow this is more complicated than I expected date types to be :) | 05:43 |
| pboro | testFilterByTimeStamp uses java.util.Date for querying the rows, and the Date object is created using SimpleDateFormat, but no timezone is assigned, so it prolly uses system timezone from your computer... but when doing the inserts, you use ingres's TIMESTAMP cast, which prolly uses the timezone of the dbms server | 05:44 |
| pboro | so this could be the problem | 05:44 |
| pboro | I would modify createDateTable to use prepared statements too | 05:45 |
| atrofast | Okay let me test that | 05:45 |
| pboro | so that there would not be this difference in handling the dates on inserting them and in retrieving them | 05:45 |
| pboro | the test actually tests two things: a) retrieving the row using literal date and setString and b) retrieving the row using java.util.Date object and setTimestamp (java.util.Date is converted into javax.sql.Timestamp in between) | 05:46 |
| *** cthibert has joined #ingres | 05:54 | |
| *** mull has joined #ingres | 06:00 | |
| atrofast | This is how I create the tables: http://ingres.pastebin.com/tcS7yUNM but still the same... Date works, the other two fails | 06:04 |
| pboro | hmm try this: | 06:07 |
| pboro | ps.setTimestamp(2, new java.sql.Timestamp(new SimpleDateFormat("HH:mm:ss,dd-yyyy-MM").parse("15:12:41,28-2009-06").getTime())); | 06:08 |
| pboro | then it's 1:1 with the insert | 06:08 |
| pboro | oops, with the retrieval | 06:09 |
| pboro | if it still doesn't work... I'm puzzled | 06:09 |
| pboro | btw does the test fail on the literal test or the timestamp one? | 06:10 |
| *** toumi01 has quit IRC | 06:10 | |
| atrofast | It fails on the getCount line | 06:10 |
| atrofast | It retrieves the wrong number of rows | 06:10 |
| pboro | yeah, but on which part of the test | 06:12 |
| pboro | there's actually to asserts in the same test, does the first one fail or the second one? | 06:12 |
| *** rossand1 has joined #ingres | 06:13 | |
| *** rossand has quit IRC | 06:13 | |
| atrofast | The first one | 06:13 |
| atrofast | And no that new statement didn't make a difference :( | 06:13 |
| pboro | ok... damn, it has to be something with the timezones, does the dbms run on the same computer as where you're running the tests? | 06:14 |
| atrofast | Yes it's all local | 06:14 |
| pboro | okay, and what is the timezone for ingres, is it the same as your os? | 06:14 |
| atrofast | Although hmm the DBMS is set for PST | 06:14 |
| atrofast | And my OS is EST | 06:14 |
| pboro | oops... well :) | 06:14 |
| atrofast | How do I change that? | 06:15 |
| pboro | which one is the correct one? | 06:15 |
| atrofast | Well the DBMS should be EST to match my OS | 06:15 |
| pboro | tho' I would argue the tests are broken, since they test times without making any assumptions about timezones | 06:15 |
| atrofast | Well I can override the tests if I must | 06:16 |
| pboro | it should be enough to set dbms timezone correct, you can change that using cbf | 06:16 |
| pboro | you need to recreate the test data then, and you can revert the changes to the createDateTable | 06:16 |
| pboro | hmm actually... I'm not sure how to change the timezone of the dbms, can't find it in config.dat | 06:18 |
| *** toumi01 has joined #ingres | 06:19 | |
| atrofast | Yeah me neither | 06:19 |
| atrofast | it's listed when I do ingprenv | 06:19 |
| pboro | ingsetenv prolly | 06:20 |
| pboro | yes :) | 06:21 |
| pboro | ingsetenv NAME value | 06:21 |
| atrofast | Okay let's see how this works :D | 06:21 |
| pboro | yup... "ingsetenv II_TIMEZONE_NAME EST" should do the trick, then restart | 06:22 |
| atrofast | Haha no :( | 06:23 |
| atrofast | select date('now') shows the correct time but still same failures | 06:23 |
| pboro | ok, try: | 06:23 |
| pboro | (still browsing the manual...) | 06:25 |
| atrofast | Haha thanks for help me out :) | 06:25 |
| pboro | SELECT TIMESTAMP_WITH_TZ(dt) FROM yourtable; | 06:27 |
| pboro | and see what are the timezones | 06:27 |
| pboro | there has got to be some difference, the problem with the literal timestamp and time test is that it doesn't define the timezone and it is likely that for some reasons they don't match | 06:27 |
| atrofast | http://ingres.pastebin.com/6NfxBvqP | 06:29 |
| pboro | erm... how is it possible that one of the dates is using different timezone than the others? | 06:30 |
| pboro | it's using the SimpleDateFormat? | 06:30 |
| atrofast | Hehe I dunno, all three rows are using SimpleDateFormat | 06:30 |
| pboro | wtf :D | 06:30 |
| pboro | ahh... there's prolly a boundary case | 06:31 |
| pboro | hmmh, no... the timezone should not change from savings time on those dates | 06:31 |
| atrofast | Can't I just create them without any timezone data and be done with ti? :( | 06:32 |
| pboro | ahh but one of the timestamps IS on not savings time and others are | 06:32 |
| pboro | well you could change the test so that in createDateTable the rows are inserted using A SPECIFIC timezone and the tests also use that specific timezone... as I said, the tests are currently flawed in the sense that they make no assumption of the timezone | 06:33 |
| pboro | but hmm, I wonder if that would actually fix it... | 06:33 |
| atrofast | What's the syntax for create? TIMESTAMP WITH TIME ZONE EST? | 06:34 |
| pboro | actually :) you can create a timestamp column WITHOUT timezone information | 06:34 |
| Dejan | wow | 06:35 |
| pboro | add WITHOUT TIME ZONE to it | 06:35 |
| Dejan | this channel is ACTIVE | 06:35 |
| Dejan | people TALK | 06:35 |
| Dejan | :) | 06:35 |
| atrofast | Okay let's see what happens if I do that | 06:35 |
| atrofast | Stil lfails | 06:35 |
| pboro | you recreated the table already? | 06:36 |
| atrofast | Haha Dejan, pboro is helping out a JDBC n00b here :) | 06:36 |
| pboro | you need WITHOUT TIME ZONE in the table creation | 06:36 |
| pboro | like... | 06:36 |
| pboro | CREATE TABLE DATES (D ANSIDATE, DT TIMESTAMP WITHOUT TIME ZONE, T TIME WITHOUT TIME ZONE) | 06:36 |
| atrofast | Yeah CREATE TABLE DATES (D ANSIDATE, DT TIMESTAMP without time zone, T TIME without time zone) | 06:36 |
| pboro | but that's not a good idea, because of savings times ;/ | 06:36 |
| Dejan | JDBC n00b? o.O I did not ask any quesion! | 06:36 |
| Dejan | :D | 06:36 |
| pboro | I wonder if there's a problem in 10.0 or if the problem is in the test... are those tests available for postgresql or similar, I could check out how they do that stuff? | 06:37 |
| Dejan | The only JDBC n00b here is ME | 06:37 |
| Dejan | I reserve that title for myself! | 06:37 |
| pboro | found it :) | 06:37 |
| atrofast | Yeah these tests run on Postgres/Postgis Oracle, SQLServer and MySQL (I think) ... Although Oracle's dates use a special converter so they're not quite as comparable | 06:38 |
| atrofast | I've only been using JDBC for a month or two :P | 06:39 |
| atrofast | The tests I wrote for Ingres in GeoTools are pretty much just copy/paste of the postgres ones with updates where needed | 06:42 |
| pboro | Yeah, I guess PostgreSQL ones are pretty good starting point | 06:43 |
| pboro | I'm preparing a small test case for you in Java, it will shed some light to this problem :) | 06:43 |
| pboro | since shooting blind is a bit awful... | 06:43 |
| atrofast | Thank you, I really appreciate the help | 06:44 |
| pboro | (good alternative doing for not writing my thesis...) | 06:44 |
| atrofast | You won't hear me complaining :D | 06:45 |
| pboro | 10 more minutes, I'll try it out on my Ingres 9.2 | 06:51 |
| pboro | damn I hate Oracle, Java API documentation was fast before and as soon as Oracle moved it on their servers, it became sluggerish... | 06:53 |
| *** Mud has quit IRC | 06:54 | |
| atrofast | Hehe I didn't browse it often enough to have an opinion :) | 06:55 |
| atrofast | Okay I'll BRB, gotta grab some breakfast and stuff, havne't eaten yet... Dove right back into work heh | 06:56 |
| pboro | sure :) I'll post the code on pastebin or similar and you can get it from there :) | 06:58 |
| pboro | hmmh... doesn't Ingres expose the session timezone in dbmsinfo... | 07:08 |
| pboro | apparently not | 07:10 |
| *** gerhard has quit IRC | 07:13 | |
| pboro | wtf... I may have already found a new bug in 9.2 while testing this stuff :D | 07:27 |
| pboro | If I run INSERT INTO foo VALUES (DATE('NOW')) thru JDBC, I get... 2010-07-13 14:26:31.000000000+03:00... but through isql, I get 2010-07-13 17:26:31.000000000+03:00 | 07:28 |
| pboro | the timezone is correct in both cases, but the value stored when using JDBC is incorrect | 07:28 |
| pboro | it should be "2010-07-13 14:26:31.000000000+00:00" or "2010-07-13 17:26:31.000000000+03:00", but not the one I got :o | 07:29 |
| pboro | oh well, doesn't matter | 07:29 |
| atrofast | I wonder if this problem persist in 10.0 | 07:35 |
| atrofast | Probably does :P | 07:35 |
| *** Alex| has quit IRC | 07:40 | |
| grantc | pboro, what happens if you set the timeZone property at connection time? | 07:47 |
| pboro | grantc, what was the parameter? | 07:48 |
| grantc | or use TZ | 07:48 |
| grantc | in the connection attribute | 07:48 |
| pboro | lemme try | 07:48 |
| grantc | timeZone (property) TZ (attribute) | 07:48 |
| grantc | the fine manual says the jdbc driver should use the II_TIMEZONE_NAME of the server, perhaps that's not happening | 07:49 |
| grantc | also what do you get if you do "insert into foo values (CURRENT_TIMESTAMP)" where the column is a timestamp value not ingresdate | 07:50 |
| pboro | I'm not using INGRESDATE at all | 07:50 |
| pboro | ah, I need to add timezone as a connection attribute, it's not supported on the db url... | 07:51 |
| grantc | in the url it's TZ not timezone | 07:53 |
| grantc | timeZone is the property | 07:53 |
| pboro | ah okay, I'll try that | 07:54 |
| pboro | (btw kbdoc http://www.ingres.com/kb/article/400153 is outdated) | 07:54 |
| pboro | grantc, it worked | 07:55 |
| *** Alex| has joined #ingres | 07:55 | |
| *** ChanServ sets mode: +o Alex| | 07:55 | |
| pboro | ingprenv tells that the current Ingres timezone is EUROPE-EASTERN, if I add TZ=EUROPE-EASTERN to connection string, I get: | 07:56 |
| pboro | Local time according to Java: 13 heinä 2010 17:55:19 +0300 | 07:56 |
| pboro | Local time according to Ingres: 13 heinä 2010 17:55:19 +0300 | 07:56 |
| grantc | yeah - AFAIAC the docs.ingres.com is the goto for that | 07:56 |
| pboro | without TZ, I get: | 07:56 |
| pboro | Local time according to Java: 13 heinä 2010 17:56:27 +0300 | 07:56 |
| pboro | Local time according to Ingres: 13 heinä 2010 14:56:27 +0300 | 07:56 |
| grantc | and the II_TIMEZONE_NAME on the server is EU-EAST? | 07:57 |
| atrofast | Haha that worked | 07:57 |
| pboro | but the funny thing is, that the timezone is correct in both cases :D | 07:57 |
| atrofast | TZ=NA-EASTERN | 07:57 |
| atrofast | All tests pas | 07:57 |
| pboro | grantc, it's EUROPE-EASTERN | 07:57 |
| pboro | atrofast, lol, there's definitely a bug :D | 07:57 |
| grantc | or an undocumented feature | 07:57 |
| grantc | as it's sending/assuming GMT | 07:57 |
| atrofast | You'd think it'd work if DBMS server and OS has the same timezone | 07:57 |
| pboro | well, a nasty one at least... | 07:57 |
| pboro | atrofast, that was my assumption too | 07:57 |
| grantc | it all depends on what the JDBC spec says etc... | 07:58 |
| pboro | atrofast, but since it didn't work, I thought... I'll write a test program for you, but surprisingly I got the same prob | 07:58 |
| atrofast | Thanks a lot for the help pboro, grantc | 07:58 |
| grantc | pboro, what's the col type? | 07:58 |
| atrofast | TIMESTAMP and TIME | 07:58 |
| pboro | the ones atrofast mentioned | 07:58 |
| atrofast | They were both not behaving as we expected | 07:58 |
| grantc | with/without timezone? | 07:58 |
| atrofast | Tried with both | 07:59 |
| pboro | my test was without | 07:59 |
| pboro | oops, with I meant :) | 07:59 |
| grantc | atrofast, send an email to teresa | 08:00 |
| grantc | unless pboro want's to log an SD issue | 08:00 |
| pboro | here's the test case: https://www10.uta.fi/opsidemo/test/IngresTests.java | 08:02 |
| pboro | I can't log an SD issue since I have to direct all cases through the finnish support (Tieto), so I would prefer you guys doing it | 08:03 |
| pboro | And I hate trying to convince the finnish support that there actually is a problem to make them submit an issue to service desk... :/ | 08:04 |
| pboro | but if you leave me no option, I will do it :D | 08:07 |
| grantc | Understood :) | 08:09 |
| pboro | It sucks that we have to pay the same amount for the support as everyone else, but we don't have a permission to create new issues on SD ;( | 08:10 |
| *** KermitTheFragger has joined #ingres | 08:11 | |
| atrofast | SO pboro your test case shows that local time according to Ingres is wrong unless you set TZ? | 08:11 |
| pboro | but that's the way it has to go, so... :) | 08:11 |
| pboro | atrofast, yup | 08:11 |
| pboro | atrofast, did you try it too? | 08:11 |
| pboro | ah, I assume you're writing the bug report :) | 08:12 |
| pboro | my driver version is 3.4.8 | 08:12 |
| pboro | and 9.2.0 (a64.lnx/143)NPTL + p13732 | 08:12 |
| atrofast | Yeah trying it with Ingres 10 | 08:13 |
| pboro | java -classpath iijdbc.jar:. IngresTests "jdbc:ingres://localhost:II7/testdb" | 08:15 |
| pboro | there's an example how to run it | 08:15 |
| pboro | then just add ;TZ=YOURTIMEZONE before the last " | 08:15 |
| pboro | (you might also need USER=y;PASSWORD=x) | 08:15 |
| atrofast | Heh I get this: Couldn't get connection!: java.sql.SQLException: No suitable driver found for jdcb:ingres://localhost:G17/geo | 08:16 |
| atrofast | Oh duh | 08:16 |
| atrofast | jdbc | 08:16 |
| atrofast | I'm an idiot | 08:16 |
| pboro | do you have iijdbc.jar in the same dir? | 08:16 |
| pboro | hee hee :D | 08:16 |
| pboro | oh you, of course, need to compile it too (javac IngresTests.java)... | 08:17 |
| pboro | oops, as you did (or used the .class I had shared too) | 08:17 |
| atrofast | http://ingres.pastebin.com/bPNbSEPb is what I get | 08:17 |
| atrofast | I compiled it myself :) | 08:17 |
| pboro | you need "" around the connection url | 08:17 |
| pboro | because ; is a special char for the shell | 08:17 |
| pboro | echo foo; echo bar :) | 08:18 |
| atrofast | Haha | 08:18 |
| pboro | so your TZ was not really used by the prog... | 08:18 |
| atrofast | I'm screwing up today :P | 08:18 |
| atrofast | Okay now it works | 08:18 |
| pboro | "works"... :----D | 08:18 |
| atrofast | Haha yeah "works" | 08:19 |
| atrofast | It behaves as expected! | 08:19 |
| pboro | yup :D | 08:19 |
| pboro | oh well, one more bug getting squashed :) thanks mate, I love to see JDBC stuff getting better since it's so vital for me | 08:19 |
| pboro | I'll delete the test case from my server, ok? | 08:20 |
| pboro | so copy it before it disappears :) | 08:20 |
| atrofast | Yeah it's fine I got it, thanks | 08:20 |
| pboro | ok | 08:20 |
| grantc | it's not a bug | 08:22 |
| grantc | TIMESTAMP has no timezone | 08:22 |
| grantc | if you want timezone you need to define the column as "TIMESTAMP WITH TIME ZONE" | 08:22 |
| pboro | so the default for timestamp is no timezone? hmm... | 08:23 |
| atrofast | Then why doesn't it work? :P | 08:23 |
| grantc | atrofast, no idea | 08:23 |
| atrofast | I mean if default is no time zone, queries should ignore it | 08:23 |
| pboro | yeah, I would expect it to store the local time then | 08:23 |
| atrofast | Yet it doesn't | 08:23 |
| pboro | "local" | 08:23 |
| grantc | pboro, that's what i got from reading the docs | 08:23 |
| grantc | if you want local time you need to use "timestamp with local time zone" | 08:24 |
| grantc | RTFM :P | 08:24 |
| *** cthibert has quit IRC | 08:24 | |
| pboro | if this is the case, then Ingres works differently compared to all other dbmses | 08:24 |
| grantc | http://docs.ingres.com/Ingres/9.3/SQL%20Reference%20Guide/datetimedatatypes.htm#o2095 | 08:24 |
| grantc | pboro, that i don't know | 08:25 |
| pboro | if time_zone_spec is missing, which is the default? I guess that's in the standard, hmm... | 08:26 |
| pboro | according to the spec, TIMESTAMP is TIME + DATE without TIME ZONE... okay, but what time should be stored in the db then, I wonder if that is defined | 08:27 |
| atrofast | I say we should all just use UNIX Epoch time :P | 08:30 |
| pboro | that's GMT :) | 08:30 |
| grantc | atrofast, regarding your mail "TIMESTAMP=TIMESTAMP WITHOUT TIME ZONE" | 08:33 |
| pboro | grantc, since the JDBC driver is expected to use the timezone of the dbms, I would expect it to work alike when the query is run using isql | 08:33 |
| pboro | grantc, instead, I need to add TZ to make it work correctly through JDBC | 08:33 |
| pboro | the TZ attribute is simply stating the same fact that the driver should be getting from the server, no? | 08:34 |
| grantc | from what i can gather you need to use with local time zone | 08:34 |
| pboro | how come it is not necessary with isql? | 08:34 |
| *** Mud has joined #ingres | 08:35 | |
| pboro | I'm not argueing whether it should work this way or that way, but there's a conflict between how it works through JDBC and for example, through isql | 08:35 |
| grantc | that i don't know | 08:36 |
| pboro | hmm, actually, the description for TZ/timezone says... | 08:36 |
| pboro | "Specifies the Ingres timezone associated with the client's location. Corresponds to the Ingres environment variable II_TIMEZONE_NAME and is assigned the same values. This property is not used directly by the driver but is sent to the DBMS and affects the processing of dates." | 08:36 |
| pboro | it says nothing that driver would use the same setting as the server :o | 08:37 |
| pboro | so actually... if your timezone is not GMT, you will ALWAYS need TZ/timezone setting in JDBC driver | 08:37 |
| atrofast | I think it should be easier to use than that IMO | 08:38 |
| pboro | that doesn't make much sense :) at least it has to be mentioned in the JDBC driver instructions very clearly | 08:39 |
| pboro | me too | 08:39 |
| pboro | the timezone of the server would be a good default for the driver to use if no TZ/timezone is set | 08:39 |
| pboro | it's pretty unusual that the server would be in different tz than the JDBC client, although cases do exist, but then... there's no other way than explicitly defining TZ/timezone on the client side | 08:40 |
| atrofast | Our JDBC guy is on vacation, got an out of off reply so we'll see waht he says when he gets back :) | 08:42 |
| pboro | cool, thanks :) I have not run into this problem before since we don't use DATE('NOW') or literal times/timestamps at all... if I use java.sql.Timestamp or java.sql.Time, it works correctly regardless of TZ/timezone, since java.sql.Time/Timestamp contains timezone and the server correctly stores the times in this case | 08:44 |
| atrofast | I wonder if it's an obscure problem... In any event GeoTools is passing almost all tests now... Thanks for all the help pboro, it's been invaluable | 08:45 |
| pboro | no prob, it was fun debugging :) | 08:46 |
| pboro | if you remember, let me know what the jdbc guy replied to the problem :) | 08:46 |
| atrofast | Yup, I'll keep you up to date :0 | 08:48 |
| *** KermitTheFragger has quit IRC | 09:25 | |
| *** cthibert has joined #ingres | 09:36 | |
| *** Alex| has quit IRC | 09:51 | |
| *** Alex| has joined #ingres | 09:51 | |
| *** ChanServ sets mode: +o Alex| | 09:51 | |
| *** Dejan has quit IRC | 10:02 | |
| *** grantc has quit IRC | 10:05 | |
| *** cthibert has left #ingres | 11:11 | |
| *** cthibert has joined #ingres | 11:14 | |
| *** toumi01 has quit IRC | 11:14 | |
| *** toumi01 has joined #ingres | 11:23 | |
| *** mull has quit IRC | 12:15 | |
| *** mull has joined #ingres | 12:17 | |
| *** toumi01 has quit IRC | 12:59 | |
| *** Mud has quit IRC | 13:08 | |
| *** Alex____ has joined #ingres | 13:15 | |
| *** Alex| has quit IRC | 13:15 | |
| *** Alex____ has quit IRC | 13:33 | |
| *** toumi01 has joined #ingres | 13:57 | |
| *** cthibert has left #ingres | 14:12 | |
| *** mull has quit IRC | 14:47 | |
| *** toumi01 has quit IRC | 16:13 | |
| *** atrofast has quit IRC | 18:00 | |
| *** mull has joined #ingres | 18:23 | |
| *** cytrinox has quit IRC | 19:23 | |
| *** cytrinox has joined #ingres | 19:25 | |
| *** rossand1 has quit IRC | 19:28 | |
| *** mull has quit IRC | 19:33 | |
| *** Alex| has joined #ingres | 22:37 | |
| *** ChanServ sets mode: +o Alex| | 22:37 | |
| *** Mud has joined #ingres | 23:04 | |
| *** Alex| has quit IRC | 23:24 | |
| *** grantc has joined #ingres | 23:54 | |
| *** Alex| has joined #ingres | 23:59 | |
| *** ChanServ sets mode: +o Alex| | 23:59 | |
Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!