Search code examples
sqlpsqlqliksense

Issues pulling data from server

I'm attempting to pull data from an Oracle Database where the timestamp is in UTC time, but I'm attempting to pull using CST, based on the information that a client has requested.

Basically, I'm looking to pull the information based on the code for the previous day, but I also need to account for the time difference to Central time(-6 hours), so essentially, I need to pull information for the previous 30 or so hours.

Here is my below code:

SELECT *
FROM SPROBSUMMARYM1 PM1 
JOIN SPROBSUMMARYM2 PM2 ON PM1."NUMBER" = PM2."NUMBER"
JOIN SCOMPUTERM1 Com1 ON PM1."FS_MACHINE" = Com1."MACHINE_NAME"
where PM1."OPEN_TIME" > TRUNC(SYSDATE)-1 AND PM1."OPEN_TIME"<TRUNC(SYSDATE);
Solution

  • In Oracle time, 1 is a full day, one hour is 1/24. To get 6 hours you would use 6/24. SO your SQL should become 

    SELECT *
  FROM sprobsummarym1 pm1
       JOIN sprobsummarym2 pm2 ON pm1."NUMBER" = pm2."NUMBER"
       JOIN scomputerm1 com1 ON pm1."FS_MACHINE" = com1."MACHINE_NAME"
 WHERE pm1."OPEN_TIME" > TRUNC (SYSDATE) - 1 - 6/24
   AND pm1."OPEN_TIME" < TRUNC (SYSDATE) - 6/24;