Query only last 4 days In-DB Oracle

[GoldenDesign04]

Trying to pull in a massive table, from beginning of this year to current is over 100mil rows. I have several criteria that will lessen the load. Most importantly, I only need the previous 4 days.

I have a timestamp column but cannot get any of the SQL to work. Admittedly I usually use Postgress or MySQL and this is an Oracle DB.

Syntax to pull only previous 4 days?

I've tried 

          where TIMESTAMP   >= DATEADD(day,-4, GETDATE())

          where  TIMESTAMP > SYSDATE - 4 

To no real solution

[junyak]

Did you try in the query editor?

Similar to the below post:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Date-in-SQL-Editor-Visual-Quer...

[RolandSchubert]

Hi @GoldenDesign04 ,

you could try:

DATEDIFF(DAY, CAST(TIMESTAMP AS date), GetDate()) < 5

I assume, the TIMESTAMP column is a TIMESTAMP data type (i.e. date + additional time information). To avoid confusion, the column should be converted to a DATE data type. Datediff function return the number of units (days) between first and last day. Hope this is helpful.

Best,

Roland

[GoldenDesign04]

This makes sense but the trouble that seems to be occurring is the SQL query editor is so **bleep** specific on its structure that anytime I put in a straight forward query, I get errors.

I strip out all the parenthesis, to get the "test query" to pass and once run it just ignores any date argument I wrote and returns the whole table

Is this an incompatibility with the SQL editor and Oracle DBs?

I have two other data sources that are in other DBs (MSSQL, Postgres) and I don't have this issue.

@junyak 

 @RolandSchubert 

[RolandSchubert]

In general, there is no incompatiblity in my opinion (at least this is my experience using sometimes Alteryx with Oracle DBs).  Do you create your SQL in Oracle Developer or Toad and copy it to Alteryx or do you use the Alteryx SQL editor to create queries? Could you provide the complete SQL - it's strange that only the WHERE clause does not work. And - is it only related to date restrictions in the where clause, or does this happen also on other conditions?

[GoldenDesign04 · Accepted solution]

@RolandSchubert 

I believe I found it. Previous pulls I was doing a syntax like this:

WHERE
 TimeStamp > SysDate -5
AND
 Media_code = 'R'
AND
 Seconds > 180
 OR
  Seconds <2701

I used alteryx SQL editor. I am pulling in the entirety of a single table (31 million rows), filtering out by date, if a certain code is R, and a numerical value.

Where
   (  TIMESTAMP > SysDate -5
      And MEDIA_TYPE_CODE = 'R' 
      And TIMESHIFT_SECS > 180
   )
   Or 
   (  TIMESTAMP > SysDate -5
      And MEDIA_TYPE_CODE = 'R' 
      And TIMESHIFT_SECS < 2701
   )

Oh, my goodness. The level of explicit writing needed. It never occurred to me to do it as such.

Thank you @RolandSchubert for pushing me to keep adding detail. Trying to explain it more helped me work it out!