Can now() function be instructed to produce time in given timezone?

[sanjeev.gour]

Hi-

We use now function to produce current time is some of our queries, it produces the timestamp in the server's current timezone, is there an easy to produce the these timestamps in a given timezone, basically in GMT. For cases where we have the date and time in the Java code, we offset the current server timezone but now() is evaluated inside Teiid, so we don't have that option. Some alternative we see are-

1. Start the JVM with user.timezone=GMT - this works but sets the timezone globally for the JVM.
2. Have a custom function to produce the in provided timezone

Is there an even simpler way?

Thanks.

Sanjeev.

[shawkins]

There is also the system function modifytimezone - Date_Time Functions - Teiid 8.10 (draft) - Project Documentation Editor

[sanjeev.gour]

Thanks, Steve. I think I overlooked this in the docs. I am wondering if I could create a custom function on top it to use it in general using the combination of now() and modifytimezone, something like now_in_gmt(). As an alternative I could always have a custom function using Java Date and sql timestamp but if there is something simpler using the existing functions, that would be nice. Any pointer in that direction?

Thanks.

Sanjeev.

[shawkins]

Unfortunately there isn't a good way to create a custom function that calls other Teiid functions as we expect the function implementation to be in java.  It would be a valid feature to alternatively allow a function to be defined by a Teiid expression rather than just java.

[sanjeev.gour]

Ok, I will use the Java way for current release and create a feature request for this to be included in the future.

Thank you.

[sanjeev.gour]

Here is the feature request-

[TEIID-3270] Allow UDFs to call Teiid functions - JBoss Issue Tracker