How to Insert a Timestamp in Oracle in a Specific Format

Question

I am at a loss as how to insert the current time in a different format than the default. Can somebody help explain?

Here is how my table was created:

CREATE TABLE ACTIVITY_LOG 
(
TIME TIMESTAMP NOT NULL 
, ACTIVITY VARCHAR2(200) NOT NULL 
);

My insert command works:

insert into activity_log 
values (localtimestamp,'blah');

But how do i insert the localtimestamp value into my table in a different format using the various MM DD YY HH MM SS tags? I've tried the following, but it gives me the ORA-1830: date format picture ends before converting entire input string error.

insert into activity_log
values (to_timestamp(localtimestamp,'YYYY/MM/DD'),'blah');

Answer 1

You don't insert a timestamp in a particular format. Timestamps (and dates) are stored in the database using an internal representation, which is betwen 7 and 11 bytes depending on the type and precision. There is more about that in this question, among others.

Your client or application decides how to display the value in a human-readable string form.

When you do:

to_timestamp(localtimestamp,'YYYY/MM/DD')

you are implicitly converting the localtimestamp to a string, using your session's NLS settings, and then converting it back to a timestamp. That may incidentally change the value - losing precision - but won't change how the value is stored internally. In your case the mismatch between the NLS setting and the format you are supplying is leading to an ORA-01830 error.

So your first insert is correct (assuming you really want the session time, not the server time). If you want to see the stored values in a particular format then either change your client session's NLS settings, or preferably format it explicitly when you query it, e.g.:

select to_char(time, 'YYYY-MM-DD HH24:MI:SS.FF3') from activity_log

Answer 2

You don't seem to provide any indication of what your 'localtimestamp' is - is that pseudocode? A variable name? A column you haven't shown the definition for?

What data type is 'localtimestamp'? What data does it contain? Pertinent questions as other answers point out, because if it truly is a time stamp then oracle will be converting it to a string for you, before passing that string to to_timestamp() in your final query. Your initial stab at it should just work if the variable is a timestamp, containing a timestamp

Ultimately "date format picture ends" means "you passed me a string looking like '2017-05-17 12:45:59', but claimed it was only 'yyyy-mm-dd'. What was I expected to do with the rest of it?"

Your current final comment on your question "I was hoping to look in the table and see a useful looking time" - that's your query tool's problem. Have a look in the setting of your query tool and change the date format it displays. As has been noted, dates in oracle are stored as a decimal number days since a certain moment in time. If 0 represents 01 Jan 1970, then 1.75 represents 6pm on the 2 Jan 1970. It is up to the end program the user is using, to format the date into something you like.. you cannot "insert a timestamp with a different format" because time stamps don't have a format any more than a number like 1.75 has a format. It is what your query does with it when it gets it out, that gives it the format:

To_char(timestampcol, 'yyyy mm did')
To-char(tomestampcol, 'mon dd yyyy')

These use oracles built in date formatter, that turns that decimal number of the date into a string in the given format; you will see a string.. or you can just write "select * from table" and run it in TOAD and toad will show you the dates according to the format in settings, or you can write a c# program and get a load of date objects out and call my date.ToString("yyyy-MM-dd") on them to format them. The idea I'm trying to get across is that you don't pick the date format on the way in, you pick it on the way out, if you don't like what you're looking at, you have to change it on the way out, not the way in