Hi
Currently running APEX 23.2 on OCI. Application is very large and historical, it is a mighty beast!
On our tables we have a CREATED_DATE column that stores the CURRENT_DATE of when the record was created. We're in the UK, so observe Daylight Savings. So for 5 months of the year this is UTC and the 7 it's UTC+1. In our IRs we want to display records created in the last X days, which can be achieved easily with a filter. However, for 7 months of the year we have to wait an hour before records appear as they are seen as being in the future.
Using Debug I can see that the query for the IR is along the lines of:
select i.*, count(*) over () as APEX$TOTAL_ROW_COUNT
from (select "APEX_ROW_PK","STORY_HEADER","STORY_TEXT","CREATED_DATE","EXPIRY_DATE"
from(select /*+ qb_name(apex$inner) */d."APEX_ROW_PK",d."STORY_HEADER",d."STORY_TEXT",d."CREATED_DATE",d."EXPIRY_DATE"
from(select ('')"APEX_ROW_PK","STORY_HEADER","STORY_TEXT","CREATED_DATE","EXPIRY_DATE"
from (SELECT n.story_header
,n.story_text
,n.created_date
,n.created_date + n.expiry_length_in_days AS expiry_date
FROM news n
)d
)d
)i
)i where 1=1 and rownum<=10001
and "CREATED_ON"between sysdate-numtodsinterval(:apex$f1, :apex$f2) and sysdate
order by "CREATED_ON" desc nulls first;
The key problem line being:
and "CREATED_ON"between sysdate-numtodsinterval(:apex$f1, :apex$f2) and sysdate
Here sysdate is used, which is UTC. Is there any way that this can use CURRENT_DATE instead? Or is there a simple way of handling this?
I'm sure this must have been asked and answered before, but I can't find anything. We can't be the only ones that store dates as DATE and have to handle Daylight Savings. How do others handle this?
Many thanks for any help or suggestions.
Kind regards
Sara