You can translate the question and the replies:

How to convert Japan timestamp to US EAST timestamp

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): select A.post_timstamp, ***FROMTZ(CAST(A.posttimstamp AS TIMESTAMP),'Japan')AT TIME ZONE 'US/Eastern' "US/Eastern" *** from base_view_a I hope this funtion is also oralce compatible, since I need this query all pull down to the source. Looking forward to your reply.
local timestamp convert
user
17-07-2020 06:24:15 -0400
code

5 Answers

Hello, I am using the below function in Denodo to convert the timezone "convert_timezone(text sourcetimezone, text targettimezone, timestamp timestamp): timestamp" This function allows you to transform Timestamp values from one timezone to another. As this function is available in Denodo’s update starting 20200310, I would upgrade the platform to this particular update in order to use this function If you are a customer with a valid support user, you can download the Denodo-v70-update-20200310 from the [Denodo support site](https://support.denodo.com/). Hope this helps.
Denodo Team
17-07-2020 17:40:44 -0400
code
thank you providing this, but based on my tiral, this fuction seems not qork , see below: select "JAPAN_STAMP_SEC", convert_timezone('jp','us_east', "JAPAN_STAMP_SEC") as us_timezone from base_view1 where "KEY" = 1816497 result: JAPAN_STAMP_SEC us_timezone 2020-04-14 03:41:15.527744 2020-04-14 03:41:15.527744 seems nothing changed. BTW, I think this function is not Oracle supported, which means it cannot pushed down to oracle side to calculation, all data needs to be sent to denodo side. Ps, I ran this query on client 20190903 and Sever version is 20200310. I can see this function is available on menu "Help" --> "Function_List"
user
 Edited on: 08-09-2020 01:28:06 -0400
Hello, I was able to convert to US east time zone from Japan time zone in an Oracle view by using the below function “convert_timezone('Japan', 'America/New_York',<column_name>)” I.e by using ‘Japan’ as sourcetimezone and ‘America/New_York’ as targettimezone. Also, if you are a valid support user and need further assistance for this scenario, you can open a Support case and our Support Team will help you further. Hope this helps!
Denodo Team
20-07-2020 17:34:34 -0400
code
Thanks. Do we have some instruction about "sourcetimezone" and "targettimezone" defined, forexample about the exact name of different timezone that we can follow: “convert_timezone(text sourcetimezone, text targettimezone, timestamp timestamp): timestamp”
user
 Edited on: 08-09-2020 01:28:06 -0400
Hi, This is a new functionality that was recently released and documentation details will be added in the future but are not yet available. For the exact value of the time zone of the location to be used in this function, you can match the input value with the timezone as listed under the “Time Zone for published web services” [In the vdp admin tool, navigate to Administration -> Server Configuration -> Default I18n -> Time Zone for published web services]. Hope this helps!
Denodo Team
27-07-2020 14:16:23 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here