Hi all,
Sorry, my mistake. In table item the date is in '2005-11-01 16:42:01.19+01'
format, while Oracle expects a regular timestamp. The tables checksum_history
and most_recent_checksum use the syntax '2008-02-14 14:23:29.894'. What is the
meaning of the digits following the seconds (.19+01 in the item table and .894
the other tables).
Postgresql dump of table item:
INSERT INTO item (item_id, submitter_id, in_archive, withdrawn,
owning_collection, last_modified) VALUES (397, 91, 0, 1, 3, '2005-11-01
16:42:01.19+01');
If we try to import this into Oracle by changing it to_date, it looks like a
similar date, but in another format. Will this still work?
SQL> INSERT INTO item (item_id, submitter_id, in_archive, withdrawn,
owning_collection, last_modifie
d) VALUES (397, 91, 0, 1, 3, to_date ('2005-11-01 16:42:01', 'yyyy-mm-dd
hh24:mi:ss'));
1 rij is aangemaakt.
SQL> select * from item;
ITEM_ID SUBMITTER_ID IN_ARCHIVE WITHDRAWN LAST_MODIFIED OWNING_COLLECTION
-----------------
397 91 0 1 01-11-05 16:42:01,000000
3
Best wishes,
Francis
-----Oorspronkelijk bericht-----
Van: Brouns, Francis [mailto:[email protected]]
Verzonden: maandag 18 januari 2010 17:18
Aan: [email protected]
Onderwerp: [Dspace-tech] Import Postgresql timestamp data into Oracle
Hi all,
We are in the process of migrating our Dspace 1.4.2 instance that is running on
Postgresql to an Oracle database. One of the (many) issues we encounter is the
TIMESTAMP. Several tables contain a field of type TIMESTAMP. In the Postgresql
database_schema.sql the type TIMESTAMP is used for most tables, except table
ITEM which uses a TIMESTAMP WITH TIMEZONE. The Oracle version of
database_schema.sql uses the TIMESTAMP type for all tables. However, the dates
exported by Postgresql all are in the form of 2008-02-14 14:23:29.894. Oracle
does not recognise this as a valid timestamp.
Is it save to convert these timestamps all to a regular date, or is the
timezone required? If so, how can we import these into Oracle?
Best wishes,
Francis Brouns
------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
DSpace-tech mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
DSpace-tech mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
