Unload with timestamp filter

Question

I was attempting to unload all data prior to 2017 from a database and hit what seems to be a bug...  
  
my query was:   
  
unload ('select * from traffic where timestamp < \'2017-01-01\' order by timestamp asc')  
to 's3://xxx/export/unload/2019-01-22-3/'   
iam_role 'arn:aws:iam::xxx:role/xxx'  
delimiter as ','  
addquotes  
escape  
manifest;  
  
what I ended up with was only data prior to 2015, which I assume is a result of the date being evaluated as subtraction and looking at the query in the inspector:  
  
unload ('select * from traffic where timestamp < 2017-01-01') to 's3://xxx/export/unload/2019-01-22/' iam_role '' delimiter as ',' addquotes escape allowoverwrite manifest

Accepted Answer

It's likely that the backslash escape is being stripped by something that handles the query text before the query gets to Redshift.    
  
Try doubling up the quotes instead as that also works.

```
unload ('select * from traffic where timestamp < '''2017-01-01'' order by timestamp asc')…
```

Answer

One general approach to avoid bugs or limitations in the select clause of the unload statement is to define a view where the view definition is what you want to unload, then refer to the view in the unload statement, e.g.:  
  
create view #vname as  
  select * from traffic where timestamp < 2017-01-01 order by timestamp asc  
;  
  
unload (select * from #vname) to ..  
;  
  
Edited by: karbjonn on Jan 29, 2019 12:38 AM  
Removed the quotes around the date criteria, and the unload select clause, as on posting the original message AWS gets confused and do not display the statements correctly. Of course, in real life, quotes must be added.

Answer

> which I assume is a result of the date being evaluated as subtraction  
  
(lols!!  I shouldn't laugh, but that is funny =-)  
  
Maybe try using DATE_CMP() and seeing if the literal in the function is interprented correctly?  
  
I would try it myself but I do not have access to a cluster.

Unload with timestamp filter

関連するコンテンツ