CODEWITHSUNDEEP
sqloracle-databaseplsql
CJLopez
CJLopez

Reputation: 5815

Finding if x minutes have passed

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

enter image description here

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

enter image description here

Thanks and hope this works for someone else

Upvotes: 0

Views: 242

Answers (1)

Kjetil S.
Kjetil S.

Reputation: 3787

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.

Upvotes: 2

Related Questions