Finding if x minutes have passed

Question

So, I need to know if 20 minutes have passed, but, working with timestamps is proving that I'm not good at pl/sql

This is my query

SELECT
systimestamp currenttime,
lockeddtts original,
lockeddtts + to_number( (
    SELECT
        value
    FROM
        configurationTable
    WHERE
        key = 'UNLOCK_TIME'
) ) * INTERVAL '1' MINUTE added20,
CASE
        WHEN systimestamp > lockeddtts + to_number( (
            SELECT
                value
            FROM
                configurationTable
            WHERE
                key = 'UNLOCK_TIME'
        ) ) * INTERVAL '1' MINUTE THEN 1
        ELSE 0
    END
    passed20
FROM
    xTable

And this is the result I get

From my understanding and my logic, currentTime is indeed greater than original time plus 20 minutes, still, oracle tells its not

What am I missing?

I'm doing this oracle wise in order to use oracle server time rather than backend server so not to mess with time zones, and still, it's not cooperating with me

Edit:

Thanks to @Kjetil S. for the answer, I just need to tweak it a little for it to work, converting the date sumatory to timestamp and it's working

This was my end query

SELECT
        CASE
            WHEN systimestamp > to_timestamp(lockeddtts + to_number( (
                SELECT
                    value
                FROM
                    configurationTable
                WHERE
                    key = 'UNLOCK_TIME'
            ) ) / ( 24 * 60 )) THEN 1
            ELSE 0
        END
    passed20
FROM
    xTable

Proof it works

Thanks and hope this works for someone else

https://stackoverflow.com/questions/257324/oracle-how-to-add-minutes-to-a-timestamp — Randy, Apr 03 '18 at 20:43
@Randy sorry, I don't see how this is relevant. I'm asking about why the compare fails, not about adding time to a timestamp field — CJLopez, Apr 03 '18 at 20:56
@âńōŋŷXmoůŜ sorry, I don't see the need to, already showing the data I need to test against in xTable (original column in result query on image) , and for configurationTable, it's just a mere '20' string that I convert to number in order to add it — CJLopez, Apr 03 '18 at 21:10
The end query you posted contains `to_timestamp(lockedtts + to_number(...))`. This can only work due to sheer luck, since `to_timestamp` takes VARCHAR argument. — wolfrevokcats, Apr 03 '18 at 21:43

score 2 · Accepted Answer · answered Apr 03 '18 at 21:12

I'm sure interval can be used somehow, but the "classic" Oracle way I think is to add the number of days to a TIMESTAMP or DATE column. When handling minutes instead of days, just divide by 24*60 which is the number of minutes in a day. Something like:

with c as (select value minutes from configurationTable where key='UNLOCK_TIME')
select
  systimestamp                   currenttime,
  lockeddtts                     original,
  lockeddtts+minutes/(24*60)     lockeddtts_plus_minutes,
  case when systimestamp >= lockeddtts+minutes/(24*60) then 1 else 0 end passed
from xTable,c

Using with like this saves you from having to write select ... from configurationTable twice.

Thanks this worked indeed. BTW, I don't need to use the with, in my original query, I only do the check, never retrieve the data. I only added the data retrieval for testing and debugging purpose. Please check my edit for clarification — CJLopez, Apr 03 '18 at 21:22

Finding if x minutes have passed

1 Answers1