Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: Exporting table data with time column

    Hello All,
    I have a table in db2 having structure
    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- ------
    DATIME SYSIBM TIMESTAMP 10 0 No
    TIMEDIT SYSIBM TIME 3 0 No
    LANG SYSIBM SMALLINT 2 0 No
    ALERT SYSIBM SMALLINT 2 0 No

    I want to export the data of this table to text file and then to import it to Postgresql database. The problem is when I export data from this table with export command as.
    Export into table.txt of del modified by timestamp=\"YYYY.MM.DD HH.MM.SS\" select * from table.
    everything is fine expect time format into exported file. I am getting time data in format 09.44.22(instead of 09:44:22 or 094422) which is not accepted by postgresql. Can anybody help to work around it so that I can insert table data from text file into postgresql from db2.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    From the Command Reference manual:

    The export utility normally writes
    date data in YYYYMMDD format
    char(date) data in ″YYYY-MM-DD″ format
    time data in ″HH.MM.SS″ format
    time stamp data in ″YYYY-MM-DD-HH. MM.SS.uuuuuu″ format

    Data contained in any datetime columns specified in the SELECT statement
    for the export operation will also be in these formats.

    So it is doing as expected with the TIME data. I can't find any MODIFY option for time. However, one option is to change you Select * to a Select column list and use the CHAR function to force JIS format.

    Export into table.txt of del modified by timestamp=\"YYYY.MM.DD HH.MM.SS\" SELECT DATIME, CHAR(TIMEDIT, JIS), LANG, ALERT FROM table

    This will get you a time value in the format 09:44:22

  3. #3
    Join Date
    Nov 2011
    Posts
    2

    Thumbs up

    Hi Stealth,
    Thank you very much. It worked fine. Forcing JIS generated the desired Time format. Thanks you very much once again...


    Regards,
    Paramjit Joshi

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •