TIMESTAMP(6) Oracle Insert

Question

I am generating dummy data for a database, one attribute is using TIMESTAMP(6).

Can anyone give me an example of how the value looks?

Also I would like to be able to insert the date along

i.e 03/18/2012 02:35 AM

so that it looks like

INSERT INTO FLIGHT VALUES (1,'London Heathrow','Miami Airport','03/18/2012 02:35 AM','04/18/2012 13:35 PM');

Is this possible? Thanks alot...

EDIT----

How can i get rid of the timestamp column massive space

Answer 1

Whenever you are dealing with DATE or TIMESTAMP data types, you should always insert DATE or TIMESTAMP values. You should not be inserting strings and relying on implicit conversions to convert the string to a DATE or a TIMESTAMP. You should be explicitly calling TO_DATE or TO_TIMESTAMP. Your INSERT statement should also be explicitly listing the names of the columns you're inserting into.

You'd want your INSERT statement to look something like this

INSERT INTO FLIGHT( <<list of columns>> )
  VALUES (1,
         'London Heathrow',
         'Miami Airport',
         to_timestamp( '03/18/2012 02:35 AM', 'MM/DD/YYYY HH:MI AM'),
         to_timestamp( '04/18/2012 13:35 PM', 'MM/DD/YYYY HH:MI AM') );

You can adjust how SQL*Plus displays the data in a particular column by changing the format mask. For E.g.

SQL> column dept_time format a30;
SQL> column arrv_time format a30;

will cause SQL*Plus to display both DEPT_TIME and ARRV_TIME in 30 horizontal characters (your current NLS_TIMESTAMP_FORMAT appears to generate 28 character strings, if you change your NLS_TIMESTAMP_FORMAT, you may want to change the width of the column you're asking SQL*Plus to display).

Answer 2

Please take a look at the following example:

ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS';

SELECT *
FROM orders 
WHERE ordered_at 
   BETWEEN '2013-10-06 12:00:00' 
       AND '2013-10-06 16:00:00'