What would be the best datatype to use to store this timestamp? TIMESTAMP, or DATE?
The TIMESTAMP datatype is an extension to the DATE datatype. In addition to the datetime elements, the TIMESTAMP datatype holds fractions of a second. It comes in two forms, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. So, if you want the precision till fraction of seconds along with the timezone elements, go for TIMESTAMP. Else, a DATE data type will give you datetime elements.
Also, what would the format be?
Format is only for DISPLAY. So use the format model which you would like to display. You need to use TO_CHAR and a proper format model. For example,
SQL> select to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'MM
-------------------
01/21/2015 11:02:27
SQL> select to_char(sysdate, 'mm/dd/yyyy hh:mi:ss am') from dual;
TO_CHAR(SYSDATE,'MM/DD
----------------------
01/21/2015 11:03:04 am
SQL>
When I tried to import my CSV file containing all of the timestamps,
SQL Developer Import Wizard didnt like the formatting.
That is the issue with your locale-specific NLS_DATE_FORMAT.
