Thread: data import: 12-hour time w/o AM/PM
I am importing data in CSV format. My technique is to COPY the data into interim tables (starting with a "z") where I analyze them, clean up inconsistencies; eventually I will populate the "proper" tables with these data. (In the example below: COPY CSV into zevent, from there populate event.) The time field of the event data is givng me a headache: it's in 12h format but without the AM/PM qualifier! CREATE TABLE zevent ( id integer, previous integer, "next" integer, location_id integer NOT NULL, datum date NOT NULL, otime character varying(5)NOT NULL ) CREATE TABLE event ( id integer NOT NULL, location_fk integer NOT NULL, otime timestamp without time zone NOT NULL, next_fk integer, previous_fkinteger, CONSTRAINT event_pkey PRIMARY KEY (id), CONSTRAINT next_event_fk FOREIGN KEY (next_fk) REFERENCESevent (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT previous_event_fk FOREIGN KEY (previous_fk) REFERENCES event (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) From the date and time I want to create a timestamp. I know that - the events take place during the day, say between 10:30 and 22:30 - it's always a set of events at one location spaced about 30min apart - the imported data are chained (have a link to previous/next event) Have you got any idea how I could tackle this problem -- Best Regards, Tarlika Elisabeth Schmitz
Howdy, Tarlika. If the data doesn't bring the AM/PM how are you supposed to distinguish between,say, 10pm and 10am ? Does it always start at 10:30am? So 10am never arises? Best, Oliveiros ----- Original Message ----- From: "Tarlika Elisabeth Schmitz" <postgresql3@numerixtechnology.de> To: <pgsql-sql@postgresql.org> Sent: Tuesday, February 08, 2011 6:14 PM Subject: [SQL] data import: 12-hour time w/o AM/PM I am importing data in CSV format. My technique is to COPY the data into interim tables (starting with a "z") where I analyze them, clean up inconsistencies; eventually I will populate the "proper" tables with these data. (In the example below: COPY CSV into zevent, from there populate event.) The time field of the event data is givng me a headache: it's in 12h format but without the AM/PM qualifier! CREATE TABLE zevent ( id integer, previous integer, "next" integer, location_id integer NOT NULL, datum date NOT NULL, otime character varying(5)NOT NULL ) CREATE TABLE event ( id integer NOT NULL, location_fk integer NOT NULL, otime timestamp without time zone NOT NULL, next_fk integer, previous_fkinteger, CONSTRAINT event_pkey PRIMARY KEY (id), CONSTRAINT next_event_fk FOREIGN KEY (next_fk) REFERENCESevent (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT previous_event_fk FOREIGN KEY (previous_fk) REFERENCES event (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) From the date and time I want to create a timestamp. I know that - the events take place during the day, say between 10:30 and 22:30 - it's always a set of events at one location spaced about 30min apart - the imported data are chained (have a link to previous/next event) Have you got any idea how I could tackle this problem -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
On Tuesday 8. February 2011 19.14.00 Tarlika Elisabeth Schmitz wrote: > From the date and time I want to create a timestamp. > I know that > - the events take place during the day, say between 10:30 and 22:30 > - it's always a set of events at one location spaced about 30min apart > - the imported data are chained (have a link to previous/next event) > > > Have you got any idea how I could tackle this problem Add 12 hours if time is in the interval 1:00-10:30? regards, Leif
On 9 February 2011 07:14, Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de> wrote: > From the date and time I want to create a timestamp. > I know that > - the events take place during the day, say between 10:30 and 22:30 > - it's always a set of events at one location spaced about 30min apart > - the imported data are chained (have a link to previous/next event) Any chance of seeing actual data? Might be a job for awk/perl rather than the RDBMS. Cheers, Andrej
On Wed, 9 Feb 2011 08:21:47 +1300 Andrej <andrej.groups@gmail.com> wrote: >On 9 February 2011 07:14, Tarlika Elisabeth Schmitz ><postgresql3@numerixtechnology.de> wrote: >> From the date and time I want to create a timestamp. >> I know that >> - the events take place during the day, say between 10:30 and 22:30 >> - it's always a set of events at one location spaced about 30min >> apart >> - the imported data are chained (have a link to previous/next event) > >Any chance of seeing actual data? Might be a job for awk/perl rather >than the RDBMS. attached small CSV sample: at both locations, events start just after twelve o'clock (noon). -- Best Regards, Tarlika Elisabeth Schmitz
Attachment
On Tue, 8 Feb 2011 18:38:44 -0000 "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt> wrote: >----- Original Message ----- >From: "Tarlika Elisabeth Schmitz" <postgresql3@numerixtechnology.de> >To: <pgsql-sql@postgresql.org> >Sent: Tuesday, February 08, 2011 6:14 PM >Subject: [SQL] data import: 12-hour time w/o AM/PM > > >I am importing data in CSV format. My technique is to COPY the data >into interim tables (starting with a "z") where I analyze them, clean >up inconsistencies; eventually I will populate the "proper" tables with >these data. (In the example below: COPY CSV into zevent, from there >populate event.) > >The time field of the event data is givng me a headache: it's in 12h >format but without the AM/PM qualifier! > >Howdy, Tarlika. > >If the data doesn't bring the AM/PM how are you supposed to >distinguish between,say, 10pm and 10am ? Does it always start at >10:30am? So 10am never arises? > >Best, >Oliveiros > On a given day, between 6-9 events are staged, about 1/2 hour apart. If the set of events spans 12:00, I definitely know that this isn't midnight. I also know the order within a set. Looking at a particular location, which contributes to 80% of the data, there have been no events at 10:?? ever (so far). Looking at it as a human being I can decide in a fraction of a second whether it's AM or PM. I just can't think of an automated procedure how to transform this half-baked 12-hour nonsense into a time.