Member Avatar for samsons17

HI there,

I am doing my internship with accenture and i got my first assignment.

Basically i first thing with this task is i need to find way to modify the query so that it will pull only the data that has existed/activated more than one hour. This is the complete query :

select account_no, unixts_to_date(created_t), poid_id0, state_flag from
(
SELECT a.account_no, m.poid_id0, m.state_flag, c.created_t, row_number() OVER (PARTITION BY c.account_obj_id0 ORDER BY c.created_t DESC) rnk
FROM pone_bt_prod_inst_t m, pone_act_inst_t c, account_t a
WHERE m.state_flag IN ('0', '4', '5', '6', '7')
AND a.poid_id0 = m.account_obj_id0
AND c.prod_inst_id_id0 = m.poid_id0
AND m.service_type = '/service/ip/wimax'
AND m.entity_type = 'Plan'
AND c.state_flag NOT IN ('1', '2', '14')
ORDER BY created_t ASC
)
WHERE rnk = 1
UNION ALL
SELECT account_no, unixts_to_date(created_t), poid_id0, state_flag FROM
(
SELECT a.account_no, m.poid_id0, m.state_flag, c.created_t, row_number() OVER (PARTITION BY c.account_obj_id0 ORDER BY c.created_t DESC) rnk
FROM pone_bt_prod_inst_t m, pone_act_inst_t c, account_t a
WHERE m.state_flag IN ('16', '17', '18')
AND a.poid_id0 = m.account_obj_id0
AND c.prod_inst_id_id0 = m.poid_id0
AND m.service_type = '/service/ip/wimax'
AND m.entity_type = 'Deal'
AND c.state_flag NOT IN ('1', '2', '14')
ORDER BY created_t ASC
)
WHERE rnk = 1;

I show you the query so that you could grab the whole figure how the database looks like just in case you want to.
Anyway, from that query, I tr to play with it and I am trying to get the difference between the current time with the time in the created_t column.
As far as i got, this is the query :

SELECT TO_CHAR( unixts_to_date(created_t), 'SSSSS' )
FROM pone_bt_prod_inst_t 
WHERE ( TO_CHAR( SYSDATE, 'SSSSS' ) - TO_CHAR( unixts_to_date(created_t), 'SSSSS' ) ) > 3600;

I dont know whats wrong but it gave me the continous looping of "time" for the output.. And i know that the " 3600" is wrong, but may i know what the number i should put there as the comparison? because i want to output only data than has generated more than 1 hour, but still i dont know what format that original unix system used T___T What i mean is that, when i run the query of SELECT created_t FROM pone_bt_prod_inst_t; , it will output the time like this 1212640384 , so what is it actually?

thank you in advance for helping..

Edited by pritaeas because: Code tags
  • 3 Contributors
  • 3 Replies
  • 230 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by samsons17

Dani AI

Generated

created_t is an integer Unix timestamp (seconds since 1970-01-01), not an Oracle DATE. The earlier attempt using a TO_CHAR format that returns “seconds of day” explains the strange output — that format is not suited to epoch arithmetic. Since this is Oracle (as asked and confirmed), two practical approaches are shown below.

For best performance (keeps the condition sargable so an index on created_t can be used), compute the current epoch and compare numerically:

-- sargable: compare stored epoch seconds to current epoch minus 3600 (one hour)
SELECT account_no,
       (DATE '1970-01-01' + created_t/86400) AS created_at
FROM   pone_bt_prod_inst_t
WHERE  created_t < TRUNC((SYSDATE - DATE '1970-01-01') * 86400) - 3600;

If a DATE comparison is preferred for readability, convert the stored seconds to an Oracle DATE and compare to SYSDATE minus one hour:

-- date arithmetic: convert seconds to an Oracle DATE and compare
SELECT account_no,
       (DATE '1970-01-01' + created_t/86400) AS created_at
FROM   pone_bt_prod_inst_t
WHERE  DATE '1970-01-01' + created_t/86400 < SYSDATE - 1/24;

Notes and cautions:

  • 1 hour = 3600 seconds; 1 hour = 1/24 day in Oracle DATE arithmetic.
  • If created_t is UTC but the database server uses a different timezone, results will be off by the timezone offset; convert SYSTIMESTAMP to UTC (for example with SYS_EXTRACT_UTC) before calculating the epoch if UTC alignment is required.
  • Prefer the numeric comparison when filtering large tables so the condition can use an index on created_t.
  • ’s link to Oracle date-difference techniques is relevant for understanding DATE vs TIMESTAMP functions, but the numeric-epoch approach above is usually the simplest and fastest fix.

Recommended Answers

All 3 Replies

Member Avatar for ssuet

hey buddy.....
there is litte bit diffrence in that code ....first tell me which database software are you using??

Member Avatar for samsons17

ssuet , i am using oracle database

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.