GMT to Local User Time: Need Object in Query Selection

building semantic layer
1

I’ve incorporated the following code for the date objects in the universe:

Now let’s take it apart. Oracle can convert a “timestamp with time zone” data type (yes, it really is called that) to another time zone. Work inside out …

  1. to_date(‘12/15/1970’, ‘mm/dd/yyyy’) + (Tbl.LAD / 86400) returns a DATE data type.
  2. cast( DATE AS TIMESTAMP) converts it to a TIMESTAMP data type.
  3. from_tz( TIMESTAMP, ‘GMT’) converts it to a TIMESTAMP WITH TIME ZONE data type as GMT.
  4. Finally the AT LOCAL parameter switches it to the local users timezone.

However, I’m now experiencing query failures with a message “Invalid Month”. I’ve tried to recast the object as a DATE but this isn’t working. The goal is this: 1) Show the Date in the user’s Local Time Zone, and 2) Allow the same date field to be used as part of the Query Selection.

Any suggestions ?

paulo_bmc (BOB member since 2005-07-20)

2

Have you tried the NEW_TIME Oracle function to convert between Timezones.


NEW_TIME(tbl.LAD,'GMT','PST')

Sridharan :india: (BOB member since 2002-11-08)

3

I know it’s been a long time since you posted this but I’m trying to do the same thing with my universe against an Oracle db…would you still have the SQL syntax you wrote for each object?

Thank you
jl

jleblanc :us: (BOB member since 2007-05-02)