Date & Time UTC issue

building semantic layer
1

We are trying to create an object in Universe Designer that utilizes a function on the table field in the SELECT side i.e. PI_TO_GMT(DISCHARGE_DT_TM) and then on the WHERE side when used in a Query Filter it does something like

Ex: 1 - DISCHARGE _DT_TM BETWEEN PI_TO_GMT(—INPUT BEGIN—) AND PI_TO_GMT(—INPUT END—).

Right now if I drop the object in the query filter it creates the query using the function on the time nullifying the index:
Ex: 2 - PI_TO_GMT(DISCHARGE_DT_TM) BETWEEN (—INPUT BEGIN—) AND (—INPUT END—)

I would like it to do as I have in Example 1, but use the PI_TO_GMT(DISCHARGE_DT_TM) when used as an object and not a filter.

fineline74 (BOB member since 2008-08-19)

2

Are your “—INPUT BEGIN—” and "—INPUT END—"s prompts, or manually entered values in conditions?

I’m assuming the latter, in which case you should instead convert the left-hand value. That is, create a function GMT_TO_PI, if you don’t have it already, and then create a new object GMT_TO_PI(DISCHARGE _DT_TM). This can be compared to the inputted values.

joepeters :us: (BOB member since 2002-08-29)

3

[Moderator note - moved to the Semantic Layer forum]

Nick Daniels :uk: (BOB member since 2002-08-15)

4

You are correct the SELECT is PI_FROM_GMT,

and in the WHERE I’m looking for it to use PI_TO_GMT not like this

pi_to_gmt(discharge_dt_tm,‘cst’) between 5/1/15 and 5/2/15

but like this

discharge_dt_tm between pi_to_gmt(5/1/15,'cst) and pi_to_gmt(5/2/15,‘cst’)

so I don’t lose the indexing on the object and so that it converts it to display in local even though it is stored on the database in UTC. Essentially I want that fact unknown to the end user and I don’t want to use Filters or Relative Dates to do it.

fineline74 (BOB member since 2008-08-19)

5

Maybe this is an easier question how do end users currently create objects (dimensions – I don’t want filters or relative dates I can do that) that are stored in GMT on the database but can be used in the SELECT and WHERE clause so that the fact they are stored in GMT is masked from the end user and so that in where clause the conversion function to GMT does not nullify the indexing on the datetime field? This has got to be a common problem with common solutions.

fineline74 (BOB member since 2008-08-19)

6

The only way to get there is with prompts. You can’t construct an object to apply a function to an explicitly-entered value.

If your values were date only and did not include time, you could create a lookup table, and then use the pi_to_gmt function in the join.

You could also consider a function-based index.

Joe

joepeters :us: (BOB member since 2002-08-29)

7

I don’t know but this seems like there should be a simpler solution. How many people across multiple time zones utilize this solution. There is not a way to push in a function that still allows the index on the date and time to work. I like the thought of a function based index but have not read enough to determine whether that is a feasible solution or not.

fineline74 (BOB member since 2008-08-19)

8

The functionality you’re asking for is not limited to custom functions and dates. It would be nice to apply a upper() function to entered text, for example, but it’s simply not possible currently.

Sounds like a good topic for http://ideas.sap.com, though.

joepeters :us: (BOB member since 2002-08-29)