Thread: Date precision problem
Hi, I having some problem with the date fields migration from 7.1.3 version to 7.2 version... In my current version PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 I get this result select now(); now ------------------------ 2002-04-11 12:58:33+02 (1 row) In the new version PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96 SELECT NOW(); now ------------------------------- 2002-04-11 13:02:27.943119+02 (1 row) The date is different in the last part: 2002-04-11 13:02:27.943119+02 ^^^^^^^^^ Then when I am trying to update this field from ACCESS I have an error and the entry can't be updated because the dates are not equals. Someone can help me? Thanks.
noy wrote: > > Hi, > > I having some problem with the date fields migration from 7.1.3 version to 7.2 > version... > > In my current version PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC > 2.96 I get this result > > select now(); > now > ------------------------ > 2002-04-11 12:58:33+02 > (1 row) > > In the new version PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96 > > SELECT NOW(); > now > ------------------------------- > 2002-04-11 13:02:27.943119+02 > (1 row) > > The date is different in the last part: > > 2002-04-11 13:02:27.943119+02 > ^^^^^^^^^ > Then when I am trying to update this field from ACCESS I have an error and the > entry can't be updated because the dates are not equals. Is there no problem with 7.1.3 ? regards, Hiroshi Inoue http://w2422.nsk.ne.jp/~inoue/
Hiroshi Inoue wrote: > > noy wrote: > > > > Hi, > > > > I having some problem with the date fields migration from 7.1.3 version to 7.2 > > version... > > Is there no problem with 7.1.3 ? No, I have no problem in the version 7.1.3 with psqlodbc_07_01_0009 driver. Bye
noy wrote: > > Hiroshi Inoue wrote: > > > > noy wrote: > > > > > > Hi, > > > > > > I having some problem with the date fields migration from 7.1.3 version to 7.2 > > > version... > > > > > Is there no problem with 7.1.3 ? > > > > No, I have no problem in the version 7.1.3 with psqlodbc_07_01_0009 driver. Here I see no problem with 7.2 server though I see the problem with 7.1.3 server. regards, Hiroshi Inoue http://w2422.nsk.ne.jp/~inoue/
> I having some problem with the date fields migration from 7.1.3 version to 7.2 > version... > In the new version PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96 > The date is different in the last part: > 2002-04-11 13:02:27.943119+02 > ^^^^^^^^^ > Then when I am trying to update this field from ACCESS I have an error and the > entry can't be updated because the dates are not equals. Could you be more specific about the problem? I first interpreted this as a problem with now(), but now that I read this again you are talking about updating fields so that is not the actual test case is it? What entry are you updating? What are you comparing to be equal? Is this some funny business with Access generating updates from keys it is holding internally (I recall something about it doing that)? If for some reason you need less precision in your date/times, you can now explicitly set the precision of a column: thomas=# select timestamp without time zone 'now', thomas=> timestamp(2) without time zone 'now', thomas=> timestamp(0) without time zone 'now'; timestamp | timestamp | timestamp ----------------------------+------------------------+--------------------- 2002-04-17 06:34:33.032644 | 2002-04-17 06:34:33.03 | 2002-04-17 06:34:33 But afaik others have not seen a problem; could you have some funny settings in Access which are causing your queries to fail? I haven't used it myself, so can't help in more detail I'm afraid... - Thomas
Thomas Lockhart wrote: > > Could you be more specific about the problem? I first interpreted this > as a problem with now(), but now that I read this again you are talking > about updating fields so that is not the actual test case is it? Hi, These are all the steps that show the problem. ---- 1 ---- In PostgreSQL 7.1.3 create a table and a trigger to update it. create table "test" ( id integer, name character varying(20), last_update timestamp with time zone ); CREATE FUNCTION LASTUPDATE_TEST () RETURNS OPAQUE AS ' BEGIN new."last_update" = ''now''; RETURN new; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER last_update BEFORE UPDATE OR INSERT ON "test" FOR EACH ROW EXECUTE PROCEDURE LASTUPDATE_TEST(); ---- 2 ---- Insert 3 rows in the table select * from test; id | name | last_update ----+--------+------------------------ 1 | name 1 | 2002-04-17 16:56:38+02 2 | name 2 | 2002-04-17 16:56:54+02 3 | name 3 | 2002-04-17 16:57:00+02 (3 rows) ---- 3 ---- From ACCESS update one of the rows in the table. Here are the logs for this update. DEBUG: StartTransactionCommand DEBUG: query: SELECT "id","name","last_update" FROM "test" WHERE "id" = 1 DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: BEGIN DEBUG: ProcessUtility: BEGIN DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: UPDATE "test" SET "name"='name 1 update 1' WHERE "id" = 1 AND "name" = 'name 1' AND "last_update" = '2002-04-17 16:56:38' DEBUG: ProcessQuery DEBUG: query: SELECT 'now' DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: COMMIT DEBUG: ProcessUtility: COMMIT DEBUG: CommitTransactionCommand ---- 4 ---- From ACCESS update again the same row in the table. DEBUG: StartTransactionCommand DEBUG: query: SELECT "id","name","last_update" FROM "test" WHERE "id" = 1 DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: BEGIN DEBUG: ProcessUtility: BEGIN DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: UPDATE "test" SET "name"='name 1 update 2' WHERE "id" = 1 AND "name" = 'name 1 update 1' AND "last_update" = '2002-04-17 16:59:02' DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: COMMIT DEBUG: ProcessUtility: COMMIT DEBUG: CommitTransactionCommand ---- 5 ---- The result after both updates select * from test; id | name | last_update ----+-----------------+------------------------ 2 | name 2 | 2002-04-17 16:56:54+02 3 | name 3 | 2002-04-17 16:57:00+02 1 | name 1 update 2 | 2002-04-17 16:59:58+02 (3 rows) ---- 6 ---- Import the table to PostgreSQL 7.2 and the result is correct: select * from test; id | name | last_update ----+-----------------+------------------------ 2 | name 2 | 2002-04-17 16:56:54+02 3 | name 3 | 2002-04-17 16:57:00+02 1 | name 1 update 2 | 2002-04-17 16:59:58+02 (3 rows) ---- 7 ---- Then make an update over the same row in the table imported in PostgresSQL 7.2 DEBUG: StartTransactionCommand DEBUG: query: SELECT "id","name","last_update" FROM "test" WHERE "id" = 1 DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: BEGIN DEBUG: ProcessUtility: BEGIN DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: UPDATE "test" SET "name"='name 1 update 3' WHERE "id" = 1 AND "name" = 'name 1 update 2' AND "last_update" = '2002-04-17 16:59:58' DEBUG: ProcessQuery DEBUG: query: SELECT 'now' DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: COMMIT DEBUG: ProcessUtility: COMMIT DEBUG: CommitTransactionCommand ---- 8 ---- The row is updated succesfully. select * from test; id | name | last_update ----+-----------------+------------------------------- 2 | name 2 | 2002-04-17 16:56:54+02 3 | name 3 | 2002-04-17 16:57:00+02 1 | name 1 update 3 | 2002-04-17 16:40:44.548177+02 (3 rows) ---- 9 ---- Try to update the same row from ACCESS again. And a rollback is made by Postgres DEBUG: StartTransactionCommand DEBUG: query: SELECT "test"."id" FROM "test" DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT "id","name","last_update" FROM "test" WHERE "id" = 2 OR "id" = 3 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT "id","name","last_update" FROM "test" WHERE "id" = 1 DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: BEGIN DEBUG: ProcessUtility: BEGIN DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: UPDATE "test" SET "name"='name 1 update 4' WHERE "id" = 1 AND "name" = 'name 1 update 3' AND "last_update" = '2002-04-17 16:40:44.548' DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: ROLLBACK DEBUG: ProcessUtility: ROLLBACK DEBUG: CommitTransactionCommand -------------------------------- This is all the sequence to obtain the error. ACCESS includes in the where clause "last_update" = '2002-04-17 16:40:44.548' and postgres has '2002-04-17 16:40:44.548177+02' These two dates are different and the row is not updated because there is not matching row. The field last_update receives the value of the function now (by the trigger). Then if we execute select now(); in Postgres 7.1.3 we obtain now ------------------------ 2002-04-17 18:01:58+02 But select now(); in Postgres 7.2 returns now ------------------------------- 2002-04-17 17:06:11.937501+02 The date representation is different. I hope the explanation is clear now. bye & thanks.
... > This is all the sequence to obtain the error. ACCESS includes in the where > clause "last_update" = '2002-04-17 16:40:44.548' and postgres has '2002-04-17 > 16:40:44.548177+02' These two dates are different and the row is not updated > because there is not matching row. So how did Access decide that it had a three digit fractional seconds field? afaik neither PostgreSQL nor its ODBC driver are truncating the time. If you need to constrain times to have a limited precision, you might try defining things as (in this case) "timestamp(3)" rather than just "timestamp". And perhaps others have some experience in tweaking Access to give you, uh, access to the data you have. - Thomas
On Wed, Apr 17, 2002 at 06:34:49PM +0200, noy wrote: > Thomas Lockhart wrote: > > > > Could you be more specific about the problem? I first interpreted this > > as a problem with now(), but now that I read this again you are talking > > about updating fields so that is not the actual test case is it? > > DEBUG: StartTransactionCommand > DEBUG: query: BEGIN > DEBUG: ProcessUtility: BEGIN > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: query: UPDATE "test" SET "name"='name 1 update 4' WHERE "id" = 1 AND > "name" = 'name 1 update 3' AND "last_update" = '2002-04-17 16:40:44.548' > DEBUG: ProcessQuery > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: query: ROLLBACK > DEBUG: ProcessUtility: ROLLBACK > DEBUG: CommitTransactionCommand Ok, what's happening is that Access is trying to update the row and the only way to identify the row is by matching all the fields. Access is truncating the date, hence it can't update. Solution: define a primary key on the table and tell access to use that. Then it won't rely on properties of the date types to update successfully. You may be able to fix it on the postgres by telling it to use a date type that does not store nanoseconds. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Ignorance continues to thrive when intelligent people choose to do > nothing. Speaking out against censorship and ignorance is the imperative > of all intelligent people.
noy wrote: > > ---- > 8 > ---- > The row is updated succesfully. > > select * from test; > id | name | last_update > ----+-----------------+------------------------------- > 2 | name 2 | 2002-04-17 16:56:54+02 > 3 | name 3 | 2002-04-17 16:57:00+02 > 1 | name 1 update 3 | 2002-04-17 16:40:44.548177+02 > (3 rows) > > ---- > 9 > ---- > Try to update the same row from ACCESS again. And a rollback is made by Postgres > > DEBUG: StartTransactionCommand > DEBUG: query: SELECT "test"."id" FROM "test" > DEBUG: ProcessQuery > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: query: SELECT "id","name","last_update" FROM "test" WHERE "id" = 2 OR > "id" = 3 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 > OR "id" = 1 OR "id" = 1 > DEBUG: ProcessQuery > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: query: SELECT "id","name","last_update" FROM "test" WHERE "id" = 1 > DEBUG: ProcessQuery > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: query: BEGIN > DEBUG: ProcessUtility: BEGIN > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: query: UPDATE "test" SET "name"='name 1 update 4' WHERE "id" = 1 AND > "name" = 'name 1 update 3' AND "last_update" = '2002-04-17 16:40:44.548' > DEBUG: ProcessQuery > DEBUG: CommitTransactionCommand > DEBUG: StartTransactionCommand > DEBUG: query: ROLLBACK > DEBUG: ProcessUtility: ROLLBACK > DEBUG: CommitTransactionCommand Oh probably I see your point. How about changing the line new."last_update" = ''now''; to new."last_update" = ''now''::timestamp(0); or creating the last_update field as timestamp(0) from the first ? regards, Hiroshi Inoue http://w2422.nsk.ne.jp/~inoue/