pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddata import: 12-hour time w/o AM/PM
6+ messages / 4 participants
[nested] [flat]
* data import: 12-hour time w/o AM/PM
@ 2011-02-08 18:14 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
0 siblings, 3 replies; 6+ messages in thread
From: Tarlika Elisabeth Schmitz @ 2011-02-08 18:14 UTC (permalink / raw)
To: pgsql-sql
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_fk integer,
CONSTRAINT event_pkey PRIMARY KEY (id),
CONSTRAINT next_event_fk FOREIGN KEY (next_fk)
REFERENCES event (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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: data import: 12-hour time w/o AM/PM
@ 2011-02-08 18:25 Leif Biberg Kristensen <leif@solumslekt.org>
parent: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
2 siblings, 0 replies; 6+ messages in thread
From: Leif Biberg Kristensen @ 2011-02-08 18:25 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: data import: 12-hour time w/o AM/PM
@ 2011-02-08 18:38 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
2 siblings, 1 reply; 6+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2011-02-08 18:38 UTC (permalink / raw)
To: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>; pgsql-sql
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_fk integer,
CONSTRAINT event_pkey PRIMARY KEY (id),
CONSTRAINT next_event_fk FOREIGN KEY (next_fk)
REFERENCES event (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
)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: data import: 12-hour time w/o AM/PM
@ 2011-02-08 19:21 Andrej <andrej.groups@gmail.com>
parent: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
2 siblings, 1 reply; 6+ messages in thread
From: Andrej @ 2011-02-08 19:21 UTC (permalink / raw)
To: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: data import: 12-hour time w/o AM/PM
@ 2011-02-08 19:27 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
parent: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 0 replies; 6+ messages in thread
From: Tarlika Elisabeth Schmitz @ 2011-02-08 19:27 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: data import: 12-hour time w/o AM/PM
@ 2011-02-08 19:55 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
parent: Andrej <andrej.groups@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tarlika Elisabeth Schmitz @ 2011-02-08 19:55 UTC (permalink / raw)
To: pgsql-sql
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
Attachments:
[text/csv] event.csv (521B, ../../20110208195516.72664f66@dick.coachhouse/2-event.csv)
download | inline:
location_id datum otime id previous next
253 2010-09-08 01:05 942 941 943
253 2010-09-08 01:40 943 942 944
253 2010-09-08 02:10 944 943 945
253 2010-09-08 02:45 945 944 946
253 2010-09-08 03:20 946 945 947
253 2010-09-08 03:50 947 946 (null)
253 2010-09-08 12:30 941 (null) 942
9062 2010-09-08 01:00 858 857 859
9062 2010-09-08 01:35 859 858 860
9062 2010-09-08 02:05 860 859 861
9062 2010-09-08 02:40 861 860 862
9062 2010-09-08 03:15 862 861 863
9062 2010-09-08 03:45 863 862 (null)
9062 2010-09-08 12:25 857 (null) 858
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-02-08 19:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-08 18:14 data import: 12-hour time w/o AM/PM Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
2011-02-08 18:25 ` Leif Biberg Kristensen <leif@solumslekt.org>
2011-02-08 18:38 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-02-08 19:27 ` Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
2011-02-08 19:21 ` Andrej <andrej.groups@gmail.com>
2011-02-08 19:55 ` Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox