0

Im trying to execute

select * from WMERROR where INSERTTIMESTAMP > SYSTIMESTAMP - (5/(24*60)).

Oracle 12C DB is in CEST Timezone.so i tried SQL statement on both client side(INDIA) and on machine located at CEST timezone?? Why SQL is Not yeildng results ,which is executed without errors.??

help me to resolve this usecase. Thanks

Naveena K N
  • 11
  • 3

1 Answers1

2

CURRENT_TIMESTAMP - 5/(24*60) will convert the timestamp to a date to perform the subtraction of the fractions of days so is effectively:

CAST( CURRENT_TIMESTAMP AS DATE ) - 5/(24*60)

This removes the time zone information and may cause your issues as

CASE
WHEN TIMESTAMP '2019-03-29 12:00:00+08:00'
     >= CAST( TIMESTAMP '2019-03-29 12:00:00+08:00' AS DATE )
THEN 1
ELSE 0
END

Outputs 0.

Instead, you can use an INTERVAL literal:

select *
from   wmerror
where  INSERTTIMESTAMP >= current_timestamp  - INTERVAL '5' MINUTE;
MT0
  • 143,790
  • 11
  • 59
  • 117
  • still im not getting expected output, sql is executed without any errors. – Naveena K N Mar 29 '18 at 09:53
  • @NaveenaKN Please edit your question with a [MCVE] including DDL statements for your table, DML statements for sample data that replicates the problem and your expected results. Without this we are guessing (and you may find that producing a [MCVE] helps you to work out a solution to your own problem). – MT0 Mar 29 '18 at 09:56