Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Problems with Timestamp with Time Zone

  1. #1

    Default Problems with Timestamp with Time Zone

    I'm reading two identical tables from two different sources. One is a Postgres table, the other one is Oracle.

    One column (let's say COL1) is a TIMESTAMP WITH TIME ZONE.

    I want to compare the content of the two tables, in order to see if there are missed rows. So, I'm using a "Merge Rows (diff)" step. The problem is that the "Table Input" step reads correctly COL1 from PostgreS as a timestamp ('2018/08/20 06:43:40.000000000'), but it reads COL1 from Oracle as a String ('2018-08-20 06:43:40.0 CET').

    Is there a way to read COL1 as a TIMESTAMP WITH TIME ZONE also from Oracle?

    Thank you!

  2. #2
    Join Date
    Aug 2016
    Posts
    235

    Default

    Quote Originally Posted by uilli View Post
    I'm reading two identical tables from two different sources. One is a Postgres table, the other one is Oracle.

    One column (let's say COL1) is a TIMESTAMP WITH TIME ZONE.

    I want to compare the content of the two tables, in order to see if there are missed rows. So, I'm using a "Merge Rows (diff)" step. The problem is that the "Table Input" step reads correctly COL1 from PostgreS as a timestamp ('2018/08/20 06:43:40.000000000'), but it reads COL1 from Oracle as a String ('2018-08-20 06:43:40.0 CET').

    Is there a way to read COL1 as a TIMESTAMP WITH TIME ZONE also from Oracle?

    Thank you!
    I don't know about Oracle specifically, but sometimes it can be difficult or even impossible to change what data types a query returns or is interpreted as. Why don't you convert the String to TimeStamp instead?

  3. #3
    Join Date
    May 2016
    Posts
    255

    Default

    You'll might have to transform the dates, whether in the transformation or while reading the date columns in the Table input step, instead of doing a SELECT * FROM table or SELECT col1 FROM table, you'll have to format the col1 you are extracting, something like SELECT TO_TIMESTAMP_TZ(col1) FROM table, and you'll have to adapt it so it matches (this won't work for your purposes, but you'll have to read the Oracle documentation).
    Probably you'll have to transform the date to string so you're able to control the formats.
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  4. #4

    Default

    Thank you for your replies.

    Maybe I have not been clear enough, or I have not understood your answers.

    My tables are identical, but one is Postgres (P_tab), the other one is Oracle (O_tab).

    P_tab.col1 and O_tab.col1 are both TIMESTAMP WITH TIME ZONE. The problem is that "Table Input" reads P_tab.col1 as a TIMESTAMP, but it reads O_tab.col1 as a STRING. And I should compare these attributes.

  5. #5
    Join Date
    Aug 2016
    Posts
    235

    Default

    Quote Originally Posted by uilli View Post
    Maybe I have not been clear enough, or I have not understood your answers.
    That's exactly how I understood the situation. Either you have to try and manipulate the return type in the query using "CAST" or similar (can be difficult, unpredictable or impossible), or you simply convert the type as you see fit.

    I prefer using Strings instead of dates to have complete control, but this is less useful if you need to do time zone operations.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2017 Pentaho Corporation. All Rights Reserved.