Skip to Main Content
Feature Request FR-2720
Product Area Page Components
Status CLOSED

23 Voters

Improve Timestamp with Time Zone Support in Interactive Reports and Interactive Grids

anton nielsen Public · Aug 30 2022

Idea Summary
This is more of a bug than a feature request. Currently when using a filter “in the last 2 hours” (or any similar date logic) on a TIMESTAMP WITH TIME ZONE (or TIMESTAMP WITH LOCAL TIME ZONE) data type, APEX IR and IG add

and "MY_COLUMN"between sysdate-numtodsinterval(:apex$f1, :apex$f2) and sysdate

This should be

and "MY_COLUMN"between systimestamp-numtodsinterval(:apex$f1, :apex$f2) and systimestamp

The use of sysdate does an implicit conversion that does not take into account any time zone information and therefor does not provide the right result (when the timestamp time zone is not the same as the database time zone).

Use Case
Every time you use a TIMESTAMP WITH TIME ZONE in an IR or IG.

Preferred Solution (Optional)
See "Idea Summary"

This idea submission is related to bug #35160648 in our bug system.

1 Comment

Comments

  • anton nielsen OP 2.6 years ago

    There is a workaround using HTML Expression and some crazy code

    with the_interval as
    (select (systimestamp - cast( sysdate as timestamp with time zone)) dts
        from dual)

    and then you have to have a display column that does
    select my_real_column,
    (my_real_column + the_interval.dts) as display_this_column
    from …, the_interval

    And then, edit the column DISPLAY_THIS_COLUMN and put #MY_REAL_COLUMN# into the HTML Expression.

    But dates and time zones are already very hard. It's much better for this to be fixed within the IR and IG engines.