Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2013
    Posts
    1

    Question Unanswered: Converting Character to Timestamp With Time Zone

    Hi ,

    I need to store a character value from a file into a column which should hold timestamp details with time zone.

    Sample Data from the file
    2012-10-15T16:03:00+01:00

    DB2 Details:
    Version : DB2 v9.7.0.8
    Fix pack : 8


    It would be very helpful if I could get to know how to achieve this while using a loader in DB2

    Thanks In advance!!

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Current versions (up to v10.5) of DB2 for LUW does not support the datatype "timestamp with time zone". But current versions of DB2 for Z/OS supports this data type. If your target database is hosted on Z/OS then refer to the DB2 for Z/OS documentation for details of the literal values for that datatype.

    If your target database is hosted on some flavour of Unix or Windows maybe you need two columns, one for the timestamp (in utc) and another for the offset from utc.

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    You can use 2 columns as was mentioned and use some dummy for insert statements or IMPORT commands:

    Code:
    create table test_ts (ts timestamp, tz dec(6)) in userspace1;
    
    create view test_ts_v (t) as select cast(null as varchar(25)) from test_ts;
    
    create trigger test_ts_v_iir
    instead of insert on test_ts_v
    referencing new as n
    for each row
    insert into test_ts (ts, tz)
    values (
      timestamp(translate(substr(t, 1, 19), '-.', 'T:'))
    , dec(replace(substr(t, 20), ':', '') || '00', 6)
    );
    
    insert into test_ts_v values '2012-10-15T16:03:00+01:00';
    
    select t.*, ts + tz as ts_loc from test_ts t;
    
    TS                         TZ       TS_LOC                    
    -------------------------- -------- --------------------------
    2012-10-15-16.03.00.000000   10000, 2012-10-15-17.03.00.000000
    Note that you will not be able to use LOAD utility to load data into this view.
    If you want to use LOAD, then you can load some staging table first and then use LOAD FROM CURSOR for loading into your table.

    Another option is use a customized application if you load not from ixf file and have some shell scripting experience.
    See Moving data using a customized application (user exit).
    Regards,
    Mark.

Posting Permissions

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