Hi team, we have a oracle data source, we create base view on this source, there is a table from this oracle source, and the value for its one timestamp column(called post_timestamp) is Japan timestamp, and I want convert it to US east timestamp , how to do it?
Approches I tried:
1.change the datatype of "post_timestamp" in the base view to "timestamptz", but when save this change, error prompt ("The field has an incompatible subtype for type "timestamptz":TIMESTAMP")
2.Open the base view, Go to "Option" --> "Search method" --> set the "Default i18n" to "us_east", save the view, but seems nonthing happend, when I search this column, the value still Japan timestap. see below:
current value of "post_timstamp": 2020-04-04 07:23:08.120218
my expecte value of "post_timstamp": 2020-04-03 18:23:08.120218
Something like below same function as oralce (the bold and italics part):
***FROMTZ(CAST(A.posttimstamp AS TIMESTAMP),'Japan')AT TIME ZONE 'US/Eastern' "US/Eastern" ***
I hope this funtion is also oralce compatible, since I need this query all pull down to the source. Looking forward to your reply.