compatable datatype question

[shaunnx]

Howdy -

First time on the list and newbie with Oracle -
let me know if this isn't the right list for
novice-type questions.

Okay -

Trying to convert PostgreSQL 7.2.x tables
to Oracle 9i (9.2.0.2) tables.


On PostgreSQL, there is a datatype called 'interval'.
It's definition is the following:

[snip Pg: interval def.]

interval values can be written with the following syntax:

Quantity Unit [Quantity Unit...] [Direction]
@ Quantity Unit [Quantity Unit...] [Direction]

where: Quantity is a number (possibly signed), Unit is second,
minute, hour, day, week, month, year, decade, century,
millennium, or abbreviations or plurals of these units;
Direction can be ago or empty. The at sign (@) is optional noise.
The amounts of different units are implicitly added up with
appropriate sign accounting.

Quantities of days, hours, minutes, and seconds can be specified
without explicit unit markings. For example, '1 12:59:10' is
read the same as '1 day 12 hours 59 min 10 sec'.
The optional precision p should be between 0 and 13, and
defaults to the precision of the input liter

[/snip Pg: interval def.]

I am trying to find an Oracle equivalent, but haven't
seen anything like this.

For example, say I have a PostgreSQL table that looks
like so:

[snip PG table]
(
measure_id integer,
code_set character varying(50),
label character varying(50),
sex character(1),
age_low double precision,
age_high double precision,
event_low interval,
event_high interval,
result_required boolean
}
[/snip PG table]

The datatypes (interval and boolean) don't really
exist. Boolean, I think, I can change to just Char(1)
and ignore whatever doesn't match 't' or 'f'. But,
I need an Oracle facsimile for 'interval'. I've
tried 'interval YEAR TO MONTH' and 'DATE', but those
don't work.

This is what the data looks like:

[snip data]
1|\N|Male|M|35|64|\N|\N|f
1|\N|Female|F|45|64|\N|\N|f
3|\N|All|\N|50|999|\N|\N|f
4|\N|All|\N|18|75|-2 years|-1 years|t
2|\N|All|\N|18|75|-2 years|-1 years|f
[/snip data]

Note the last two rows. With Pg: 'interval',
I can do sum /aggregate counts with it.

Otherwise, can I *create* my own datatype in Oracle?
If so, how?

Any suggestions?

Thanks!

-X

[skhanal]

I don't think there is an equivalent of this in Oracle. You will have to implement it using trigger.

Oracle has sequence which only works with integer not with date data.

[shaunnx]

thanks for the reply:

skhanal - i just got an email from someone on this list that says i can use 'timestamp' to do this, but i am not sure how that works - or *if* it works.

does anyone know if 'timestamp' can be put in a way to allow insert / extract of data that looks like this:

[snip]

1 year
-2 years
1.5 years

[/snip]

i didn't think it was possible, but perhaps someone can enlighten me?

thanks again all!

-X