Обсуждение: Insert statement changes timestamp value from MS Access ODBC
Please see attached text document. Thanks, Dave David Dabney OHH Database Specialist/Programmer Hollings Marine Lab 131 Fort Johnson Road Charleston, SC 29464 843.762.8984 insert into public_collection (original_collection_code, method, station_id, date_time, sampling_project_id, contributor) select LUCES_wq.deploy_code, LUCES_wq.method, LUCES_wq.station_id, Min(LUCES_wq.datetime) AS MinOfdatetime, 12, 1 FROM LUCES_wq GROUP BY LUCES_wq.deploy_code, LUCES_wq.method, LUCES_wq.station_id ORDER BY LUCES_wq.deploy_code INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per, do_mg_l,depth_m ) SELECT deploy_code, c.id, datetime, water_temp, ph, sp_cond, salinity, do_sat, do_mg_l, depth_m FROM LUCES_wq as lwq INNER JOIN public_collection as c on lwq.deploy_code = c.original_collection_code INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per, do_mg_l,depth_m) SELECT deploy_code, c.id, datetime, water_temp, ph, sp_cond, salinity, do_sat, do_mg_l, depth_m FROM LUCES_wq as lwq INNER JOIN public_collection as c on lwq.deploy_code = c.original_collection_code WHERE c.id <=1980 INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per, do_mg_l,depth_m) SELECT deploy_code, c.id, datetime, water_temp, ph, sp_cond, salinity, do_sat, do_mg_l, depth_m FROM LUCES_wq as lwq INNER JOIN public_collection as c on lwq.deploy_code = c.original_collection_code WHERE c.id <=1981 and c.id >1980 TEST IN EMS insert into public.wq (collection_id, date_time, original_deployment_code, water_temp, depth_m, salinity, sp_cond, do_per,do_mg_l, ph) values (1982,'4/1/2001 3:00:00','MLM20010327',17.67,1.64,30.20,46.40,72.80,5.84,7.61) psql COPY wq ( collection_id , date_time , original_deployment_code , water_temp , depth_m , salinity , sp_cond , do_per , do_mg_l, ph ) FROM '/usr/local/pgsql/ddluces.csv' CSV; COPY ohh_v8=# Deleted inserted records and tried to do in Access with ' ' around datetime. Access gives type mismatch. Tried queryw/out. Now it works. Should we always concatenate to insert date_time? Now all the sudden this works. INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per, do_mg_l,depth_m) SELECT deploy_code, c.id, datetime, water_temp, ph, sp_cond, salinity, do_sat, do_mg_l, depth_m FROM LUCES_wq as lwq INNER JOIN public_collection as c on lwq.deploy_code = c.original_collection_code WHERE c.id >=1982
I'm sorry, but I attached the incorrect file the first time!
We are using MS Access as a frontend to import many water quality records into postgresql v. 8.0.2 on Red Hat 3
Enterprise.
When importing a table of 40,000+ records something is going wrong with the timestamp field only for certain records.
The records are listed below.
MS Access Details:
DATA TO IMPORT FROM LOCAL MSACCESS Table into linked ODBC pg table:
ID original_deployment_code date_time water_temp depth_m salinity sp_cond do_per
do_mg_l ph
1 MLM20010327 2001-04-01 02:00:00 17.860001 1.49352 30.1 46.299999 80.400002
6.42 7.64
2 MLM20010327 2001-04-01 02:30:00 17.700001 1.61544 30.200001 46.400002 78.699997 6.3
7.64
3 MLM20010327 2001-04-01 03:00:00 17.67 1.64592 30.200001 46.400002 72.800003
5.84 7.62
4 MLM20010327 2001-04-01 03:30:00 17.639999 1.524 30.1 46.299999 79.300003
6.36 7.61
Here's the SQL run from Access:
INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per,
do_mg_l,depth_m )
SELECT original_deployment_code, c.id, lwq.date_time, water_temp, ph, sp_cond, salinity, do_per, do_mg_l, depth_m
FROM DD5 AS lwq INNER JOIN public_collection AS c ON lwq.original_deployment_code=c.original_collection_code;
I get a uniqueness constraint error.
Here are the Postgresql table details:
CREATE TABLE "public"."wq" (
"id" SERIAL,
"collection_id" INTEGER NOT NULL,
"date_time" TIMESTAMP(0) WITH TIME ZONE NOT NULL,
"original_deployment_code" VARCHAR(20),
"water_temp" NUMERIC(28,6),
"depth_m" NUMERIC(28,6),
"salinity" NUMERIC(28,6),
"sp_cond" NUMERIC(28,6),
"do_per" NUMERIC(28,6),
"do_mg_l" NUMERIC(28,6),
"ph" NUMERIC(28,6),
"turbidity" NUMERIC(28,6),
"chlorophyll_ug_l" NUMERIC(28,6),
"orp_mv" NUMERIC(28,6),
"reviewed" BOOLEAN DEFAULT false NOT NULL,
"date_entered" TIMESTAMP(0) WITH TIME ZONE NOT NULL,
"date_updated" TIMESTAMP(0) WITH TIME ZONE NOT NULL,
"entered_by" VARCHAR(50) NOT NULL,
"updated_by" VARCHAR(50) NOT NULL,
CONSTRAINT "wq_pkey" PRIMARY KEY("id"),
CONSTRAINT "fk_collection" FOREIGN KEY ("collection_id")
REFERENCES "public"."collection"("id")
ON DELETE NO ACTION
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH OIDS;
COMMENT ON TABLE "public"."wq"
IS 'This is continuous wq. Unique constraint is on collection and date_time. Do we need fields for raw and
interpolatedvalues???';
COMMENT ON COLUMN "public"."wq"."collection_id"
IS 'fk to collection table. ';
COMMENT ON COLUMN "public"."wq"."date_time"
IS 'Date that the sample was taken. This is usually in 30 minute increments per collection.';
COMMENT ON COLUMN "public"."wq"."original_deployment_code"
IS '???';
COMMENT ON COLUMN "public"."wq"."do_per"
IS 'Calculate this in a trigger?';
COMMENT ON COLUMN "public"."wq"."reviewed"
IS 'This defaults to false and must be set manually in order to verify entries. Possibly setup a view to filter these
out.';
COMMENT ON COLUMN "public"."wq"."date_entered"
IS 'Triggered value upon insert. Will use current_timestamp unless specified.';
COMMENT ON COLUMN "public"."wq"."date_updated"
IS 'Triggered value upon update. Will use current_timestamp.';
COMMENT ON COLUMN "public"."wq"."entered_by"
IS 'Triggered value upon insert. Will use current_user unless otherwise specified.';
COMMENT ON COLUMN "public"."wq"."updated_by"
IS 'Triggered value upon update. Will use current_user.';
CREATE UNIQUE INDEX "wq_unique_key" ON "public"."wq"
USING btree ("collection_id", "date_time");
CREATE TRIGGER "new_wq_trig" BEFORE INSERT
ON "public"."wq" FOR EACH ROW
EXECUTE PROCEDURE "public"."new_record_logger"();
CREATE TRIGGER "update_wq_trig" BEFORE UPDATE
ON "public"."wq" FOR EACH ROW
EXECUTE PROCEDURE "public"."update_record_logger"();
****new_record_logger trigger****
BEGIN
if new.date_entered is null then
new.date_entered := current_timestamp;
end if;
if new.entered_by is null then
new.entered_by := current_user;
end if;
if new.date_updated is null then
new.date_updated := current_timestamp;
end if;
if new.updated_by is null then
new.updated_by := current_user;
end if;
return new;
END;
RESULTS:
Taking the uniqueness constraint off allows import and the above inserted data is below (Notice that 2:00 and 2:30
changedto 3:00 and 3:30 during the insert):
id collection_id date_time original_deployment_code water_temp depth_m salinity sp_cond
do_per do_mg_l ph turbidity chlorophyll_ug_l orp_mv reviewed date_entered date_updated
entered_by updated_by
204414 1982 2001-04-01 03:30:00 MLM20010327 17.639999 1.524 30.1 46.299999
79.300003 6.36 7.61 0 2005-07-27 14:28:39 2005-07-27
14:28:39 ddabney ddabney
204413 1982 2001-04-01 03:00:00 MLM20010327 17.67 1.64592 30.200001 46.400002
72.800003 5.84 7.62 0 2005-07-27 14:28:39 2005-07-27
14:28:39 ddabney ddabney
204412 1982 2001-04-01 03:30:00 MLM20010327 17.700001 1.61544 30.200001 46.400002
78.699997 6.3 7.64 0 2005-07-27 14:28:39 2005-07-27
14:28:39 ddabney ddabney
204411 1982 2001-04-01 03:00:00 MLM20010327 17.860001 1.49352 30.1 46.299999
80.400002 6.42 7.64 0 2005-07-27 14:28:39 2005-07-27
14:28:39 ddabney ddabney
I'm assuming this is a bug.
"David Dabney" <David.Dabney@noaa.gov> writes: > I'm assuming this is a bug. Perhaps, but there is absolutely no chance of anyone reproducing the problem from the information you've offered. You say "it fails when I try to insert data from table DD5", but you give us no clue about what data is in DD5. There is some generic advice about how to create a useful bug report here: http://www.postgresql.org/docs/8.0/static/bug-reporting.html The short and sweet version of it is "give us a SQL script that delivers a wrong result" ... regards, tom lane
Tom, Thanks for the quick reply and I'm sorry I wasn't explicit enough in my description. At the top of the attachment there is this section which is the piece of data from DD5 that is causing the problem: **************** MS Access Details: DATA TO IMPORT FROM LOCAL MSACCESS Table into linked ODBC pg table: ID original_deployment_code date_time water_temp depth_m salinity sp_cond do_per do_mg_l ph 1 MLM20010327 2001-04-01 02:00:00 17.860001 1.49352 30.1 46.299999 80.400002 6.42 7.64 2 MLM20010327 2001-04-01 02:30:00 17.700001 1.61544 30.200001 46.400002 78.699997 6.3 7.64 3 MLM20010327 2001-04-01 03:00:00 17.67 1.64592 30.200001 46.400002 72.800003 5.84 7.62 4 MLM20010327 2001-04-01 03:30:00 17.639999 1.524 30.1 46.299999 79.300003 6.36 7.61 ******************** I needed to get this data in, so I tried to manually correct the invalid date_time field after inserting and then put the uniqueness constraint back on. From EMS I tried to change the times from 3:30 and 3:00 to 2:30 and 2:00. When I refresh the data it now shows 1:30 and 1:00 for these records! I then tried to change 1:30 and 1:00 to 2:30 and 2:00 and it went back to 3:30 and 3:00. So I backed up and tried to import directly from psql. The same results happen as when I did it from MS Access. I was driving home and realized 4/1/2001 was probably when daylight savings time changed.....and it was. So I'm not sure if this is a bug or not. Regards, Dave Tom Lane wrote: >"David Dabney" <David.Dabney@noaa.gov> writes: > > >>I'm assuming this is a bug. >> >> > >Perhaps, but there is absolutely no chance of anyone reproducing the >problem from the information you've offered. You say "it fails when >I try to insert data from table DD5", but you give us no clue about >what data is in DD5. > >There is some generic advice about how to create a useful bug report >here: >http://www.postgresql.org/docs/8.0/static/bug-reporting.html > >The short and sweet version of it is "give us a SQL script that >delivers a wrong result" ... > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 2: Don't 'kill -9' the postmaster > >
The more I thought about this I realized it's not a bug and I'm sorry to have perstered the list. The fact that the data logger was deployed before and after a time change and it was not setup to update itself created this problem. I just have to push forward all the times after the change and then import the data. We've been trying to get the scientists to use UTC.....maybe this will help them understand why they should be doing that. Thanks, Dave PS Postgres rocks. Please keep up the good work! David Dabney wrote: > Tom, > > Thanks for the quick reply and I'm sorry I wasn't explicit enough in > my description. At the top of the attachment there is this section > which is the piece of data from DD5 that is causing the problem: > > **************** > MS Access Details: > > DATA TO IMPORT FROM LOCAL MSACCESS Table into linked ODBC pg table: > ID original_deployment_code date_time water_temp > depth_m salinity sp_cond do_per do_mg_l ph > 1 MLM20010327 2001-04-01 02:00:00 17.860001 > 1.49352 30.1 46.299999 80.400002 6.42 7.64 > 2 MLM20010327 2001-04-01 02:30:00 17.700001 > 1.61544 30.200001 46.400002 78.699997 6.3 7.64 > 3 MLM20010327 2001-04-01 03:00:00 17.67 > 1.64592 30.200001 46.400002 72.800003 5.84 7.62 > 4 MLM20010327 2001-04-01 03:30:00 17.639999 > 1.524 30.1 46.299999 79.300003 6.36 7.61 > ******************** > > I needed to get this data in, so I tried to manually correct the > invalid date_time field after inserting and then put the uniqueness > constraint back on. From EMS I tried to change the times from 3:30 > and 3:00 to 2:30 and 2:00. When I refresh the data it now shows 1:30 > and 1:00 for these records! I then tried to change 1:30 and 1:00 to > 2:30 and 2:00 and it went back to 3:30 and 3:00. > > So I backed up and tried to import directly from psql. The same > results happen as when I did it from MS Access. > > I was driving home and realized 4/1/2001 was probably when daylight > savings time changed.....and it was. So I'm not sure if this is a bug > or not. > Regards, > > Dave > > Tom Lane wrote: > >> "David Dabney" <David.Dabney@noaa.gov> writes: >> >> >>> I'm assuming this is a bug. >>> >> >> >> Perhaps, but there is absolutely no chance of anyone reproducing the >> problem from the information you've offered. You say "it fails when >> I try to insert data from table DD5", but you give us no clue about >> what data is in DD5. >> >> There is some generic advice about how to create a useful bug report >> here: >> http://www.postgresql.org/docs/8.0/static/bug-reporting.html >> >> The short and sweet version of it is "give us a SQL script that >> delivers a wrong result" ... >> >> regards, tom lane >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: Don't 'kill -9' the postmaster >> >> > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
David Dabney wrote: > The more I thought about this I realized it's not a bug and I'm sorry to > have perstered the list. The fact that the data logger was deployed > before and after a time change and it was not setup to update itself > created this problem. I just have to push forward all the times after > the change and then import the data. We've been trying to get the > scientists to use UTC.....maybe this will help them understand why they > should be doing that. The other thing you should do is use "timestamp with time zone" to store your timestamps. Otherwise, you aren't storing an absolute time at all. -- Richard Huxton Archonet Ltd