What is the difference between `TIMESTAMP` , `DATE` AND `TIMESTAMP with TIMEZONE`?

E.g if I wanted to search for all entries between `01-JAN-1990` and `01-JAN-2000` , how would I do so in each format?

I have been searching for timestamp as:

```
SELECT COUNT(*) FROM TABLE_NAME WHERE DATE BETWEEN '01-JAN-1990' AND '01-JAN-2000;

```

But I am not sure what format to use to search for `DATE` or `TIMESTAMP WITH TIMEZONE`.